How to count number of objects in subdocument using N1ql query

SQL++
1

This sample doc has billing and shipping addresses in addresses subdocument. How do I count how many addresses in addresses subdocument using N1ql query.
{
“name”: “xxx xxxx”,
“email”: “xxx@xxx.com”,
“addresses”: {
“billing”: {
“line1”: “123 Any Street”,
“line2”: “Anytown”,
“country”: “United Kingdom”
},
“delivery”: {
“line1”: “123 Any Street”,
“line2”: “Anytown”,
“country”: “United Kingdom”
}
},
“purchases”: {
“complete”: [
339, 976, 442, 666
],
“abandoned”: [
157, 42, 999
]
}
}

Thanks,

Matthew

2

@Van2008 ,

Those are objects not ARRAYs so convert object to array and count.

SELECT ARRAY_COUNT(OBJECT_NAMES(d.addresses)) AS cnt
FROM default AS d 
WHERE .............

OR

SELECT OBJECT_LENGTH(d.addresses) AS cnt
FROM default AS d 
WHERE .............

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

1 Like
3

Hi @Van2008,

If you know the document ID, it might be more efficient to use the K/V service. A K/V subdocument lookup “count” operation returns the number of elements if the path refers to an Array, or the number of fields if the path refers to an Object.

Here’s what that might look like in Java:

LookupInResult result = collection.lookupIn(
    documentId,
    singletonList(LookupInSpec.count("addresses"))
);
int addressCount = result.contentAs(0, Integer.class);

Thanks,
David