Check the content of an array -- CosmosDB

Question

I query a CosmosDB database using the following query :

SELECT c.EventType.EndDeviceEventDetail FROM c 
WHERE (c.EventType.EndDeviceEventType.eventOrAction = '93'
AND c.EventType.EndDeviceEventType.subdomain = '137'
AND c.EventType.EndDeviceEventType.domain = '26'
AND c.EventType.EndDeviceEventType.type = '3')

I get as a response

[
    {
        "EndDeviceEventDetail": [
            {
                "name": "Spontaneous",
                "value": "true"
            },
            {
                "name": "DetectionActive",
                "value": "true"
            },
            {
                "name": "RCDSwitchReleased",
                "value": "false"
            }
        ]
    },
    {
        "EndDeviceEventDetail": [
            {
                "name": "Spontaneous",
                "value": "true"
            },
            {
                "name": "DetectionActive",
                "value": "true"
            },
            {
                "name": "RCDSwitchReleased",
                "value": "true"
            }
        ]
    }
]

I would like to get as step further and modify my query so that I get a response only if "RCDSwitchReleased" is true.

I naïvely tried without success :

SELECT c.EventType.EndDeviceEventDetail FROM c 
WHERE (c.EventType.EndDeviceEventType.eventOrAction = '93'
AND c.EventType.EndDeviceEventType.subdomain = '137'
AND c.EventType.EndDeviceEventType.domain = '26'
AND c.EventType.EndDeviceEventType.type = '3')
AND c.EventType.EndDeviceEventDetail[2].value = 'true'

but I get a BadRequest (400) error message. Any direction/help to achieve this ?

score 0 · Accepted Answer · answered May 14 '19 at 14:15

0

An issue with Value Keyword. Cosmos SQL use Value Keyword in many different ways, this is might be a reason, we can't use value field in select query.

I Changed the document with value1 instead value then your query is working.

Suggestion

If you are applying a filter in the array, Always use Array_Contains. If order of value inside your array EndDeviceEventDetail would change, your query will not return the correct result.

My query

SELECT c.EventType.EndDeviceEventDetail FROM c 
WHERE c.EventType.EndDeviceEventType.eventOrAction = '93'
AND c.EventType.EndDeviceEventType.subdomain = '137'
AND c.EventType.EndDeviceEventType.domain = '26'
AND c.EventType.EndDeviceEventType.type = '3'
AND ARRAY_CONTAINS(c.EventType.EndDeviceEventDetail,{"name": 
"RCDSwitchReleased","value1": "true" })

Query Output

[
{
    "EndDeviceEventDetail": [
        {
            "name": "Spontaneous",
            "value1": "true"
        },
        {
            "name": "DetectionActive",
            "value1": "true"
        },
        {
            "name": "RCDSwitchReleased",
            "value1": "true"
        }
    ]
}
]

answered May 14 '19 at 14:15

Pankaj Rawat

4,037
6
41
73

This is very useful and works like a charm. Thanks for sharing your expertise Pankaj. – Christopher C May 15 '19 at 07:52
May I ask a follow-up question : how to return **only** the "value" of the array entry that contains the "name" "DetectionActive" ? Instead of returning as today the whole array. I tried sthg under (but returned a boolean) but seems that I might need some kind of JOIN here ? ` SELECT ARRAY_CONTAINS(c.EndDeviceEvent.Payload.EventType.EndDeviceEventDetail,{'name':'DetectionActive' }) FROM c ...etc... ` – Christopher C May 15 '19 at 09:05
I made a new question for this : https://stackoverflow.com/questions/56145931/return-the-content-of-a-specific-object-in-an-array-cosmosdb – Christopher C May 15 '19 at 09:28
Let me check this question – Pankaj Rawat May 15 '19 at 10:46

Check the content of an array -- CosmosDB

1 Answers1

Linked