Getting specific data from nested object in a document using N1ql

SQL++
1

Hi,
I am having a document with the structure as follow:

docID: {
“docType”: “chat”,
“members”: {
memberID1: {
“status”: “member”,
},
memberID2: {
“status”: “member”,
},
memberID3: {
“status”: “follower”,
}
}
}

I would like to use a query to get all the objects inside memberList of the document with a specified status. For example, I want to get all of the objects in the memberList of this document with status “member”, which would return the 2 objects of memberID1 and memberID2.
Thank you in advance.

2 
CREATE INDEX ix1 ON default(ALL ARRAY  m.val.status FOR m IN  OBJECT_PAIRS(members) END) WHERE docType = "chat";

SELECT RAW {m.name : m.val } FROM default AS d
UNNEST  OBJECT_PAIRS(d.members) AS m
WHERE  d.docType = "chat"  AND m.val.status = "member";
3

Thank you so much for your reply, it worked like a charm :slight_smile: