Combining mulitple arrays as conditions within CASE structure

[ddantchev]

Hi Dear Experts, I am having almost zero experience with Couchbase. I am trying to make a query on the following sample document in Couchbase:

[
  {
    "bucket": {
      "customerid": 123456,
      "ticket": [
        {
          "creationDate": "2023-11-08T07:31:47.077Z",
          "caseAction": {
            "id": "11",
            "type": "Complaint",
            "reason": "Any"
          },
          "status": {
            "state": "Active"
          },
        },
		{
          "creationDate": "2023-11-07T07:31:47.077Z",
          "caseAction": {
            "id": "11",
            "type": "Complaint",
            "reason": "Any"
          },
          "status": {
            "state": "In Progress"
          },
        },
		{
          "creationDate": "2023-11-06T07:31:47.077Z",
          "caseAction": {
            "id": "11",
            "type": "Request",
            "reason": "Any"
          },
          "status": {
            "state": "Active"
          },
        },
        {
          "creationDate": "2023-11-01T09:40:27.215Z",
          "caseAction": {
            "id": "19",
            "name": "Request",
            "reason": "no reason"
          },
          "status": {
            "state": "Closed"
          },
          "statusDate": "2023-11-30T09:57:03.704Z",
          "validFor": {
            "endDateTime": "2023-11-30T09:57:03.244Z",
            "startDateTime": "2023-11-15T09:40:27.212Z"
          }

  }
]
````Preformatted text`

The equivalent of the query in Oracle or PostgreSQL is like this: 
select customer_id,     
       sum(case
             when name = 'Request' and reason = 'Any' then power(2, 0)
             when name = 'Complaint' and reason = 'Any' then power(2, 1)
             else 0
           end) long_ActiveCases
  from cases
 where state in ('Active','In Progress')
 group by customer_id

The output is for example 123456,3

For some reason unnest doesn't work, using ARRAY is working, but here I need to use under 'ticket' two subarrays for caseStatus to check name and reason, then status for state Active or In Progress  Then use POWER and CASE, which I checked already do exist in N1QL . But don't have a clue how to combine all of those. In RDBMS it is clear, but here :slightly_smiling_face:

[dh]

How were you attempting UNNESTing ?

UNNEST flattens the array into individual documents, then you use it as if it was a flat stream.

e.g. using your example data:

cbq> SELECT b.customerid, t
   2 FROM `bucket` b
   3 UNNEST ticket t
   4 WHERE b.customerid = 123456
   5 ;
{
    "requestID": "0f3ff8f6-c449-4efe-b528-b9ec7eb2e0a6",
    "signature": {
        "customerid": "json",
        "t": "json"
    },
    "results": [
    {
        "customerid": 123456,
        "t": {
            "caseAction": {
                "id": "11",
                "reason": "Any",
                "type": "Complaint"
            },
            "creationDate": "2023-11-08T07:31:47.077Z",
            "status": {
                "state": "Active"
            }
        }
    },
    {
        "customerid": 123456,
        "t": {
            "caseAction": {
                "id": "11",
                "reason": "Any",
                "type": "Complaint"
            },
            "creationDate": "2023-11-07T07:31:47.077Z",
            "status": {
                "state": "In Progress"
            }
        }
    },
...

So your statement becomes:

SELECT b.customerid,     
       sum(CASE
             WHEN t.caseAction.type = 'Request' AND t.caseAction.reason = 'Any' THEN power(2, 0)
             WHEN t.caseAction.type = 'Complaint' AND t.caseAction.reason = 'Any' THEN power(2, 1)
             ELSE 0
           END) long_ActiveCases
FROM `bucket` b
UNNEST ticket t
WHERE t.status.state IN ['Active','In Progress']
GROUP BY b.customerid

Note:

• the quoting of reserved words used as identifiers (i.e. bucket)
  • see https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/reservedwords.html
• the syntax of the IN clause; square brackets not parentheses - it is an array in SQL++.
• the case-sensitive field names
• the field path syntax to address nested object elements
  • i.e. the dot-notation

Also, based on your example data:

• “customerid” and not “customer_id” is the field name
• “type” and not “name” compared with ‘Request’ & ‘Complaint’ in the CASE statement
  • the resulting “long_ActiveCases” is not 3 but 5:
    • the first ticket is ‘Complaint’ + ‘Any’ and state is ‘Active’
      • add power(2,1), i.e. 2
    • the second ticket is ‘Complaint’ + ‘Any’ and state is ‘In Progress’
      • add power(2,1), i.e. 2
    • the third is ‘Request’ + ‘Any’ and state is 'Active
      • add power(2,0), i.e. 1
    • the fourth is ‘Request’ + ‘no reason’ and state is ‘Closed’
      • not included
    • The result is:
      • 2 + 2 + 1 = 5

If you have a single document per customer then you could also write it as:

SELECT b.customerid,     
       array_sum(ARRAY CASE
                         WHEN v.caseAction.type = 'Request' AND v.caseAction.reason = 'Any' THEN power(2, 0)
                         WHEN v.caseAction.type = 'Complaint' AND v.caseAction.reason = 'Any' THEN power(2, 1)
                         ELSE 0
                       END
                 FOR v IN b.ticket
                 WHEN v.status.state IN ['Active','In Progress']
                 END
                ) long_ActiveCases
FROM `bucket` b

avoiding the UNNESTing.

With multiple customer documents you could wrap this to aggregate:

SELECT a.customerid, SUM(a.long_ActiveCases) long_ActiveCases
FROM
(
   SELECT ... -- using array_sum()
) AS a
GROUP BY a.customerid

HTH.

[ddantchev]

Thanks a lot!! This really helped in generating expected results, had to tune something but that is related to data, the solution provided worked perfectly!!