Handling special characters in ARRAY_CONTAINS search in cosmos sql query-CodePudding

I have db structure like this :

"selfId": "cd29433e-f36b-1410-851b-009d805073d7",
"selfName" : "A",
"bookIds": [
    "2f51bfd4",
    "2f3a1010",
    "090436c0",
    "1078c3b2",
    "b63b06e0"
]

I am working in C# and get bookId as a string. I am writing query as :

string SQLquery = string.Format("select c.selfName from c where ARRAY_CONTAINS(c.bookIds,\"" bookId "\"");

But When bookId contains special character, then query is giving error. For example if bookId = "AK"s" book" (please note id itself contains ") , then executing sqlQuery is giving error.

CodePudding user response：

When a text contains quotes, slashes, special characters, you can use Escape Sequence

The quoted property operator [""] can also be used to access properties. for example, SELECT food.id and SELECT food["id"] are equal. This syntax can be used to escape a property with spaces, special characters, or a name that is the same as a SQL keyword or reserved term.

Example:

SELECT food["id"]
FROM food 
WHERE food["foodGroup"] = "Snacks"  and food["id"] = "19015"

Reference :- https://docs.microsoft.com/en-us/azure/cosmos-db/sql/sql-query-constants#bk_arguments