Subquery doesnt give a result

SQL++
1

Hello

I have the following subquery which doesnt give me a result at all

select * from ap where custId in ( select distinct RAW b.custId from ap b where b.baId=‘12345’)
Both custId and baId are simple string attributes and are not array

What would be possibly wrong here , that query doesnt give a result at all here

Documents in my bucket are as mentioned below
AP* is the document key and rest is the value

AP1 : {“Name” : “ABC”,“custId”=“1”,“baId”=“999”}
AP2 : {“Name” : “ABC”,“custId”=“1”,“baId”=“999”}
AP3 : {“Name” : “ABC”,“custId”=“1”,“baId”=“999”}
AP4 : {“Name” : “ABC”,“custId”=“2”,“baId”=“888”}
AP5 : {“Name” : “ABC”,“custId”=“2”,“baId”=“888”}
AP6 : {“Name” : “ABC”,“custId”=“2”,“baId”=“888”}
AP7 : {“Name” : “ABC”,“custId”=“3”,“baId”=“777”}

2

I don’t see any issue checkout your data baId:“12345” is present.

Following works.

WITH ap AS ( [ {"Name" : "ABC","custId":"1","baId":"999"}, {"Name" : "ABC","custId":"1","baId":"999"}, {"Name" : "ABC","custId":"1","baId":"999"}, {"Name" : "ABC","custId":"2","baId":"888"}, {"Name" : "ABC","custId":"2","baId":"888"}, {"Name" : "ABC","custId":"2","baId":"888"}, {"Name" : "ABC","custId":"3","baId":"777"}, {"Name" : "ABC","custId":"2","baId":"12345"} ])
SELECT a.* FROM ap as a
WHERE a.custId IN ( SELECT DISTINCT RAW b.custId
                    FROM ap b
                    WHERE b.baId = "12345");

OR

WITH ap AS ( [ {"Name" : "ABC","custId":"1","baId":"999"}, {"Name" : "ABC","custId":"1","baId":"999"}, {"Name" : "ABC","custId":"1","baId":"999"}, {"Name" : "ABC","custId":"2","baId":"888"}, {"Name" : "ABC","custId":"2","baId":"888"}, {"Name" : "ABC","custId":"2","baId":"888"}, {"Name" : "ABC","custId":"3","baId":"777"}, {"Name" : "ABC","custId":"2","baId":"12345"} ]), 
  inlist AS ( SELECT DISTINCT RAW b.custId
                    FROM ap b
                    WHERE b.baId = "12345")
SELECT a.* FROM ap as a
WHERE a.custId IN inlist;