Working with JSON in Azure Cosmos DB

Nested properties

You can access nested JSON using a dot accessor. You can use nested JSON properties in your queries the same way that you can use any other properties.
Here's a document with nested JSON:
"id": "AndersenFamily", "lastName": "Andersen", "address": { "state": "WA", "county": "King", "city": "Seattle" "creationDate": 1431620472, "isRegistered": true
In this case, the state , country , and city properties are all nested within the address property.
The following example projects two nested properties: f.address.state and f.address.city .
    SELECT f.address.state, f.address.city
    FROM Families f
    WHERE f.id = "AndersenFamily"
The results are:
      "state": "WA",
      "city": "Seattle"
Working with arrays
In addition to nested properties, JSON also supports arrays.
Here's an example document with an array:
  "id": "WakefieldFamily",
  "children": [
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female",
        "grade": 1,
        "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8
When working with arrays, you can access a specific element within the array by referencing its position:
SELECT *
FROM Families f
WHERE f.children[0].givenName = "Jesse"
In most cases, however, you'll use a subquery or self-join when working with arrays.
For example, here's a document that shows the daily balance of a customer's bank account.
  "id": "Contoso-Checking-Account-2020",
  "balance": [
        "checkingAccount": 1000,
        "savingsAccount": 5000
        "checkingAccount": 100,
        "savingsAccount": 5000
        "checkingAccount": -10,
        "savingsAccount": 5000
        "checkingAccount": 5000,
        "savingsAccount": 5000
If you wanted to run a query that showed all the customers that had a negative balance at some point, you could use EXISTS with a subquery:
SELECT c.id
FROM c
WHERE EXISTS(
    SELECT VALUE n
    FROM n IN c.balance
    WHERE n.checkingAccount < 0
Difference between null and undefined
If a property is not defined in an item, then its value is undefined. A property with the value null must be explicitly defined and assigned a null value.
For example, consider this sample item:
  "id": "AndersenFamily",
  "lastName": "Andersen",
  "address": {
      "state": "WA",
      "county": "King",
      "city": "Seattle"
  "creationDate": null
In this example, the property isRegistered has a value of undefined because it is omitted from the item. The property creationDate has a null value.
Azure Cosmos DB supports two helpful type checking system functions for null and undefined properties:
IS_NULL - checks if a property value is null
IS_DEFINED - checks if a property value is defined
You can learn about supported operators and their behavior for null and undefined values.
Reserved keywords and special characters in JSON
You can access properties using the quoted property operator []. For example, SELECT c.grade and SELECT c["grade"] are equivalent. This syntax is useful to escape a property that contains spaces, special characters, or has the same name as a SQL keyword or reserved word.
For example, here's a document with a property named order and a property price($) that contains special characters:
  "id": "AndersenFamily",
  "order": {
         "orderId": "12345",
         "productId": "A17849",
         "price($)": 59.33
  "creationDate": 1431620472,
  "isRegistered": true
If you run a queries that includes the order property or price($) property, you will receive a syntax error.
SELECT * FROM c where c.order.orderid = "12345"
SELECT * FROM c where c.order.price($) > 50
The result is:
Syntax error, incorrect syntax near 'order'
You should rewrite the same queries as below:
SELECT * FROM c WHERE c["order"].orderId = "12345"
SELECT * FROM c WHERE c["order"]["price($)"] > 50
JSON expressions
Projection also supports JSON expressions, as shown in the following example:
    SELECT { "state": f.address.state, "city": f.address.city, "name": f.id }
    FROM Families f
    WHERE f.id = "AndersenFamily"
The results are:
      "$1": {
        "state": "WA",
        "city": "Seattle",
        "name": "AndersenFamily"
In the preceding example, the SELECT clause needs to create a JSON object, and since the sample provides no key, the clause uses the implicit argument variable name $1. The following query returns two implicit argument variables: $1 and $2.
    SELECT { "state": f.address.state, "city": f.address.city },
           { "name": f.id }
    FROM Families f
    WHERE f.id = "AndersenFamily"
The results are:
      "$1": {
        "state": "WA",
        "city": "Seattle"
      "$2": {
        "name": "AndersenFamily"
Aliasing
You can explicitly alias values in queries. If a query has two properties with the same name, use aliasing to rename one or both of the properties so they're disambiguated in the projected result.
Examples
The AS keyword used for aliasing is optional, as shown in the following example when projecting the second value as NameInfo:
    SELECT
           { "state": f.address.state, "city": f.address.city } AS AddressInfo,
           { "name": f.id } NameInfo
    FROM Families f
    WHERE f.id = "AndersenFamily"
The results are:
      "AddressInfo": {
        "state": "WA",
        "city": "Seattle"
      "NameInfo": {
        "name": "AndersenFamily"
Aliasing with reserved keywords or special characters
You can't use aliasing to project a value as a property name with a space, special character, or reserved word. If you wanted to change a value's projection to, for example, have a property name with a space, you could use a JSON expression.
Here's an example:
    SELECT
           {"JSON expression with a space": { "state": f.address.state, "city": f.address.city }},
           {"JSON expression with a special character!": { "name": f.id }}
    FROM Families f
    WHERE f.id = "AndersenFamily"
Next steps
Getting started
SELECT clause
WHERE clause