Querying CosmosDB. Part 2

Hi!!!

We will continue to play a little with the same JSON Example in Part 1

We will try to do queries a little more complicated.

Total amount of time (in minutes) of laboratory in particular event.

Query

SELECT VALUE SUM(act.duration) FROM c
JOIN act IN c.activities
WHERE act.type=”lab” AND c.event_name= “CosmosDB Conf”

Results

List activities from specific type and from specific event

Query

SELECT VALUE act FROM c
JOIN act IN c.activities
WHERE act.type=”conference” AND c.event_name= “CosmosDB Conf”

Results

Get activities where duration is more than 30 minutes

Query

SELECT VALUE act FROM c JOIN act IN c.activities
WHERE act.duration>30

Results

Same as previous but bring me more information for each document

Query

SELECT VALUE { “id”:c.id,
“event”:c.event_name,
“speaker”:c.speaker,
“country”:c.event_country,
“activity”:act} FROM c
JOIN act IN c.activities
WHERE act.duration>30 and act.type=”lab”

Results

In case you have more complex JSON Document let us know. I’d like to help and be involve in other queries problems.

To be Continue…

Comments

Add more security to an Azure CosmosDB Account.

W e can access programmatically to Azure Cosmos DB resources to create, query, and delete databases, document collections, and documents via REST API. To perform operations on Azure Cosmos DB resources, you can send HTTPS requests with a selected method: GET, POST, PUT, or DELETE to an CosmosDB endpoint. Frequently we build architecture with centralized access to a persistence layer, then we won't let other applications access directly to this layer. Suppose we have the following hypothetical scenario (see Figure 1): Figure 1. Hypothetical Scenario Only WEB API Service with IP1 is allowed to establish connections with CosmosDB EndPoint. The others elements showed as Application are not allowed. By default we have in our CosmosDB resource the following configuration (see Figure 2) in Firewall and Virtual Networks option. Figure 2. Default Configuration To show you how this feature works, we are going to create a WEP API with a single function (r

Querying CosmosDB. Part 1

Hi CosmosDB Community !!! I'd like to contribute with some posts about how to query Cosmos DB Collection with SQL API. Suppose we have a speaker list of differents events around a whole world. The link above represent speaker list example. Example1.json For all examples we will use Data Explore in Azure Portal. Anyway, you can use any API in any supported programming language to write your queries. We are not using Modeling data techniques or witting same data in different ways to avoid cross partition queries. The main objective of the post is how to use built-in functions and operators in CosmosDB SQL API. I'd like to list all events from Hassan Arteaga Rodríguez Query SELECT * FROM c where c.speaker = "Hassan Arteaga Rodriguez" Results I need to list all events from MEXICO. Not all fields Query SELECT c.speaker, c.event_name,c.init_date,c.end_date FROM c where c.event_country = "MEXICO" Results

Some architectures with Cosmos DB and Azure Functions

Fig 1. Access Control Fig 2. Documents storage with reference to blob. Fig 3. Automatic storage maintenance of Documents with expiration. Fig 4. Reports.

Hassan Arteaga Rodríguez Blog

Search This Blog