Select objects from nested array

SQL++
,
1

I want to select all the replies object where comment = “testcomment1” and user_id inside replies array element = 342 ? How can I achieve that ? I am new to couchbase
{
“blog”: “testblog1”,
“comments”: [
{
“comment”: “testcomment1”,
“replies”: [
{
“text”: “Hello”,
“user_id”: 342
},
{
“text”: “another reply”,
“user_id”: 473
}
],
“user_id”: 883
},
{
“comment”: “testcomment2”,
“user_id”: 790
}
],
“user_id”: 41
}

2

Whole document

SELECT d.* 
FROM default AS d 
WHERE ANY v IN d.comments SATISFIES v.comment = "testcomment1" AND 
                     (ANY u IN v.replies SATISFIES u.user_id = 342 END) END;

Only matched replies

SELECT  ARRAY_FLATTEN(ARRAY (ARRAY u FOR u IN v.replies WHEN u.user_id = 342 END) FOR v IN d.comments WHEN v.comment = "testcomment1" END),2) AS replies
    FROM default AS d 
    WHERE ANY v IN d.comments SATISFIES v.comment = "testcomment1" AND 
                         (ANY u IN v.replies SATISFIES u.user_id = 342 END) END;

https://blog.couchbase.com/working-json-arrays-n1ql/
https://blog.couchbase.com/making-the-most-of-your-arrays-with-array-indexing/
https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

1 Like
3

Thanks a lot for your reply. It is working.