CODEWITHSUNDEEP
couchbasesql++
Sweta Sharma
Sweta Sharma

Reputation: 483

how to get the count of an array key and value in Couchbase

I have 3 documents for which I want to get the count of a key present in an array. below is the documents :-

    [
        {
            appId: "f1606a90-c96f-4db9-8e5f-9f0b6d2d2116",
            attributes: [
                {
                    "activity": "Brand Management",
                },
                {
                    "priority": "No"
                },
                {
                    "numberOfLine": 6
                }
            ]
       },
       {
           appId: "f1606a90-c96f-4db9-8e5f-9f0b6d2d2116",
           attributes: [
               {
                   "activity": "Brand Management",
               }
           ]
      },
      {
          appId: "f1606a90-c96f-4db9-8e5f-9f0b6d2d2116",
          attributes: [
              {
                  "activity": "Upsert",
              },
              {
                  "mailStatus": "Assigned"
              }
          ]
      }

Query :-

SELECT RAW
    count(1)
FROM
    workflow wf
    UNNEST wf.attributes attr
WHERE
    wf.appId = "f1606a90-c96f-4db9-8e5f-9f0b6d2d2116"
    AND
    attr.activity == 'Brand Management';

Result :-

[
   2
]

Expected Result :-

[
    {
        "Brand Management" : 2
    }
]

I am looking for result which will give me output as activity name and the count of that activity.Any leads will be really helpful.

Upvotes: 2

Views: 114

Answers (1)

vsr
vsr

Reputation: 7414

SELECT COUNT(1) AS `Brand Management`
FROM workflow wf
UNNEST wf.attributes attr
WHERE wf.appId = "f1606a90-c96f-4db9-8e5f-9f0b6d2d2116"
      AND attr.activity == 'Brand Management';

Upvotes: 0

Related Questions