Mastering Cosmos DB through Basic and Advanced Concepts with C#/

...

/

Advanced NoSQL Operations

Advanced NoSQL Operations

Learn some of the features and functions we can use when we need to write complex queries.

We'll cover the following...

Introduction

Cosmos DB SQL syntax is rich and full of features specific to JSON documents. We lose some operations and gain a lot more compared to traditional SQL. To efficiently query the database, we must know what we can use with this specific implementation. This lesson will show a few examples of what the Cosmos DB SQL syntax can offer.

For the query examples below, we can use documents that look like the following. It represents a user of an online shop containing their whole purchase history.

Press + to interact
{
    "id": "user1",
    "name": "Bob Example",
    "age": 20,
    "address": {
        "street": "Street 1",
        "city": "London",
        "state": "UK"
    },
    "orders": [
        {
            "id": "order1",
            "total": 100,
            "date": "2030-01-11T12:12:12.1234567Z",
            "items": [
                {
                    "id": "item1",
                    "name": "Item 1",
                    "quantity": 4,
                    "pricePerUnit": 20
                },
                {
                    "id": "item2",
                    "name": "Item 2",
                    "quantity": 1,
                    "pricePerUnit": 20
                }
            ]
        }
    ]
}

Note: This document is just an example. It probably doesn’t fit the needs of a real e-commerce platform since everything about the customer is here. The higher the frequency a document is updated at, the higher the risk of conflicts.

Selection options

The following are the selection options in SQL:

The DISTINCT keyword

A classic keyword for SQL, DISTINCT is used to remove duplicated results. For example, we can get a list of all the states our customers come from.

Press + to interact
SELECT DISTINCT VALUE c.address.state FROM c

The TOP keyword

With TOP, we limit the number of results returned. Below, we get the names of the five oldest customers.

Press + to interact
SELECT TOP 5 c.name FROM c

ORDER BY c.age DESC

Projection and alias

...