Couchbase - Case with subquery

Question

I am going to write a case which has subquery, something like below, But I cannot get neither out put nor error:

select
case
when f.resGeo.isRural = true 
  then (select g.ID as geo_id
    from bktsample.scpPC.GeoInfo g 
    where g.PROVINCE_ID = f.resGeo.province.id 
     and g.CITYES_ID = f.resGeo.countie.id
     and g.PART_ID = f.resGeo.part.id
     and g.CITYORCOUNTRY_ID = f.resGeo.countie.id
     and g.VILLAGE_ID = f.resGeo.village.id)
when f.resGeo.isRural = false
  then (select g.ID 
    from bktsample.scpPC.GeoInfo g 
    where g.PROVINCE_ID = f.resGeo.province.id 
     and g.CITYES_ID = f.resGeo.countie.id
     and g.PART_ID = f.resGeo.part.id
     and g.CITYORCOUNTRY_ID = f.resGeo.countie.id) 
end as geo_id

from bktsample.scpPC.Family f;

PS: GEO is my collection, scpPC is my scope and bktsample is my bucket.

I'm confused by "neither out put nor error". Something must be happening when you execute this query. Is it timing out? What indexes have you created? — Matthew Groves

vsr vsr · Accepted Answer · 2022-07-27T15:55:45

Each document in the Family collection should have returned one document of geo_id (either empty array or objects of ID)

Small change to your query:

CREATE INDEX ix1 ON bktsample.scpPC.Family(resGeo.province.id, resGeo.countie.id, resGeo.part.id, resGeo.countie.id, resGeo.village.id, resGeo.isRural);
CREATE INDEX ix2 ON bktsample.scpPC.GeoInfo(PROVINCE_ID, CITYES_ID, PART_ID, CITYORCOUNTRY_ID, VILLAGE_ID, ID);

SELECT
    (SELECT
     SELECT g.ID AS geo_id
     FROM bktsample.scpPC.GeoInfo AS g
     WHERE g.PROVINCE_ID = f.resGeo.province.id
           AND g.CITYES_ID = f.resGeo.countie.id
           AND g.PART_ID = f.resGeo.part.id
           AND g.CITYORCOUNTRY_ID = f.resGeo.countie.id
           AND (f.resGeo.isRural == false OR g.VILLAGE_ID = f.resGeo.village.id))
    ) AS geo_ids
FROM bktsample.scpPC.Family f
WHERE f.resGeo.province.id IS NOT NULL;

Couchbase - Case with subquery

1 Answers