Unable to UNNEST when combining with USE KEYS

SQL++
,
1

Hi

I have document structure like below and want to pull documents whose keys are in snapShotId as in the given document. I am unable to unnest the value of snapShotsMappings with USE KEYS by writing the query something like below. Can anyone help me out in getting the query correct.

SELECT p.*
FROM promotions AS p
USE KEYS ARRAY_FLATTEN((SELECT snaps.snapShotId
FROM promotions UNNEST snapShotsMappings as snaps 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

2 
SELECT p.* 
FROM promotions  AS p
USE KEYS ARRAY_FLATTEN((SELECT RAW eventIds
                        FROM promotions  USE KEYS "3068b1e2-5cb6-458f-bfae-f1b9fe638b11" WHERE ANY v IN  snapShotsMappings SATISFIES v. snapShotId ="eff84cc6-4223-4e6f-840b-aeaa16408890" END ),1)
3

Hi @vsr1

Apologies if I hadn’t explain the problem correctly.
There is no participation of eventIds in the query,
I have updated the document to get more clear representation by removing eventIds from document.

{
“snapShotsMappings”: [
{
“lastEffectiveDateTime”: 1590127866599,
“snapShotId”: “eff84cc6-4223-4e6f-840b-aeaa16408890”
},
{
“lastEffectiveDateTime”: 1590127866500,
“snapShotId”: “996165d2-610e-4217-a567-be1304c4aa0f”
}
],
“_class”: “xxx.yyy.zzz”,
}

Suppose the above document documentId is 1234567890.

snapShotsMappings.snapShotId represents document Ids of other documents. I need to write a query to pull all the documents whose document Id is in snapShotsMappings.snapShotId.

Something like below:

SELECT p.*
FROM promotions AS p
USE KEYS ARRAY_FLATTEN((SELECT snaps.snapShotId
FROM promotions UNNEST snapShotsMappings as snaps USE KEYS “1234567890”),1)

Regards
Venkat

4

USE KEYS must be allowed only after bucket. Check syntax.

SELECT p.*
FROM promotions AS p
USE KEYS ARRAY_FLATTEN((SELECT RAW snapShotsMappings[*].snapShotId
FROM promotions USE KEYS "1234567890"),1)

OR

SELECT p.*
FROM promotions AS p
USE KEYS ARRAY_FLATTEN((SELECT RAW snaps.snapShotId
FROM promotions USE KEYS "1234567890" UNNEST snapShotsMappings as snaps ),1)
1 Like
5

Hi @vsr1

Many Thanks, I got solution for almost all my queries by you.

Can you also suggest which of the above two are better performant??

Regards,
Venkat

6

First one. Avoid UNNEST (which is Self JOIN and can expand results), if you can.

If you think you have duplicates USE like this. USE KEYS ARRAY_DISTINCT (ARRAY_FLATTEN(…))

7

Sure. Many thanks @vsr1