Select Subdocumnet with N1QL

SQL++
1

I have following document

[
{
“report”: [
{
“action”: [
{
“commentCount”: “0”,
“flagCount”: “0”,
“likeCount”: “0”
}
],
“cat_id”: “1”,
“child_cat_id”: “5”,
“expiry_date”: “1807361887”,
“identifier”: “c5ed4cad9a5b3ecc7a238aca60a04926”,
“images”: [],
“modified_by”: null,
“pin_create_date”: 1487938839,
“pin_id”: “15689”,
“pin_privacy”: “1”,
“pin_rate”: “0”,
“pin_story”: “just a test please ignore”,
“pin_type”: “report”,
“place_id”: null,
“place_lat”: “22.563398”,
“place_long”: “88.351308”,
“place_name”: “Reported Event”,
“point”: “0”,
“progress”: “0”,
“social_id”: “web”,
“status”: “1”
},
{
“action”: [
{
“commentCount”: “0”,
“flagCount”: “0”,
“likeCount”: “0”
}
],
“cat_id”: “2”,
“child_cat_id”: “13”,
“expiry_date”: “1483228800”,
“identifier”: “1d5c5cccac39473f1d487a87503c979e”,
“images”: [],
“modified_by”: null,
“pin_create_date”: 1480535176,
“pin_id”: “11631”,
“pin_privacy”: “1”,
“pin_rate”: “0”,
“pin_story”: “again a test to test thing up”,
“pin_type”: “report”,
“place_id”: null,
“place_lat”: “28.77247418”,
“place_long”: “77.37876892”,
“place_name”: " 28.772474183943032,77.3787689",
“point”: “0”,
“progress”: “0”,
“social_id”: “web”,
“status”: “1”
},
{
“action”: [
{
“commentCount”: “0”,
“flagCount”: “0”,
“likeCount”: “0”
}
],
“cat_id”: “1”,
“child_cat_id”: “6”,
“expiry_date”: “1827291923”,
“identifier”: “0027459f3fa673bb58fa3ca397d44376”,
“images”: [],
“modified_by”: null,
“pin_create_date”: 1511759123,
“pin_id”: “46049”,
“pin_privacy”: “1”,
“pin_rate”: “0”,
“pin_story”: “Just a test report”,
“pin_type”: “report”,
“place_id”: “0”,
“place_lat”: “28.61279604”,
“place_long”: “77.22921111”,
“place_name”: “Reported Near War Memorial Arch”,
“point”: “0”,
“progress”: “0”,
“social_id”: “native”,
“status”: “1”
},
]}]
From above document I want to select only those report having cat_id = 1, I tried ANY and IN , but that did not work

Following is the where clause

WHERE 1 IN pin.report[*].cat_id

2

Please post the exact query you tried. Also note the document contains cat_id as string in the where you have number and in JSON both are not same.

SELECT report FROM default WHERE "1" IN report[*].cat_id;

3

Hi
this is the query I am using to get result having cat_id =

SELECT ARRAY item FOR item IN users.pin WHEN item.cat_id = ‘1’ AND item.child_cat_id != ‘0’ END AS item FROM users;

This is working but is their any other way of quering nested document and one more thing how do I get count of select document because ARRAY_COUNT is not working.

Thanks for replying

4

if you want the select nested document inside array,
if you know position you can directly refrence that position by users.pin[3].cat_id
If you don’t know position and first document matching by condition you can use FIRST item FOR item IN users.pin WHEN item.cat_id = ‘1’ AND item.child_cat_id != ‘0’ END
If you don’t know position and wants all matching documents you need to use ARRAY item FOR item IN users.pin WHEN item.cat_id = ‘1’ AND item.child_cat_id != ‘0’ END

If you need to get all the document count you need to use aggregate query.
If you need to find number of elements in ARRAY you can use ARRAY_COUNT(). Could you please more specific what it is not working.

SELECT  items, ARRAY_COUNT(items) AS cnt FROM users
LET items = ARRAY item FOR item IN users.pin WHEN item.cat_id = ‘1’ AND item.child_cat_id != ‘0’ END
WHERE  ARRAY_LENGTH(items) > 0 ;
5

@vsr1 I had to do use this

SELECT ARRAY_COUNT(issues) FROM(SELECT ARRAY item FOR item IN users.pin WHEN item.cat_id = ‘1’ AND item.child_cat_id != ‘0’ END AS issues FROM users) AS t1;
to get total count of result returned by query.

One more thing can I use LIMIT function or ORDER BY function in the same query
SELECT ARRAY item FOR item IN users.pin WHEN item.cat_id = ‘1’ AND item.child_cat_id != ‘0’ END AS item FROM users;
to get sorted result with defined limit.

Thanks again for replying

6

Yes Checkout https://developer.couchbase.com/documentation/server/5.0/n1ql/n1ql-language-reference/select-syntax.html

Examples at https://developer.couchbase.com/documentation/server/5.0/n1ql/n1ql-language-reference/selectintro.html

If you need to sort the array or limit array elements use subquery expression you can find documentation . Also checkout Multiple join from different bucket