Query for ArrayLength in N1QL

SQL++
, ,
1

I’m trying to count the number of objects in an array (LAST_EXECUTIONS) inside a document. The document looks like this:

{
“id”: “deletionProcess”,
“processStatus”: “active”,
“limit”: 10000,
“numThreads”: 10,
“maxDocumentsToProcess”: 10000,
“customConfiguration”: {
“retentionDays”: “4”
},
“retentionDays”: 4,
“LAST_EXECUTIONS”: [
{
“identifier”: “physical_deletion”,
“retries”: “?”,
“status”: “accepted”,
“affected_documents”: 11
}
]
}

I have tried the following query: SELECT ARRAY_LENGTH(deletionProcess.LAST_EXECUTIONS) AS count FROM es-spl,

but instead of a single int it returns an array of 240 or so empty objects. Does anyone know what I’m doing wrong?

2

Hi @boris.mtdv, I work on SDK rather than N1QL so I’m not certain why that’s not working, but is there a reason you’re using a N1QL query for this over the KV SDK? For single-doc operations the SDK will be faster, and you can easily get the length of the field that way.

1 Like
3

I can use the java sdk as well, I just don’t know how. If you could point me in the right direction that would be great.

4

The following query returns number of array elements for each document.

SELECT ARRAY_LENGTH(LAST_EXECUTIONS) AS count 
FROM  `es-spl`  USE KEYS ["deletionProcess"];

If you want in whole bucket

Do aggregate query.

1 Like
5

This will do it:

    Cluster cluster = CouchbaseCluster.create("localhost");
    cluster.authenticate("Administrator", "password");
    Bucket bucket = cluster.openBucket("default");
    JsonDocument doc = bucket.get("deletionProcess");
    int count = doc.content().getArray("LAST_EXECUTIONS").size();
1 Like