Azure Cosmos DB: How to query using SQL?

Tip

The Azure Cosmos DB DocumentDB API or SQL (DocumentDB) API is now known as Azure Cosmos DB SQL API. You don't need to change anything to continue running your apps built with DocumentDB API. The functionality remains the same.

The Azure Cosmos DB SQL API supports querying documents using SQL. This article provides a sample document and two sample SQL queries and results.

This article covers the following tasks:

  • Querying data with SQL

Sample document

The SQL queries in this article use the following sample document.

{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam", 
        "givenName": "Jesse", 
        "gender": "female", "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
        "familyName": "Miller", 
         "givenName": "Lisa", 
         "gender": "female", 
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}

Where can I run SQL queries?

You can run queries using the Data Explorer in the Azure portal, via the REST API and SDKs, and even the Query playground, which runs queries on an existing set of sample data.

For more information about SQL queries, see:

Prerequisites

This tutorial assumes you have an Azure Cosmos DB account and collection. Don't have any of those? Complete the 5-minute quickstart or the developer tutorial to create an account and collection.

Example query 1

Given the sample family document above, following SQL query returns the documents where the id field matches WakefieldFamily. Since it's a SELECT * statement, the output of the query is the complete JSON document:

Query

SELECT * 
FROM Families f 
WHERE f.id = "WakefieldFamily"

Results

{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam", 
        "givenName": "Jesse", 
        "gender": "female", "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
        "familyName": "Miller", 
         "givenName": "Lisa", 
         "gender": "female", 
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}

Example query 2

The next query returns all the given names of children in the family whose id matches WakefieldFamily ordered by their grade.

Query

SELECT c.givenName 
FROM Families f 
JOIN c IN f.children 
WHERE f.id = 'WakefieldFamily'
ORDER BY f.children.grade ASC

Results

[
  { "givenName": "Jesse" }, 
  { "givenName": "Lisa"}
]

Next steps

In this tutorial, you've done the following:

  • Learned how to query using SQL

You can now proceed to the next tutorial to learn how to distribute your data globally.