How to SQL query a nested list and array in a MS Azure CosmosDB?

Question

I have the following (Azure) CosmosDB (sub) structure, that has 2 nested arrays:

{
    "id": "documentTypes",
    "SomeThing": "SomeThing",
    "configuration": [
        {
            "language": "en",
            "list": [
                {
                    "id": 1,
                    "name": "Supporting Documents"
                },
                {
                    "id": 2,
                    "name": "Summary PDF"
                },
            ]
        }
    ],
}

I have tried the following queries, with poor results.

SELECT * FROM c WHERE c.documentTypes.configuration[0].list[0].id FROM c

and

SELECT
    p.id,       
    p.name
FROM f
    JOIN c IN f.configuration
    JOIN p IN c.list
WHERE c.id == 'documentTypes'

Q: How can I get only the list of name and ids?

not2qubit · Accepted Answer · 2022-03-31 13:25:51Z

1

You need this?

SELECT ARRAY(SELECT VALUE e FROM c JOIN d IN c["configuration"] JOIN e IN d["list"]) AS Result FROM c

Output:

[ { "Result": [ { "id": 1, "name": "Supporting Documents" }, { "id": 2, "name": "Summary PDF" } ] } ]

edited Mar 31, 2022 at 13:25

not2qubit

17.7k10 gold badges120 silver badges165 bronze badges

answered Mar 31, 2022 at 12:13

Sajeetharan

223k65 gold badges370 silver badges409 bronze badges

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

1 Comment

not2qubit Over a year ago

Indeed that partially worked, but gave too many results from other parts of the DB. Adding WHERE c.id = 'documentTypes' fixed the issue. Thank you!

not2qubit · Accepted Answer · 2022-03-31 13:21:16Z

1

My own solution was similar to Sajeetharan's:

SELECT list.id, list.name FROM c
    JOIN configuration IN c.configuration 
    JOIN list IN configuration.list
WHERE c.id = 'documentTypes'

This to me look a bit simpler by not needing [""] and the ARRAY() function, and also doesn't produce the additional Result item. I have not idea if there is any performance difference.

answered Mar 31, 2022 at 13:21

not2qubit

17.7k10 gold badges120 silver badges165 bronze badges

Collectives™ on Stack Overflow

How to SQL query a nested list and array in a MS Azure CosmosDB?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related