N1QL Use Keys within a CTE

SQL++
1

Hi,
I am struggling with a N1QL where I have a CTE and Use Keys.

select ARRAY(a.PrmtnDocId) for a in AList end as PGEvent from bucket1 use keys [“CAList::42327345_157”];
[
{
"PGEvent ": [
“PGEvent ::506554_26_316238_157”,
“PGEvent ::506554_26_507681_157”,
“PGEvent ::506554_26_507682_157”,
“PGEvent ::506554_27_316238_157”,
“PGEvent ::506554_27_321900_157”,
]
}

Now, I want to use a CTE to list the PGEvent docs. This is what I tried and obviously is wrong… how do I make this work?

with x as (select ARRAY(a.PrmtnDocId) for a in AList end as PGEvent from bucket1 use keys [“CAList::42327345_157”])
select * from bucket1 use keys x;

2

Subquery produces ARRAY.
Each element of subquery is OBJECT of ARRAY of keys. Remove Object by using RAW. This will give array of keys.
x will have ARRAY of ARRAY of keys. But USE KEYS required ARRAY of keys. Use ARRAY_FLATTEN()

WITH x AS (SELECT RAW ARRAY v.PrmtnDocId FOR v IN b.AList END
           FROM bucket1 AS b USE KEYS ["CAList::42327345_157"])
SELECT b2.*
FROM bucket1 AS b2 USE KEYS ARRAY_FLATTEN(x,1);