Keywords in Azure Cosmos DB

APPLIES TO: SQL API

This article details keywords which may be used in Azure Cosmos DB SQL queries.

BETWEEN

You can use the BETWEEN keyword to express queries against ranges of string or numerical values. For example, the following query returns all items in which the first child's grade is 1-5, inclusive.

    SELECT *
    FROM Families.children[0] c
    WHERE c.grade BETWEEN 1 AND 5

You can also use the BETWEEN keyword in the SELECT clause, as in the following example.

    SELECT (c.grade BETWEEN 0 AND 10)
    FROM Families.children[0] c

In SQL API, unlike ANSI SQL, you can express range queries against properties of mixed types. For example, grade might be a number like 5 in some items and a string like grade4 in others. In these cases, as in JavaScript, the comparison between the two different types results in Undefined, so the item is skipped.

DISTINCT

The DISTINCT keyword eliminates duplicates in the query's projection.

In this example, the query projects values for each last name:

SELECT DISTINCT VALUE f.lastName
FROM Families f

The results are:

[
    "Andersen"
]

You can also project unique objects. In this case, the lastName field does not exist in one of the two documents, so the query returns an empty object.

SELECT DISTINCT f.lastName
FROM Families f

The results are:

[
    {
        "lastName": "Andersen"
    },
    {}
]

DISTINCT can also be used in the projection within a subquery:

SELECT f.id, ARRAY(SELECT DISTINCT VALUE c.givenName FROM c IN f.children) as ChildNames
FROM f

This query projects an array which contains each child's givenName with duplicates removed. This array is aliased as ChildNames and projected in the outer query.

The results are:

[
    {
        "id": "AndersenFamily",
        "ChildNames": []
    },
    {
        "id": "WakefieldFamily",
        "ChildNames": [
            "Jesse",
            "Lisa"
        ]
    }
]

Queries with an aggregate system function and a subquery with DISTINCT are not supported. For example, the following query is not supported:

SELECT COUNT(1) FROM (SELECT DISTINCT f.lastName FROM f)

LIKE

Returns a Boolean value depending on whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. You can write logically equivalent queries using either the LIKE keyword or the RegexMatch system function. You’ll observe the same index utilization regardless of which one you choose. Therefore, you should use LIKE if you prefer its syntax more than regular expressions.

Note

Because LIKE can utilize an index, you should create a range index for properties you are comparing using LIKE.

You can use the following wildcard characters with LIKE:

Wildcard character Description Example
% Any string of zero or more characters WHERE c.description LIKE “%SO%PS%”
_ (underscore) Any single character WHERE c.description LIKE “%SO_PS%”
[ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE c.description LIKE “%SO[t-z]PS%”
[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]). WHERE c.description LIKE “%SO[^abc]PS%”

Using LIKE with the % wildcard character

The % character matches any string of zero or more characters. For example, by placing a % at the beginning and end of the pattern, the following query returns all items with a description that contains fruit:

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

If you only used a % character at the end of the pattern, you’d only return items with a description that started with fruit:

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

Using NOT LIKE

The below example returns all items with a description that does not contain fruit:

SELECT *
FROM c
WHERE c.description NOT LIKE "%fruit%"

Using the escape clause

You can search for patterns that include one or more wildcard characters using the ESCAPE clause. For example, if you wanted to search for descriptions that contained the string 20-30%, you wouldn’t want to interpret the % as a wildcard character.

SELECT *
FROM c
WHERE c.description LIKE '%20-30!%%' ESCAPE '!'

Using wildcard characters as literals

You can enclose wildcard characters in brackets to treat them as literal characters. When you enclose a wildcard character in brackets, you remove any special attributes. Here are some examples:

Pattern Meaning
LIKE “20-30[%]” 20-30%
LIKE “[_]n” _n
LIKE “[ [ ]” [
LIKE “]” ]

IN

Use the IN keyword to check whether a specified value matches any value in a list. For example, the following query returns all family items where the id is WakefieldFamily or AndersenFamily.

    SELECT *
    FROM Families
    WHERE Families.id IN ('AndersenFamily', 'WakefieldFamily')

The following example returns all items where the state is any of the specified values:

    SELECT *
    FROM Families
    WHERE Families.address.state IN ("NY", "WA", "CA", "PA", "OH", "OR", "MI", "WI", "MN", "FL")

The SQL API provides support for iterating over JSON arrays, with a new construct added via the in keyword in the FROM source.

If you include your partition key in the IN filter, your query will automatically filter to only the relevant partitions.

TOP

The TOP keyword returns the first N number of query results in an undefined order. As a best practice, use TOP with the ORDER BY clause to limit results to the first N number of ordered values. Combining these two clauses is the only way to predictably indicate which rows TOP affects.

You can use TOP with a constant value, as in the following example, or with a variable value using parameterized queries.

    SELECT TOP 1 *
    FROM Families f

The results are:

    [{
        "id": "AndersenFamily",
        "lastName": "Andersen",
        "parents": [
           { "firstName": "Thomas" },
           { "firstName": "Mary Kay"}
        ],
        "children": [
           {
               "firstName": "Henriette Thaulow", "gender": "female", "grade": 5,
               "pets": [{ "givenName": "Fluffy" }]
           }
        ],
        "address": { "state": "WA", "county": "King", "city": "Seattle" },
        "creationDate": 1431620472,
        "isRegistered": true
    }]

Next steps