N1QL query for objects inside a object

[nagarajan_subbaraj]

Hi Team,
I have a document like below, how do we loop and get only the seq=1 object
{
“Plns”: [{
“Pln”: [{
“Num”: 1,
“Stps”: [{
“Seq”: 1
}
]
}
]
}
]
}

[dh]

Collection Operators | Couchbase Docs

HTH.

[nagarajan_subbaraj]

@nagarajan_subbaraj

{
“Plns”: [{
“Pln”: [{
“Num”: 1,
“Stps”: [{
“Seq”: 1
}
]
}
]
}
]
}

i tried to do, but not working
SELECT ARRAY {Z} FOR Z IN Y.Stps IN FOR Y IN X.Pln FOR X IN Plns WHEN Z.Seq=1 END
FROM bucket USE KEYS ‘DOC’

[dh]

Try:

SELECT RAW
  array_flatten(
    array_agg(
      ARRAY s
      FOR s
      IN array_flatten(
          ARRAY p[*].Stps
          FOR p IN b.Plns[*].Pln
          END
         ,2)
      WHEN s.Seq = 1
      END
    )
  ,2
  )
FROM `bucket` b USE KEYS["DOC"]

(The array_agg() & outermost array_flatten() are included in case you want to select from multiple documents at a time. If it will always be just one document then they can be omitted.)

This does return a single ARRAY as the result. If you want individual results rather than an array, SELECT from this ARRAY, e.g.

SELECT x.*
FROM
(
... the select above...
)[0] x

Or you can use UNNEST:

SELECT s.*
FROM `bucket` USE KEYS["DOC"]
UNNEST Plns p
UNNEST p.Pln pp
UNNEST pp.Stps s
WHERE s.Seq = 1

but be wary of UNNEST with large source documents as it’s [in effect] a Cartesian join.

HTH.