n1ql子查询处

Question

SELECT * 
FROM `masterdata` AS T1
WHERE T1.`$Type` = "ProEvent"
  AND Id IN (SELECT RAW A.DocId AS Id 
             FROM `masterdata` AS T
             UNNEST T.List AS A
             WHERE T.`$Type` = "CustomerAutoList"
               AND T.CustId = "202479")

当单独执行子查询时，将结果返回以下格式

[
  "PromoEvent::506427_43_503934_157",
  "PromoEvent::506427_43_509843_157"
]

，但是当在主查询中的“ where”条件中给出子查询时，结果为空

SELECT * 
FROM `masterdata` AS T1
WHERE T1.`$Type` = "ProEvent"
  AND Id IN (SELECT RAW A.DocId AS Id 
             FROM `masterdata` AS T
             UNNEST T.List AS A
             WHERE T.`$Type` = "CustomerAutoList"
               AND T.CustId = "202479")

Answer 1

您的查询应该起作用。检查provent文档的ID值。

尝试以下查询。如果仍然有发行项目文档并查看这些期望值，请检查其消除位置。

WITH docids AS (ARRAY_FLATTEN((SELECT RAW a.List[*].DocId
                               FROM `masterdata` AS a
                               WHERE a.`$Type`="CustomerAutoList" AND a.CustId="202479")
                              ), 1)
SELECT t.*
FROM `masterdata` AS t
WHERE t.`$Type`="ProEvent"
AND t.Id IN docids;

如果文档与“ prowent”的文档密钥相同

SELECT t.*
FROM `masterdata` AS a
JOIN `masterdata` AS t ON KEYS a.List[*].DocId
WHERE a.`$Type`="CustomerAutoList" AND a.CustId="202479";