Query items in Azure Cosmos DB for NoSQL using .NET

Raksts
08/14/2024

APPLIES TO: NoSQL

Items in Azure Cosmos DB represent entities stored within a container. In the API for NoSQL, an item consists of JSON-formatted data with a unique identifier. When you issue queries using the API for NoSQL, results are returned as a JSON array of JSON documents.

Query items using SQL

The Azure Cosmos DB for NoSQL supports the use of Structured Query Language (SQL) to perform queries on items in containers. A simple SQL query like SELECT * FROM products will return all items and properties from a container. Queries can be even more complex and include specific field projections, filters, and other common SQL clauses:

SELECT 
    p.name, 
    p.description AS copy
FROM 
    products p 
WHERE 
    p.price > 500

To learn more about the SQL syntax for Azure Cosmos DB for NoSQL, see Getting started with SQL queries.

Query an item

Note

The examples in this article assume that you have already defined a C# type to represent your data named Product:

// C# record type for items in the container
public record Product(
    string id,
    string category,
    string name,
    int quantity,
    bool sale
);

To query items in a container, call one of the following methods:

GetItemQueryIterator<>
GetItemLinqQueryable<>

Query items using a SQL query asynchronously

This example builds a SQL query using a simple string, retrieves a feed iterator, and then uses nested loops to iterate over results. The outer while loop will iterate through result pages, while the inner foreach loop iterates over results within a page.

// Query multiple items from container
using FeedIterator<Product> feed = container.GetItemQueryIterator<Product>(
    queryText: "SELECT * FROM products p WHERE p.quantity > 10"
);

// Iterate query result pages
while (feed.HasMoreResults)
{
    FeedResponse<Product> response = await feed.ReadNextAsync();

    // Iterate query results
    foreach (Product item in response)
    {
        Console.WriteLine($"Found item:\t{item.name}");
    }
}

The Container.GetItemQueryIterator<> method returns a FeedIterator<> that is used to iterate through multi-page results. The HasMoreResults property indicates if there are more result pages left. The ReadNextAsync method gets the next page of results as an enumerable that is then used in a loop to iterate over results.

Alternatively, use the QueryDefinition to build a SQL query with parameterized input:

// Build query definition
var parameterizedQuery = new QueryDefinition(
    query: "SELECT * FROM products p WHERE p.quantity > @quantity"
)
    .WithParameter("@quantity", 10);

// Query multiple items from container
using FeedIterator<Product> filteredFeed = container.GetItemQueryIterator<Product>(
    queryDefinition: parameterizedQuery
);

// Iterate query result pages
while (filteredFeed.HasMoreResults)
{
    FeedResponse<Product> response = await filteredFeed.ReadNextAsync();

    // Iterate query results
    foreach (Product item in response)
    {
        Console.WriteLine($"Found item:\t{item.name}");
    }
}

Tip

Parameterized input values can help prevent many common SQL query injection attacks.

Query items using LINQ asynchronously

In this example, an IQueryable<> object is used to construct a Language Integrated Query (LINQ). The results are then iterated over using a feed iterator.

// Get LINQ IQueryable object
IOrderedQueryable<Product> queryable = container.GetItemLinqQueryable<Product>();

// Construct LINQ query
var matches = queryable
    .Where(p => p.quantity > 10);

// Convert to feed iterator
using FeedIterator<Product> linqFeed = matches.ToFeedIterator();

// Iterate query result pages
while (linqFeed.HasMoreResults)
{
    FeedResponse<Product> response = await linqFeed.ReadNextAsync();

    // Iterate query results
    foreach (Product item in response)
    {
        Console.WriteLine($"Matched item:\t{item.name}");
    }
}

The Container.GetItemLinqQueryable<> method constructs an IQueryable to build the LINQ query. Then the ToFeedIterator<> method is used to convert the LINQ query expression into a FeedIterator<>.

Tip

While you can iterate over the IQueryable<>, this operation is synchronous. Use the ToFeedIterator<> method to gather results asynchronously.

Next steps

Now that you've queried multiple items, try one of our end-to-end tutorials with the API for NoSQL.

Build an app that queries and adds data to Azure Cosmos DB for NoSQL

Kopīgot, izmantojot