HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Getting Nested query result from Cosmos DB with property named "Value"

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
resultwithquerygettingnamedvaluenestedpropertycosmosfrom

Problem

I have Cosmos Db collection, I am trying to query the "Key" "Value" pair from.
Working query:

SELECT ed
From c
JOIN ed IN c.ExtendedData
Where ed.Key = "MembershipTypeId"


And got result:

[
    {
        "ed": {
            "Key": "MembershipTypeId",
            "Value": "224"
        }
    }
]


I need to filter by "Key" and "Value", but "Value" appears to be a reserved keyword
Not working query:

SELECT ed
From c
JOIN ed IN c.ExtendedData
Where ed.Value = "224"


Got HTTP 400 with message: Syntax error, incorrect syntax near 'Value'

Solution

If property name is the same as reserved word (in this case Value),
following syntax could be used:

["Value"]


In the case above here is working query:

SELECT ed 
FROM c 
JOIN ed IN c.ExtendedData 
WHERE ed.Key = "MembershipTypeId" 
AND ed["Value"] = "224"

Code Snippets

SELECT ed 
FROM c 
JOIN ed IN c.ExtendedData 
WHERE ed.Key = "MembershipTypeId" 
AND ed["Value"] = "224"

Context

StackExchange Database Administrators Q#250173, answer score: 3

Revisions (0)

No revisions yet.