0

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

Christopher C
  • 35
  • 9
  • Looks like you posted this question twice, the first being [here](https://stackoverflow.com/questions/56126817/check-the-content-of-an-array-cosmosdb). Curious as to why. Please don't repeat the same question. – David Makogon May 15 '19 at 11:35
  • This question (Return the content of a specific object in an array — CosmosDB) is a bit different from the one you refered as original, so thought it was more appropriate to post a follow up question. It is not recommended to post follow up questions in the comment field (as I did in the original post). – Christopher C May 15 '19 at 11:46
  • Looks like the exact same root-cause: using a reserved word in the query (which both answers call out). – David Makogon May 15 '19 at 11:52

1 Answers1

0

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:

enter image description here

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
Jay Gong
  • 23,163
  • 2
  • 27
  • 32
  • This should have been marked as a duplicate of the question (and answer) you referred to. There are actually more than one question (and answer) dealing with reserved keywords as property names, with slightly different context. – David Makogon May 15 '19 at 11:36