What is the Cosmos DB NoSQL query language and how does it differ from standard SQL?

The Cosmos DB NoSQL API supports a SQL-like query language (sometimes called Cosmos DB SQL or the NoSQL query language) that is syntactically close to ANSI SQL but adapted for JSON documents. You use familiar keywords — SELECT, FROM, WHERE, ORDER BY, GROUP BY, JOIN — but with several important extensions and differences.

Key differences from standard SQL:

• FROM clause is the container alias — FROM c makes c an alias for each item in the container, not a table. You navigate JSON fields with dot notation: c.address.city.
• Array iteration with JOIN — Cosmos DB JOIN is not a relational join between two containers; it is a self-join that flattens arrays. SELECT t FROM c JOIN t IN c.tags produces one row per array element, allowing per-element filtering.
• Built-in JSON functions — Functions like IS_ARRAY(), IS_NULL(), ARRAY_CONTAINS(), STRINGEQUALS(), and dozens of math/string helpers operate directly on JSON types.
• No multi-container JOINs — You cannot JOIN two different containers in one query the way you JOIN two SQL tables. Data denormalization is required instead.
• Undefined vs null — A field that does not exist on a document returns undefined, not null. You must check for both: WHERE IS_DEFINED(c.region).

Example query using Cosmos DB-specific syntax:

-- Flatten tags array and filter by tag value
SELECT c.id, c.title, t AS tag
FROM c
JOIN t IN c.tags
WHERE t = "azure"
  AND c.published = true
ORDER BY c.createdAt DESC