CODEWITHSUNDEEP
subquerycouchbasesql++
Doss
Doss

Reputation: 27

Sub query in where condition not working - N1QL

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
]

Upvotes: 1

Views: 333

Answers (1)

vsr
vsr

Reputation: 7414

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/

Upvotes: 1

Related Questions