Return the content of a specific object in an arra

Question

This is a follow up to question 56126817

My current 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","value": "true" })

My Query Output

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

Question

How could change my query so that I select only the "value" of the array that contains the "name" "DetectionActive" ? The idea behind is to filter the query on one array entry and get as output the "value" of another array entry. From reading here, UDF (not the best in this case) and JOIN should be used.

First attempt

SELECT t.value FROM c JOIN t in c.EventType.EndDeviceEventDetail 
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","value": "true" })

Gets Bad Request (400) error

Answer 1

Your idea and direction is right absolutely, I simplified and tested your sql.

SELECT detail.value  FROM c 
join detail in c.EventType.EndDeviceEventDetail
WHERE c.EventType.EndDeviceEventType.eventOrAction = '93'
AND ARRAY_CONTAINS(c.EventType.EndDeviceEventDetail,{"name": 
"RCDSwitchReleased","value": "true" })

Found the error message as below:

It because that the value is the reserved word in cosmos db sql syntax,please refer to this case:Using reserved word field name in DocumentDB

You could try to modify the sql like:

SELECT detail["value"]  FROM c