Getting started with SQL queries
APPLIES TO:
NoSQL
In Azure Cosmos DB for NoSQL accounts, there are two ways to read data:
Point reads - You can do a key/value lookup on a single item ID and partition key. The item ID and partition key combination is the key and the item itself is the value. For a 1 KB document, point reads typically cost 1 request unit with a latency under 10 ms. Point reads return a single whole item, not a partial item or a specific field.
Here are some examples of how to do Point reads with each SDK:
SQL queries - You can query data by writing queries using the Structured Query Language (SQL) as a JSON query language. Queries always cost at least 2.3 request units and, in general, will have a higher and more variable latency than point reads. Queries can return many items.
Most read-heavy workloads on Azure Cosmos DB use a combination of both point reads and SQL queries. If you just need to read a single item, point reads are cheaper and faster than queries. Point reads don't need to use the query engine to access data and can read the data directly. Of course, it's not possible for all workloads to exclusively read data using point reads, so support of SQL as a query language and schema-agnostic indexing provide a more flexible way to access your data.
Here are some examples of how to do SQL queries with each SDK:
The remainder of this doc shows how to get started writing SQL queries in Azure Cosmos DB. SQL queries can be run through either the SDK or Azure portal.
Upload sample data
In your API for NoSQL Azure Cosmos DB account, open the Data Explorer to create a container called Families
. After the container is created, use the data structures browser, to find and open it. In your Families
container, you will see the Items
option right below the name of the container. Open this option and you'll see a button, in the menu bar in center of the screen, to create a 'New Item'. You will use this feature to create the JSON items below.
Create JSON items
The following 2 JSON items are documents about the Andersen and Wakefield families. They include parents, children and their pets, address, and registration information.
The first item has strings, numbers, Booleans, arrays, and nested properties:
{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas" },
{ "firstName": "Mary Kay"}
],
"children": [
{
"firstName": "Henriette Thaulow",
"gender": "female",
"grade": 5,
"pets": [{ "givenName": "Fluffy" }]
}
],
"address": { "state": "WA", "county": "King", "city": "Seattle" },
"creationDate": 1431620472,
"isRegistered": true
}
The second item uses givenName
and familyName
instead of firstName
and lastName
:
{
"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
}
Query the JSON items
Try a few queries against the JSON data to understand some of the key aspects of Azure Cosmos DB's SQL query language.
The following query returns the items where the id
field matches AndersenFamily
. Since it's a SELECT *
query, the output of the query is the complete JSON item. For more information about SELECT syntax, see SELECT statement.
SELECT *
FROM Families f
WHERE f.id = "AndersenFamily"
The query results are:
[{
"id": "AndersenFamily",
"lastName": "Andersen",
"parents": [
{ "firstName": "Thomas" },
{ "firstName": "Mary Kay"}
],
"children": [
{
"firstName": "Henriette Thaulow", "gender": "female", "grade": 5,
"pets": [{ "givenName": "Fluffy" }]
}
],
"address": { "state": "WA", "county": "King", "city": "Seattle" },
"creationDate": 1431620472,
"isRegistered": true
}]
The following query reformats the JSON output into a different shape. The query projects a new JSON Family
object with two selected fields, Name
and City
, when the address city is the same as the state. "NY, NY" matches this case.
SELECT {"Name":f.id, "City":f.address.city} AS Family
FROM Families f
WHERE f.address.city = f.address.state
The query results are:
[{
"Family": {
"Name": "WakefieldFamily",
"City": "NY"
}
}]
The following query returns all the given names of children in the family whose id
matches WakefieldFamily
, ordered by city.
SELECT c.givenName
FROM Families f
JOIN c IN f.children
WHERE f.id = 'WakefieldFamily'
ORDER BY f.address.city ASC
The results are:
[
{ "givenName": "Jesse" },
{ "givenName": "Lisa"}
]
Remarks
The preceding examples show several aspects of the Azure Cosmos DB query language:
Since API for NoSQL works on JSON values, it deals with tree-shaped entities instead of rows and columns. You can refer to the tree nodes at any arbitrary depth, like
Node1.Node2.Node3…..Nodem
, similar to the two-part reference of<table>.<column>
in ANSI SQL.Because the query language works with schemaless data, the type system must be bound dynamically. The same expression could yield different types on different items. The result of a query is a valid JSON value, but isn't guaranteed to be of a fixed schema.
Azure Cosmos DB supports strict JSON items only. The type system and expressions are restricted to deal only with JSON types. For more information, see the JSON specification.
An Azure Cosmos DB container is a schema-free collection of JSON items. The relations within and across container items are implicitly captured by containment, not by primary key and foreign key relations. This feature is important for the intra-item joins that are described in Joins in Azure Cosmos DB.
Next steps
- Introduction to Azure Cosmos DB
- Azure Cosmos DB .NET samples
- SELECT clause
- Trying to do capacity planning for a migration to Azure Cosmos DB? You can use information about your existing database cluster for capacity planning.
- If all you know is the number of vcores and servers in your existing database cluster, read about estimating request units using vCores or vCPUs
- If you know typical request rates for your current database workload, read about estimating request units using Azure Cosmos DB capacity planner
Feedback
Submit and view feedback for