Need suggestion on how to write query when subquery returns an array

SQL++
,
1

Hi

I have small query where I first need to get list of documentIds from another document and then return documents from whose documentIds are in that list, I am trying the following query but it doesn’t work. Any help around it would be much appreciated.

select * from promotions promo where meta().id IN (
select raw eventIds from promotions where meta().id = ‘3068b1e2-5cb6-458f-bfae-f1b9fe638b11’
)

Regards,
Venkat

2

Hi

It worked using ARRAY_FLATTEN, does it incur any performance cost??

select * from promotions promo where meta().id IN
ARRAY_FLATTEN((select raw eventIds from promotions where meta().id = ‘3068b1e2-5cb6-458f-bfae-f1b9fe638b11’),1)

Regards,
venkat

3

As you know document keys the following is efficient way by avoiding index

SELECT p.* 
FROM promotions  AS p
USE KEYS ARRAY_FLATTEN((SELECT RAW eventIds
                        FROM promotions USE KEYS "3068b1e2-5cb6-458f-bfae-f1b9fe638b11"),1)
4

Hi @vsr1

Many thanks, will use USE KEYS then.

Regards
Venkat

5

Hi @vsr1

What if I have document structure like below and want to pull documents whose keys are in snapShotId, can you help me with that, something similar to the below suggestion given by you,

SELECT p.*
FROM promotions AS p
USE KEYS ARRAY_FLATTEN((SELECT RAW eventIds
FROM promotions USE KEYS “3068b1e2-5cb6-458f-bfae-f1b9fe638b11”),1)

{
“snapShotsMappings”: [
{
“lastEffectiveDateTime”: 1590127866599,
“snapShotId”: “eff84cc6-4223-4e6f-840b-aeaa16408890”
}
],
“_class”: “xxx.yyy.zzz”,
“eventIds”: [
“111aa496-cbcd-475b-91bd-f6c8001be20d”,
“996165d2-610e-4217-a567-be1304c4aa0f”
]
}

Regards,
Venkat

6 
SELECT p.* 
FROM promotions  AS p
USE KEYS ARRAY_FLATTEN((SELECT RAW eventIds
                        FROM promotions WHERE ANY v IN  snapShotsMappings SATISFIES v. snapShotId ="eff84cc6-4223-4e6f-840b-aeaa16408890" END ),1)