How do we write the following N1QL

I have the below project records.

"projectkey1" : { 
 "name" : "project name",
"productDetails" :{
"productKey1":"requested",
"productkey2":"requested"
} 
"type" : "project"
}

Now each product is stored as below are
 "productKey1"(key) : {"description" : "product1" , "type" : "product"}

We have multiple project records but what products are associated with the project is shown in the above key as key value pairs with the actual product key as a key in the project record. Also there is no limit on how many product keys a project can have. Given these requirements how can i write a N1ql that will give the projectname and the product description in the same json

Some thing like

 [{ 
    "name" : "project name",
    "product list" : ["product description1", "product description2"]
    },
    { 
    "name" : "project name2",
    "product list" : ["product description3", "product description4"]
    }
]

PS : all the records are in the same bucket

CREATE INDEX ix1 ON  default(name ) WHERE type = "project";
OR
CREATE INDEX ix2 ON default (name, OBJECT_NAMES(productDetails) ) WHERE type = "project";

SELECT p.name,
       (SELECT RAW pd.description
        FROM default AS pd USE KEYS OBJECT_NAMES(p.productDetails)
        WHERE pd.type = "product"
       ) AS productList
FROM default AS p
WHERE p.type = "project"
      AND p.name IS NOT NULL;

Use ix2 product keys limited say less than 50 otherwise index can ballon

I tried the above idea . But facing some issue . any pointers ? I realised i cant use keys as keys have a constant string concatenated to it. Instead the records have same product id values as UUID
Added this -
select raw api_external.data.displayName from api_external where api_external.type = ‘product’ and api_external.uuid in OBJECT_NAMES(t2.productStatus)

error -

“code”: 5370,
“msg”: “Unable to run subquery - cause: FROM in correlated subquery must have USE KEYS clause: FROM api_external.”,

WITH ct3 AS (select t1.uuid as uuid_proj ,
t1.appName as appName ,
t1.description as description,
t2.env as env,
(select raw api_external.data.displayName from api_external where api_external.type = ‘product’ and api_external.uuid in OBJECT_NAMES(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),
ct6 AS ( 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
)
SELECT t3.appName,
t3.uuid_proj as uuid,
t3.description,
ARRAY_AGG({t3.env,
t3.productStatus }) as productDetails
FROM ct3 AS t3
JOIN ct6 AS t6 ON t3.uuid_proj = t6.uuid_agg and t3.env = t6.env
group by t3.appName , t3.uuid_proj , t3.description ;

you mentioned product document document key is same as other field.
Is where api_external.type = ‘product’ and api_external.uuid
uuid is same as document key?
In that case change query

(select raw api_external.data.displayName
from api_external USE KEYS OBJECT_NAMES(t2.productStatus) where api_external.type = ‘product’ ) as productStatus

If not then you need CB 7.0 or need to do JOIN

It is the same but its appended by a constant string “product”:{product_key}. So I cant use the key directly.

(select raw api_external.data.displayName
   from api_external USE KEYS (ARRAY "product:" || v FOR v IN OBJECT_NAMES(t2.productStatus)  END) ) as productStatus