Group By in N1ql query which has array elements

SQL++
,
1

Hi

I have a below document in couchbase and I wanted to use group by on values on array(locationClusterId) along with someother fileds like effectiveDateTime(epoch millis) and state. Can someone say best way to write down the N1QL query based on a day range provided for effectiveDateTime.

Sample document:
{
“_class”: “com.XXX.YYY”,
“effectiveDateTime”: 1717196400000,
“locationDSL”: {
“dataType”: “LOCATION”,
“dsl”: “API”,
“lookupType”: “CLUSTER_BY_ID”,
“parameters”: {
“locationClusterId”: [
“ABC”,
“XYZ”
]
}
},
“state”: “APPROVED”
}

Expected Result:
In below the day is value of effectiveDateTime after truncating the hours and minutes etc.

[
{
“count”: 16,
“day”: “2024-02-14”,
“locationClusterId”: “ABC”,
“state”: “APPROVED”
},
{
“count”: 14,
“day”: “2024-02-06”,
“locationClusterId”: “XYZ”,
“state”: “CANCELLED”
},
{
“count”: 10,
“day”: “2024-02-12”,
“locationClusterId”: “XYZ”,
“state”: “DRAFT”
}
]

Regards,
Venkat

2

So you essentially want to UNNEST locationClusterId and group by that, as your result is per locationClusterId element rather than by the array itself.

See: https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/unnest.html

HTH.

3

@dh thanks for prompt respone,

Not familiar with UNNEST but from the quick reading of it, it might be the one I am looking for, one more think can we group based on the UNNEST elements after flattening?

Regards,
Venkat

4

Yes, after UNNESTing the alias is available the same as any other field in the document.

HTH.

5

@dh thanks will try it out.

6

@dh in the above example the doucment structure is like below, if I UNNEST
locationDSL.parameters.locationClusterId as perCluster, how can I refer locationClusterId in my query.

{
  "_class": "com.XXX.YYY",
  "effectiveDateTime": 1717196400000,
  "locationDSL": {
    "dataType": "LOCATION",
    "dsl": "API",
    "lookupType": "CLUSTER_BY_ID",
    "parameters": {
      "locationClusterId": [
        "ABC",
        "XYZ"
      ]
    }
  },
  "state": "APPROVED"
}

Query triggered but didn’t return results,

SELECT  p.state, MILLIS_TO_UTC(p.effectiveDateTime, '1111-11-11') as day, perCluster.locationClusterId, count(1) as count
FROM prices p
UNNEST locationDSL.parameters.locationClusterId perCluster
WHERE p._class IN ['com.XXX.YYY']
AND perCluster.locationClusterId IN ["XYZ", "ABC"]
AND p.state IN ["APPROVED", "DRAFT","DELETED","SUBMITTED", "AMENDED"]
AND p.effectiveDateTime BETWEEN STR_TO_MILLIS('2024-02-01T00:00:00Z') AND STR_TO_MILLIS('2025-01-01T00:00:00Z')
GROUP BY  MILLIS_TO_UTC(p.effectiveDateTime, '1111-11-11'), p.state, perCluster.locationClusterId
7

The UNNESTing is a top level field - try:

...
UNNEST locationDSL.parameters.locationClusterId AS locationClusterId
...
AND locationClusterId IN ["XYZ", "ABC"]
...
GROUP BY locationClusterId, ...

HTH.

1 Like
8 
SELECT  p.state, day, locationClusterId, COUNT(1) AS count
FROM prices p
UNNEST locationDSL.parameters.locationClusterId AS locationClusterId
LET day = MILLIS_TO_UTC(p.effectiveDateTime, '1111-11-11')
WHERE p._class IN ["com.XXX.YYY"]
      AND locationClusterId IN ["XYZ", "ABC"]
      AND p.state IN ["APPROVED", "DRAFT","DELETED","SUBMITTED", "AMENDED"]
      AND day BETWEEN "2024-02-01" AND "2024-12-31"
GROUP BY  day, p.state, locationClusterId;
9

@dh thanks this helps

10

@vsr1 thanks this helps