How to write a LIKE query in Azure CosmosDB?

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

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")