Feedback Edit

Tutorial: Query Azure Cosmos DB by using the SQL API

  • Article
  • 2 minutes to read

APPLIES TO: SQL API

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 and via the REST API and SDKs.

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 resources? Complete the 5-minute quickstart.

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.

Query

    SELECT c.givenName 
    FROM Families f 
    JOIN c IN f.children 
    WHERE f.id = 'WakefieldFamily'

Results

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

Next steps

In this tutorial, you've done the following tasks:

  • Learned how to query using SQL

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

Distribute your data globally

Trying to do capacity planning for a migration to Azure Cosmos DB? You can use information about your existing database cluster for capacity planning.