N1QL查询删除与子查询连接后的记录

Question

当我加入2个N1QL子查询时，下面的查询正在删除记录-我们正在使用couchbase和N1QL查询。

Full Query - 
select
t3.appName,
t3.uuid_proj as uuid,
t3.description,
t3.env,
t3.productStatus
from      
( select 
t1.uuid as uuid_proj ,
t1.appName as appName ,
t1.description as description,
t2.env as env,
t2.productStatus as productStatus
from 
(
select 
api_external.uuid ,
api_external.data.appName , 
api_external.data.description 
from `api_external` 
where type = 'partnerApp' 
and data.companyId = '70a149da27cc425da86cba890bf5b143' )t1
join 
(
select 
api_external.data.env,
api_external.data.productStatus,
api_external.data.partnerAppId
from 
`api_external` 
where type = 'integration' )t2
on t1.uuid = t2.partnerAppId 
) as t3 
join ( 
select t4.uuid as uuid_agg , min(t5.env) as env
from
(select api_external.uuid   from `api_external` where type = 'partnerApp' and data.companyId = '70a149da27cc425da86cba890bf5b143' )as t4 join
(select api_external.data.env, api_external.data.partnerAppId from `api_external` where type = 'integration' ) as t5
on t4.uuid = t5.partnerAppId
group by t4.uuid 
) as t6 
on
t3.uuid_proj = t6.uuid_agg and t3.env = t6.env

如您所见，它有2个子查询-下面的子查询给出了16条记录-

select    
t1.uuid as uuid_proj 
from 
(
select 
api_external.uuid ,
api_external.data.appName , 
api_external.data.description 
from `api_external` 
where type = 'partnerApp' 
and data.companyId = '70a149da27cc425da86cba890bf5b143' )t1
join 
(
select 
api_external.data.env,
api_external.data.productStatus,
api_external.data.partnerAppId
from 
`api_external` 
where type = 'integration' )t2
on t1.uuid = t2.partnerAppId
group by t1.uuid

另一个子查询也给出了16条记录-

select t4.uuid as uuid_agg , min(t5.env) as env
from
(select api_external.uuid   from `api_external` where type = 'partnerApp' and data.companyId = '70a149da27cc425da86cba890bf5b143' )as t4 join
(select api_external.data.env, api_external.data.partnerAppId from `api_external` where type = 'integration' ) as t5
on t4.uuid = t5.partnerAppId
group by t4.uuid 

在同一粒度UUID上的两个查询的逻辑连接也必须给出16条记录。但它只给出1。我做错了什么，请帮助

Answer 1

该查询使用了许多子查询并击中了问题。尝试以下简化版本

CREATE INDEX ix1 ON api_external(data.companyId, uuid, data.appName, data.description) WHERE type = "partnerApp";
CREATE INDEX ix2 ON api_external(data.partnerAppId, data.env, data.productStatus) WHERE type = "integration";
WITH ct3 AS (SELECT t1.uuid, t1.data.appName, t1.data.description,
t2.data.env, t2.data.productStatus
FROM api_external AS t1
JOIN api_external AS t2 ON t1.uuid = t2.data.partnerAppId
WHERE t1.type = "partnerApp"
AND t1.data.companyId = "70a149da27cc425da86cba890bf5b143"
AND t2.type = "integration"
AND t2.data.partnerAppId IS NOT NULL),
ct6 AS ( SELECT t4.uuid AS uuid_agg , MIN(t5.data.env) AS env
FROM api_external AS t4
JOIN api_external AS t5 ON t4.uuid = t5.data.partnerAppId
WHERE t4.type = "partnerApp"
AND t4.data.companyId = "70a149da27cc425da86cba890bf5b143"
AND t5.type = "integration"
AND t5.data.partnerAppId IS NOT NULL
GROUP BY t4.uuid)
SELECT t3.*
FROM ct3 AS t3
JOIN ct6 AS t6 ON t3.uuid = t6.uuid_agg and t3.env = t6.env;

如果结果相同，请参见以下作品。JOIN后获取MIN env结果的所有字段，记录每个组

SELECT m[1].*
FROM api_external AS t4
JOIN api_external AS t5 ON t4.uuid = t5.data.partnerAppId
WHERE t4.type = "partnerApp"
AND t4.data.companyId = '70a149da27cc425da86cba890bf5b143'
AND t5.type = "integration"
AND t5.data.partnerAppId IS NOT NULL
GROUP BY t4.uuid
LETTING m = MIN([t5.data.env, {t4.uuid, t4.data.appName, t4.data.description,
t5.data.env, t5.data.productStatus}]);