Search code examples
sqlazureazure-cosmosdbcosmos

Unable to fetch results when JSON includes sql keywords

I am trying to fetch records on cosmos db that match a certain condition but I have been unable to, looks like cosmos is complaining about left and I have been unable to find an alternative.

Here is the query that I have been trying to fetch records with:

SELECT * 
FROM Rx c 
where c.tenantId = '5c6cb2d77c1c2edc001b9007' AND 
      c.left.series ='Clariti 1 Day Toric 30pk'

Below is a sample JSON document

{
"startDate": null,
"expirationDate": null,
"left": {
    "seriesId": "54b80bae7558391d0044ffd7",
    "productId": null,
    "productName": null,
    "series": "Clariti 1 Day Toric 30pk"
},
"right": null,
"tenantId": "5c6cb2d77c1c2edc001b9007"
}

Here is the error being returned: Syntax error, incorrect syntax near 'left'.

Solution

  • Your query should use [] instead of dot when you are querying nested objects

    Square bracket notation lets you to access properties containing special characters

    SELECT * 
FROM Rx c 
where c.tenantId = '5c6cb2d77c1c2edc001b9007' AND 
      c["left"]["series"] ='Clariti 1 Day Toric 30pk'

    DEMO

    enter image description here