Count sub-objects' array length

Couchbase Server
1

Hi.

I have a couchbase bucket results and I store the records with the following format:

[
    {
        "results": {
            "data": [ {...}, {...}, {...} ]
        }
    },
    {
        "results": {
            "data": [ {...}, {...} ]
        }
    }
]

So what I want to do is to count and sum up the size (length) of each data array using a query from the administration panel; The result should be 5 for the example above.

I tried the following query but it didn’t work. Any ideas on how to improve it?

SELECT COUNT(1)
FROM results AS d
UNNEST OBJECT_VALUES(d.data) AS u
2

How about:

SELECT SUM(ARRAY_LENGTH(r.data)) FROM results r

?

HTH.

1 Like
3

Hi @arronar ,

@dh suggestion is optimal way.

OBJECT_VALUES() is for object only. As data is not object it will return NULL. UNNEST expects ARRAY. If you remove OBJECT_VALUES() it will work. Unnest is not needed if you follow @dh suggestion.