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

SQL++
, ,
1

I have two buckets in couchbase server:

demographics {“country_id”:“BGD”,“country_name”:“Bangladesh”,“population”:“164700000”,“area”:“148460”,“density”:“1265”},
{“country_id”:“DEU”,“country_name”:“Germany”,“population”:“83200000”,“area”:“357386”,“density”:“232”},

          statistics:
          {"country_id":"DEU","dt":"2022-02-17","confirmed_cases":"235626","deaths":"261"},
          {"country_id":"DEU","dt":"2022-02-16","confirmed_cases":"219972","deaths":"455"},
          {"country_id":"BGD","dt":"2022-02-17","confirmed_cases":"3539","deaths":"20"},
          {"country_id":"BGD","dt":"2022-02-16","confirmed_cases":"3929","deaths":"15"},

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:

              SELECT 
              d.country_name demographics, s.dt `statistics`, MAX([s.deaths,s])[1]
              FROM
              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
2 
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;
3

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.

1
1964×423 67.3 KB

4

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])
ORDER BY m[0] DESC;
5

thank you for that string point. it worked!

6

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

7

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

8

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

9

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.

10

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

ix1
ix1920×320 20.5 KB

11

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

12

i have solved the problem…thanks