Inner join two buckets and returning the max value of a column

I have two buckets in couchbase server:

demographics {“country_id”:“BGD”,“country_name”:“Bangladesh”,“population”:“164700000”,“area”:“148460”,“density”:“1265”},


I want to retrieve the maximum deaths value countrywise. when i am trying to get max value using max() function, it’s not returning the maximum value and also query is giving error:

                 "code": 4210,
                 "msg": "Expression (`s`.`dt`) must depend only on group keys or aggregates."

Here is my query:

              d.country_name demographics, s.dt `statistics`, MAX([s.deaths,s])[1]
              Demographics d 
             JOIN statistics s 
             On d.country_id=s.country_id
             GROUP BY country_name
             ORDER BY MAX(s.deaths) DESC;

I want the result like this: highest death with country

               country_name       dt                     deaths
               GERMANY           2022-02-16       455
               BANGLADESH     2022-02-17       20
SELECT d.country_name demographics, m[1].*
FROM Demographics d
JOIN statistics s ON d.country_id = s.country_id
GROUP BY d.country_name
LETTING m = MAX([s.deaths, {"statistics":s.dt, s.deaths}])
ORDER BY m[1].deaths DESC;

Thank you so much for the solution, but the first problem remains, it’s not returning the max deaths value…is there any problem with the indexing?the result is below:

                        "deaths": "91",
                        "demographics": "Germany",
                        "statistics": "2022-01-29"
                          "deaths": "41",
                         "demographics": "Bangladesh",
                         "statistics": "2022-02-10"

I am attaching a screenshot of my input in which 91 is not the max value but the query returns this value.

deaths is string; “91” is highest based on string. So convert to NUMBER

SELECT d.country_name demographics, m[0] AS deaths, m[1]  AS statistics
FROM Demographics d
JOIN statistics s ON d.country_id = s.country_id
GROUP BY d.country_name
LETTING m = MAX([TO_NUMBER(s.deaths), s.dt])

thank you for that string point. it worked!

I am facing problem with this query now, earlier it worked. The error is:

    "code": 4330,
    "msg": "No index available for ANSI join term s",

is there anything changed in couchbase language pattern? @vsr1

Perhaps a silly point, but are your indexes on Demographics and Statistics still defined and online/available? (Perhaps check in system:indexes?)

i have used the above query earlier and returned correct results…so i want to know what is this error for?

It means that an online index couldn’t be found to support your join. If you previously had an index defined on statistics(country_id) and it has been dropped or is unavailable then you can expect this error. Hence suggesting checking on the available indexes with a sytem:indexes query.

there are no indexes on any of the buckets…i have checked…

Just for reference (in case others come across this topic): ANSI JOIN Support in N1QL - The Couchbase Blog

i have solved the problem…thanks