Couchbase N1QL nested json query with variable key

Question

I have documents. How can I write a query with nested json field? Query: Count value greater than 3 output: doc-1 and doc-3

Doc-1

"1": {
    "count":4,
    "name": "pen"
}

Doc-2

"2": {
    "count":1,
    "name": "eraser"
}

Doc-3

"3": {
    "count":43,
    "name": "book"
}

vsr · Accepted Answer · 2021-05-18 01:26:33Z

1

Convert the dynamic object (OBJECT_VALUES(), OBJECT_NAMES(), OBJECT_PAIRS()) into ARRAY and use ANY clause https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/objectfun.html

SELECT b.*
FROM mybucket AS b
WHERE ANY v IN OBJECT_VALUES(b) SATISFIES v.`count` > 3 END;

answered May 18, 2021 at 1:26

vsr

7,4441 gold badge13 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

An index must be created for the query to run.: CREATE index idx_count ON mybucket (DISTINCT ARRAY v. count FOR v IN OBJECT_VALUES(mybucket) END);