Azure CosmosDB Query Examples

This are examples of queries to use for Azure CosmosDB, as this database uses a restricted version of SQL and it doesn’t allow all queries or joins.

Partial queries

To be used with the main, partial query. This query starts with something such as SELECT * FROM c

WHERE c.ts LIKE "%2022/03/10%" 
ORDER BY c.ts DESC

WHERE c.type = "Writeable" 
AND c.ts LIKE "%2022/03/10, 15:2%" 
ORDER BY c.ts DESC

Full queries

Normal SQL queries to be used as custom queries. Note that CosmosDB doesn’t support a 100% of all SQL statements.

(count number of entries)

SELECT COUNT(1) FROM c 
	WHERE c.ts LIKE "%2022/03/10%" 
	ORDER BY c.ts DESC

Group By .. Where

(group and count entries)

SELECT c.type, c.email, COUNT(1) AS finds
	FROM c
	WHERE c.email = ""
	GROUP BY c.type, c.email

Subqueries

This is just an example for subqueries’ structure.

SELECT *
    FROM c
    WHERE ARRAY_CONTAINS(
        ARRAY(
            SELECT VALUE c.id
                FROM c
                WHERE c.type = 'Writeable'
                AND c.ts > "2022/09/01"
                GROUP BY c.id
        ),
        c.id,
        false
    )