Query and filter hierarchal data

Dears

We have documents stored in CB in following format

{
“a”: “value 1”,
“b”: “value 2”,
“status”: “ACTIVE”,
“childs”: [{
“a”: “value 3”,
“b”: “value 4”,
“status”: “ACTIVE”
},
{
“a”: “value 11”,
“b”: “value 12”,
“status”: “INACTIVE”
},
{
“a”: “value 5”,
“b”: “value 6”,
“status”: “ACTIVE”,
“childs”: [{
“a”: “value 7”,
“b”: “value 8”,
“status”: “INACTIVE”
}]
}]
}

Would it possible to have an N1QL which would return a result set ( still maintaining same hierarchy ) considering only Active nodes
Result set if we do not pass any status in query will be same as above
However if status is passed as “ACTIVE” then response would look like

{
“a”: “value 1”,
“b”: “value 2”,
“status”: “ACTIVE”,
“childs”: [{
“a”: “value 3”,
“b”: “value 4”,
“status”: “ACTIVE”
},
{
“a”: “value 5”,
“b”: “value 6”,
“status”: “ACTIVE”
}]
}

All the nodes with status as INACTIVE are filtered with hierarchy still kept
Is this filtering possible with N1QL ?

Regards

Select whole document and reconstruct ARRAY with required info then alias with original name ( In object field names are unique, Projection alias overwrites previous one when used by (.*), NOTE: Explicit duplicate alias in projection is not allowed).

   SELECT b.*,
        ARRAY v FOR v IN b.childs WHEN v.status != "INACTIVE" END AS childs
    FROM mybucket AS b
    WHERE .....;
1 Like

Thanks @vsr1 query works for first level of childs but isn’t working for second or levels beneath that , was trying this

SELECT b.*,ARRAY v FOR v WITHIN {childs} WHEN v.childs IS NOT MISSING END and v.status!= “INACTIVE” AS childs FROM bucket AS b USE KEYS “787483743863653756”

But still doesnt help.

You must know the level and construct that level. WITHIN will not work.

Thanks for your response @vsr1 So there is no generic query to solve this problem which is independent of level. May i understand it that way?

You can’t generically reconstruct object and replace field in arbitrary level . You can get whole document do that in application