How to query data in Cosmos db from nested json

Question

I have some difficulty in writing a query to query data from nested json in Cosmos db. Sample json -

{
    "id": xyz
    "items": [
        {
            "arr_id": 1,
            "randomval": "abc"
        },
        {
            "arr_id": 2,
            "randomval": "mno"
        },
        {
            "arr_id": 1,
            "randomval": "xyz"
        }
    ]
}

Lets say in above case, if i want to get all jsons data with arr_id = 1.

Expected Result -

{
    "id": xyz
    "items": [
        {
            "arr_id": 1,
            "randomval": "abc"
        },
        {
            "arr_id": 1,
            "randomval": "xyz"
        }
    ]
}

If i write a query like below, it still gives me entire json. Select * from c where ARRAY_CONTAINS(c.items, {"arr_id": 1},true)

I want it to filter it items level too. I guess it just filters at header level and provides entire json where even a single arr_id matches.

Answer 1

You can use either

SELECT c.id, ARRAY(SELECT VALUE i FROM i in c.items where i.arr_id = 1) as items
FROM c
WHERE EXISTS(SELECT VALUE i FROM i in c.items where i.arr_id = 1)

or

SELECT c.id, ARRAY(SELECT VALUE i FROM i in c.items where i.arr_id = 1) as items
FROM c

depending on whether you expect an empty array if no arrayItem with arr_id=1 exists or you wnat to filter out those records compeletely.

Also see this link for a good overview of query options across arrays - https://devblogs.microsoft.com/cosmosdb/understanding-how-to-query-arrays-in-azure-cosmos-db/