1

Below with sub query is not working. When I execute the sub query separately, I can see it returns results. But when this sub query is added to where condition, I get "Results" : []

Select T1.* FROM `masterdata` AS T1 
WHERE T1.doc="Calendar"
AND T1.Id=(SELECT SL.DocId FROM `masterdata` AS T 
             UNNEST T.Sllist AS SL
             WHERE T.doc="Cuslist"
             AND GtId IN ["1234567"])

Sub query returning the below result

[
sls::76543_77
]
Matthew Groves
  • 25,181
  • 9
  • 71
  • 121
Doss
  • 27
  • 4

1 Answers1

1

Subquery returns ARRAY of objects, The documents inside subquery are objects. Remove Object use RAW inside subquery (must be projected single element)

You have two options

Option 1: Assume subquery returns single document, if not then query might not give results because your value might be other elements of ARRAY.

Select T1.* FROM `masterdata` AS T1 
WHERE T1.doc="Calendar"
AND T1.Id= ((SELECT RAW SL.DocId FROM `masterdata` AS T 
             UNNEST T.Sllist AS SL
             WHERE T.doc="Cuslist"
             AND GtId IN ["1234567"])[0])

Option 2: Use IN clause

Select T1.* FROM `masterdata` AS T1 
    WHERE T1.doc="Calendar"
    AND T1.Id IN (SELECT RAW SL.DocId FROM `masterdata` AS T 
                 UNNEST T.Sllist AS SL
                 WHERE T.doc="Cuslist"
                 AND GtId IN ["1234567"])

Also checkout Subquery handling and potential query rewrite https://blog.couchbase.com/in-list-handling-improvements-in-couchbase-server-6-5/

vsr
  • 7,149
  • 1
  • 11
  • 10