带子查询的Couchbase

Question

我将编写一个有子查询的案例，如下所示，但既不能输出，也不能输出错误：

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是我的收藏，scpPC是我的范围，bktsample是我的桶。

Answer 1

家族集合中的每个文档都应该返回一个geo_id文档(不是空数组就是ID对象)

对查询的小更改：

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 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;