How to query an array inside another array in CosmosDB?

Question

I have something similar to the following json structure and I need to search for an specific filename in the Azure CosmosDB data explorer (no matter the position), I have been trying differents ways and also using CROSS APPLY FLATTEN but i cannot get it

{
"entityId": "f07256a5-0e60-412a-bcc9-2e1aa66b69f5",
"array1": [
    {
        
        "array2": [
            {
                "fileName": "filename1.pdf",
            },
            {
                "fileName": "filename2.pdf",
            }
        ]
    }
]

}

Any ideas? thanks

Looks like a few other questions are similar and answer this question, such as this one and this one — David Makogon
– David Makogon, Commented Feb 9, 2023 at 14:01

Sajeetharan · Accepted Answer · 2023-02-09 13:58:57Z

3

You need something like below,

SELECT 
    c.fileName  
FROM d
JOIN f IN d.array1
JOIN c IN f.array2
WHERE c.fileName = "filename1.pdf"

answered Feb 9, 2023 at 13:58

Sajeetharan

223k65 gold badges370 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juunas · Accepted Answer · 2023-02-09 13:58:15Z

3

This one works for me:

SELECT c.entityId
FROM c
JOIN one IN c.array1
JOIN two IN one.array2
WHERE two.fileName = 'filename1.pdf'

It uses self-joins to create an object for each filename and then filters from those which has the right filename.

answered Feb 9, 2023 at 13:58

juunas

59.6k13 gold badges130 silver badges171 bronze badges

Collectives™ on Stack Overflow

How to query an array inside another array in CosmosDB?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related