How to write a LIKE query in Azure CosmosDB?

Question

I want to retrieve data from Cosmos DB with the following query:

SELECT * FROM c WHERE c.pi like '09%001'

(This is a SQL query, which I can use in MySQL)

Here, pi is a string value, which can be 09001001 or 09025001.

Is there a way to use a LIKE command in Cosmos DB?

I know that cosmos DB uses CONTAINS, but this cannot be used when you want to match specifically the beginning or end of the string.

Solution 1

Another possibility is creating your own User Defined Function. As example here's a regex check:

function matchRegex(str, pattern) {
    let regex=RegExp(pattern);
    return regex.test(str);
}

Created under the name MATCH_REGEX it can then be used like:

SELECT udf.MATCH_REGEX("09001001", "^09.*001$")

As note: it'll kill any index optimization that for instance STARTSWITH would have. Although allows for much more complex patterns. It can therefor be beneficial to use additional filters that are capable of using the index to narrow down the search. E.g. using StartsWith(c.property1, '09') as addition to the above example in a WHERE clause.

UPDATE:

Cosmos now has a RegexMatch function that can do the same. While the documentation for the MongoApi mentions the $regex can use the index to optimize your query if it adheres to certain rules this does not seem to be the case for the SqlApi (at this moment).

Solution 2

UPDATE :

You can now use the LIKE keyword to do text searches in Azure Cosmos DB SQL (core) API!

EXAMPLE:

SELECT *
FROM c
WHERE c.description LIKE "%cereal%"

OLD Answer:

This can be achieved in 2 ways

(i) Currently Azure Cosmosdb supports the CONTAINS, STARTSWITH, and ENDSWITH built-in functions which are equivalent to LIKE.

The keyword for LIKE in Cosmosdb is Contains .

SELECT * FROM c WHERE CONTAINS(c.pi, '09')

So, in your case if you want to match the pattern 09%001, you need to use:

SELECT * FROM c WHERE STARTSWITH(c.pi, '09') AND ENDSWITH(c.pi, '001')

(ii) As 404 mentioned, Use SQL API User Defined Functions which supports regex :

function executeRegex(str, pattern) {
    let regex=RegExp(pattern);
    return regex.test(str);
}

SELECT udf.EXECUTE_REGEX("foobar", ".*bar")

Comments

[arjan kroon]

That is partly the sollution. If pi contains the string 09001053 . Then this entry will be fetched from the database with the query in your answer. Would the solution then be: `SELECT * FROM c WHERE STARTSWITH(c.pi, '09') AND ENDSWITH(c.pi,'001'). Ps. I already voted on your link.

[Sajeetharan]

yes you need to use either one of the above queries and try to achieve the same