CODEWITHSUNDEEP
azureazure-cosmosdb
Homewrecker
Homewrecker

Reputation: 1106

Iterating over array in Cosmos DB

I have a Cosmos DB where a document called Auditlog resides. The simplified structure is as follows:

[
    {
        "id": "1",
        "name": "A",
        "messages": [
            {
                "gps": {
                    "src": "GPS"
                },
                "ts": "0"
            }
        ]
    },
        {
        "id": "2",
        "name": "B",
        "messages": [
            {
                "gps": {
                    "src": "DR"
                },
                "ts": "1"
            }
        ]
    }
]

I want to filter the document to get all entries that have src: GPS. The result also needs to show the ID. I have no idea on how to accomplish this. I tried using the 'IN'-operator but without luck. Using the 'IN'-operator makes it impossible to display the ID. I tried:

SELECT * FROM c
IN Auditlog.messages
WHERE c.gps.src = "GPS"

The result is correct but I need the ID to be displayed in the result. The following just results in an array of empty objects:

SELECT c.id FROM c
IN Auditlog.messages
WHERE c.gps.src = "GPS"

Can someone please help me? Thanks in advance.

Regards

Upvotes: 0

Views: 1565

Answers (1)

Rafat Sarosh
Rafat Sarosh

Reputation: 965

SELECT c.id AS id
FROM c JOIN a IN c.messages
WHERE a.gps.src = "GPS"

result will be

[
    {
        "id": "1"
    },
    {
        "id": "2"
    }
]

Upvotes: 3

Related Questions