Azure-cosmos: To fetch the document from container based on the key which contains an array of strings. Need help for non-strict comparsion

Question

ARRAY_CONTAINS function check for the strict comparison from an array of strings. Do we have any method that makes non-strict comparison for an array of strings from a given input string.

Example: We have a document in container like this

{ "id":1, "category":["cosmos","mongo"] }

But , if we are fetching for documents like SELECT u.id FROM users u WHERE ARRAY_CONTAINS(u.category,('Mongo'))

The above query returns null as it was checking for the strict comparison .Do we have any function for non-strict comparison?

Answer 1

You can use StringEquals. It can do a case-insensitive comparison and is still able to leverage the index.

SELECT c.id
FROM c
WHERE EXISTS (
    SELECT 1 
    FROM c 
    JOIN category IN c.category 
    WHERE StringEquals(category, 'Cosmos', true)
    )