Search multiple keywords in DocumentDB collection

Question

I have a Azure DocumentDB collection with a 100 documents. I have tokenized an array of search terms in each document for performing a search based on keywords.

I was able to search on just one keyword using below SQL query for DocumentDB:

SELECT VALUE c FROM root c JOIN word IN c.tags WHERE CONTAINS(LOWER(word), LOWER('keyword'))

However, this only allows search based on single keyword. I want to be able to search given multiple keywords. For this, I tried below query:

SELECT * FROM c WHERE ARRAY_CONTAINS(c.tags, "Food") OR ARRAY_CONTAINS(c.tags, "Dessert") OR ARRAY_CONTAINS(c.tags, "Spicy")

This works, but is case-sensitive. How do I make this case-insensitive? I tried using scalar function LOWER like this

LOWER(c.tags), LOWER("Dessert")

but this doesn't seem to work with ARRAY_CONTAINS.

Any idea how I can perform a case-insensitive search on multiple keywords using SQL query for DocumentDB?

Thanks,

AB

Answer 1

The best way to deal with the case sensitivity is to store them in the tags array with all lower case (or upper case) and then just do LOWER(<user-input-tag>) at query time.

As for your desire to search on multiple user input tags, your approach of building a series of OR clauses is probably the best approach.