Partition the result set and create multiple arrays

SQL++
1

Hi, I have a result set like this (output of a n1ql query)
[
{
“createdAt”: “2017-05-09T08:09:31.125Z”,
“deviceId”: “00000000-28f5-0b6a-0000-00001059cdd6”,
“eventName”: “clientSessionStart”,
“stepNo”: 1
},
{
“createdAt”: “2017-05-09T08:09:31.127Z”,
“deviceId”: “00000000-28f5-0b6a-0000-00001059cdd6”,
“eventName”: “notificationReceived”,
“stepNo”: 3
},
{
“createdAt”: “2017-05-09T08:09:31.130Z”,
“deviceId”: “00000000-28f5-0b6a-0000-00001059cdd6”,
“eventName”: “notificationReceived”,
“stepNo”: 3
}]

I need to subquery the query of this result set and partition the result set according to stepNo.
So the result should contain two separate arrays each storing the related items. First one will store the items with stepNo 1 and the other step no 3.

Do we have anything in N1QL to partition the result set? Thanks in advance.

2 
SELECT ARRAY_AGG(CASE WHEN d.stepNo == 1 THEN d ELSE MISSING END) AS seqno1,
       ARRAY_AGG(CASE WHEN d.stepNo == 3 THEN d ELSE MISSING END) AS seqno3
FROM ( SUB  QUERY ) d;

Example:

INSERT INTO default VALUES("c01",{ "createdAt": "2017-05-09T08:09:31.125Z", "deviceId": "00000000-28f5-0b6a-0000-00001059cdd6", "eventName": "clientSessionStart", "stepNo": 1 }),
                    VALUES("c02",{ "createdAt": "2017-05-09T08:09:31.127Z", "deviceId": "00000000-28f5-0b6a-0000-00001059cdd6", "eventName": "notificationReceived", "stepNo": 3 }),
                    VALUES("c03",{ "createdAt": "2017-05-09T08:09:31.130Z", "deviceId": "00000000-28f5-0b6a-0000-00001059cdd6", "eventName": "notificationReceived", "stepNo": 3 });
SELECT ARRAY_AGG(CASE WHEN d.stepNo == 1 THEN d ELSE MISSING END) AS seqno1,
       ARRAY_AGG(CASE WHEN d.stepNo == 3 THEN d ELSE MISSING END) AS seqno3
FROM default d;

More generic approach for any number of StepNo’s

SELECT OBJECT "StepNo_"||TOSTRING(v[0].stepNo):v FOR v IN
                                    (SELECT RAW ARRAY_AGG(d) FROM  default d
                                           WHERE d.stepNo IS NOT NULL
                                           GROUP by d.stepNo) END;
3

Thanks so much, ARRAY_AGG did it. This is a really useful clause.

1 Like
4

The following is more generic approach. It does any number of StepNo’s

SELECT OBJECT "StepNo_"||TOSTRING(v[0].stepNo):v FOR v IN (SELECT RAW ARRAY_AGG(d) FROM  default d
                           WHERE d.stepNo IS NOT NULL
                           GROUP by d.stepNo) END;
1 Like