From the course: Azure Cosmos DB: SQL API Deep Dive

SQL query syntax

From the course: Azure Cosmos DB: SQL API Deep Dive

Start my 1-month free trial

SQL query syntax

- [Instructor] Writing SQL queries for Azure Cosmos DB is a very familiar process if you already have experience writing SQL queries for other database engines. While there are a few unique differences, they won't impact the majority of your queries. Cosmos also includes special functions that can help you out in situations where you're trying to create queries based on loosely structured data. Let's start with the most common query. This query will select all fields for all documents in a container. This may seem a little confusing at first, but the name you use after the key word, FROM, really doesn't matter. This alias is provided by you and is then subsequently used to reference the container throughout the rest of your query. Here, we used the identifier employees, but we could have used anything. Want to reference your container using the names cats, or dogs, go right ahead. The name used here doesn't even need to be the name of your container. Once we establish the alias for our container, we can use it to project our query. Here we only returned three properties for each document. We could take it a step further and add WHERE clauses to create a filter for our query. In this example, we use our alias to filter our query so that it only returns documents whose corporate/tenure path have a value greater than three. Let's look at queries in action. We're going to visit the Azure portal again, return to our Cosmos account. From here, we can go to the Data Explorer to view what's in the account. There's already a database and container here, so let's expand both of them. We'll need to add some items to this container so we can use the Items node to do that. First, let's create an item for an employee named Juliaane with all of the fields that we anticipate using. Let's save this item. Now, let's create another item for an employee named Rich. Let's save this item too. We can keep adding items. Here's an item for Kelli that we will add and save. Here's another item for Grant that we will also add and save. Let's get a query editor open so we can test some queries. First, we will select everything, referencing our container with the name employees. Let's run this query. You'll see that this query returns all four documents. Let's do another query where we only project the fields we're interested in. We will project the ID, name, and company fields and run this query. Let's take it a step further by adding a filter where we only return results that have a tenure of one and three years. We will add this WHERE clause, and then execute our query again. I'm tired of typing out employees each time, so I'll create an alias of the alias named E, so that I can use it to make this query more concise. If I run the query again, there's no change to the results, but what if my dev team needs the data in another format? Let's reshape the query. By adding JSON curly braces, and then adding our own JSON properties, such as name, and a sub-document named credentials, with properties like internal, ID, and sponsor. And let's clean this query up. If I run this query again, you'll see that the results use the new JSON structure I specified. If I add the value keyword, the results will be flattened into simple JSON objects.

Contents