Grouping on data within an array

SQL++
1

I am trying to group on fields within an array. For example I have a two docs like this
{
“id” : “doc1”,
elements: [
“01”, “02”, “03”
]
}

{
“id” : “doc2”,
elements: [
“01”, “02”, “04”
]
}

I want to group by the values within elements and return their counts throughout the docs. so for example:
{
{
“elementId”: “01”,
“elementCnt” : 2
},
{
“elementId”: “02”,
“elementCnt” : 2
},
{
“elementId”: “03”,
“elementCnt” : 1
},
{
“elementId”: “04”,
“elementCnt” : 1
},

}

How can I accomplish this?

2 
SELECT el AS elementId, COUNT(1) AS elementCnt
FROM default AS d
UNNEST d.elements AS el
WHERE ......
GROUP BY el;
1 Like
3

The above solution works as desired but now we are considering the following:

{
“id” : “doc1”,
elements: {
"e01” : “Y”,
“e02”: “Y”,
“e03”: “Y”
}
}

{
“id” : “doc2”,
elements: {
"e01” : “Y”,
“e02”: “Y”,
“e04”: “Y”
}
}

I want the response to be in the same format as originally noted: like this,

{
{
“elementId”: “01”,
“elementCnt” : 2
},
{
“elementId”: “02”,
“elementCnt” : 2
},
{
“elementId”: “03”,
“elementCnt” : 1
},
{
“elementId”: “04”,
“elementCnt” : 1
},

}

I am trying a query where i am grouping on all the e0… fields, and doing a COUNT on those specific fields, but I seem to be getting an extra few counts presented. any idea?

4 
SELECT el AS elementId, COUNT(1) AS elementCnt
FROM default AS d
UNNEST OBJECT_NAMES(d.elements) AS el
WHERE ......
GROUP BY el;
1 Like
5

Thanks! that works, but can you explain the logic behind the query? And how UNNEST performs if I were to create and index covering elements. also If I want to filter on specific fields within elements such as where “e01” = “Y” i am unable to do so. is their a way to reference the original object to get results and counts for just e01?

6 
SELECT el.name AS elementId, COUNT(1) AS elementCnt
FROM default AS d
UNNEST OBJECT_PARIS(d.elements) AS el
WHERE el.name IN [ "e01", "e02"] AND el.val = "Y"
GROUP BY el.name;

CREATE INDEX ix1 ON default (ALL ARRAY el.name FOR el IN OBJECT_PARIS(elements) WHEN el.val = "Y" END);

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

1 Like