CODEWITHSUNDEEP
azure-cosmosdb
sparkstar
sparkstar

Reputation: 613

How to use an aggregate function on an array column in cosmos-db

I have a cosmos item like :-

{
  "id": "123,
  "meta": "ancdkjlerlh"
  "reason": [
        "reason1",
        "reason2"
    ],
  "status": "REJECTED"
}

I need to find the count of items for each reason.

I tried something like this :-


SELECT count(1) as CountOfRejected, 
c.reason
FROM c
WHERE c.status= "REJECTED"
GROUP BY c.reason

which treats the entire reason array as a whole. The ARRAY_CONTAINS method requires a second parameter to use in GROUP BY clause which I think will take 1 reason at a time. Is there a way to do it in a single query like in SQL? I want result in this format

         Reason | CountOfRejected |
        --------------------------
        Reason 1 | 10
        Reason 2 | 5
        Reason 3 | 7

Upvotes: 0

Views: 1499

Answers (1)

Steve Johnson
Steve Johnson

Reputation: 8660

You can use the following SQL:

SELECT 
    COUNT(r) as CountOfRejected,r as Reason 
FROM c JOIN r IN c.reason 
WHERE c.status= "REJECTED" 
GROUP BY r

Result:

[
    {
        "CountOfRejected": 1,
        "Reason": "reason3"
    },
    {
        "CountOfRejected": 1,
        "Reason": "reason2"
    },
    {
        "CountOfRejected": 2,
        "Reason": "reason1"
    }
]

Upvotes: 1

Related Questions