0

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'.

Sajeetharan
  • 216,225
  • 63
  • 350
  • 396
Jai
  • 155
  • 2
  • 9
  • I have tried that, the syntax error goes away but no results are returned. SELECT * FROM Rx c where c.tenantId = '5c6cb2d77c1c2edc001b9007' AND c.series='Clariti 1 Day Toric 30pk' SELECT * FROM Rx c where c.tenantId = '5c6cb2d77c1c2edc001b9007' AND "left.series"='Clariti 1 Day Toric 30pk' – Jai May 01 '19 at 17:54
  • The JSON field is named as left.series, we are new to Cosmos. If dataexplorer is complaining because left is a keyword, should we consider renaming the field? – Jai May 01 '19 at 18:01
  • Have you tried using surrounding quotes? This other question is quite similar to yours: https://stackoverflow.com/questions/30849310/using-reserved-word-field-name-in-documentdb – James May 01 '19 at 18:04
  • Thank you, I was unable to find it until you sent the link and yes, it is the solution that I was looking for. – Jai May 01 '19 at 18:46
  • Also, aside from the now-marked-as-duplicate question link shared by @JaimeDrq, I posted an answer to a related question, regarding the use of special characters within a property name, also requiring brackets to resolve properly. [link](https://stackoverflow.com/a/46219609/272109) – David Makogon May 01 '19 at 19:45

1 Answers1

2

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

Sajeetharan
  • 216,225
  • 63
  • 350
  • 396
  • 1
    Thank you Sajeetharan! no syntax errors and I am getting a result set back. – Jai May 01 '19 at 18:43
  • Just wondering, if the id was not used what would be a good index to satisfy the Where condition without full scan? Thanks. – NoChance Feb 05 '22 at 09:40