SwiftTechMinutes

Concise and Comprehensive Insights on Various Technologies.

CosmosDb

25 Awesome Advanced CosmosDb SQLs

Advanced CosmosDb Sqls

Advanced CosmosDb SQLs

Here are 25 advanced CosmosDb SQLs operations used in Azure Cosmos DB’s SQL API

Joining Documents: Performs joins between multiple collections to combine related data.

SELECT c.name, o.orderNumber
FROM customers c
JOIN orders o IN c.orders
WHERE o.totalAmount > 100

Aggregating data: Performs calculations on a set of documents, such as COUNT, SUM, AVG, MIN, and MAX.

SELECT c.category, COUNT(1) AS count
FROM products p
JOIN c IN p.categories
GROUP BY c.category

Filtering arrays: Filters documents based on specific conditions

SELECT c.name, c.orders
FROM customers c
WHERE ARRAY_LENGTH(c.orders) > 0

Sorting and paging: Used for pagination, allowing retrieval of a subset of results or skipping a certain number of documents. More

SELECT c.name, c.age
FROM customers c
ORDER BY c.age DESC
OFFSET 10 LIMIT 5

Conditional filtering: Filters documents based on specific conditions

SELECT c.name, c.orders
FROM customers c
WHERE EXISTS(SELECT VALUE o FROM o IN c.orders WHERE o.totalAmount > 100)

Nested arrays:

SELECT c.name, o.product
FROM customers c
JOIN o IN (SELECT VALUE o FROM o IN c.orders WHERE o.status = 'Shipped')

String operations: Manipulate and search strings using functions like CONTAINS, STARTSWITH, and ENDSWITH.

SELECT c.name, LENGTH(c.name) AS nameLength
FROM customers c
WHERE CONTAINS(c.name, 'John') AND STARTSWITH(c.name, 'Mr.')

Subqueries: Nest queries within queries to achieve more complex filtering or retrieve related data.

SELECT c.name, o.totalAmount
FROM customers c
JOIN (SELECT VALUE o FROM o IN c.orders WHERE o.status = 'Completed') o
WHERE o.totalAmount > 500

Using UDFs (User-Defined Functions): Click here for more.

SELECT c.name, c.age, udf.calculateDiscount(c.age, c.totalAmount) AS discount
FROM customers c
WHERE c.totalAmount > 1000

Array indexing: Perform operations on arrays, such as ARRAY_CONTAINS, ARRAY_LENGTH, and ARRAY_SLICE.

SELECT c.name, c.orders[0].orderNumber AS firstOrderNumber
FROM customers c
WHERE ARRAY_LENGTH(c.orders) > 0

Cross-document JOIN:

SELECT c.name, o.orderNumber, p.productName
FROM customers c
JOIN o IN c.orders
JOIN p IN products
WHERE p.productId = o.productId

Hierarchical queries:

SELECT c.name, ARRAY(SELECT o.orderNumber FROM o IN c.orders WHERE o.totalAmount > 100) AS highValueOrders
FROM customers c

Geospatial queries: More

SELECT c.name, c.location
FROM customers c
WHERE ST_DISTANCE(c.location, {'type': 'Point', 'coordinates': [longitude, latitude]}) < 1000

Time-based queries:

SELECT c.name, c.orderDate
FROM customers c
WHERE c.orderDate >= '2023-01-01T00:00:00Z' AND c.orderDate <= '2023-01-31T23:59:59Z'

Using the DISTINCT keyword:

SELECT DISTINCT c.category
FROM products p
JOIN c IN p.categories
WHERE p.price > 50

Conditional ordering:

SELECT c.name, c.age
FROM customers c
ORDER BY c.age DESC NULLS LAST

Self-join on nested properties:

SELECT c1.name AS customerName, c2.name AS referredBy
FROM customers c1
JOIN c2 IN customers
WHERE c1.referredBy = c2.id

Array concatenation: Perform operations on arrays, such as ARRAY_CONTAINS, ARRAY_LENGTH, and ARRAY_SLICE

SELECT c.name, ARRAY_CONCAT(c.orders, c.previousOrders) AS allOrders
FROM customers c
WHERE c.id = '123'

SELECT c.name, ARRAY_SLICE(c.orders, 5, 10) AS paginatedOrders
FROM customers c
WHERE c.customerId = '123'

SELECT c.name, c.orders
FROM customers c
WHERE ARRAY_CONTAINS(c.orders, {'orderNumber': '123', 'status': 'Shipped'}, true)

Using the IN operator with an array:

SELECT p.productName, p.price
FROM products p
WHERE p.category IN ('Electronics', 'Appliances')

Using the TOP keyword for limiting results:

SELECT TOP 10 c.name, c.age
FROM customers c
ORDER BY c.age DESC

Conditional aggregation with CASE statement:

SELECT c.category, SUM(CASE WHEN p.price > 50 THEN 1 ELSE 0 END) AS highPriceCount
FROM products p
JOIN c IN p.categories
GROUP BY c.category

Using the EXPLAIN keyword to analyze query execution:

EXPLAIN SELECT c.name, c.age
FROM customers c
WHERE c.age > 30

Conditional updates with the UPDATE statement:

UPDATE c
SET c.status = 'Paid'
FROM customers c
WHERE c.totalAmount > 1000

Updating nested properties with ARRAY constructs:

UPDATE c
SET c.orders = ARRAY(SELECT o FROM o IN c.orders WHERE o.status = 'Shipped')
FROM customers c
WHERE c.customerId = '123'

Using the MERGE statement for UPSERT operations:

MERGE INTO customers c
USING [{"customerId": "123", "name": "John Doe"}] AS updated
ON (c.customerId = updated.customerId)
WHEN MATCHED THEN UPDATE SET c.name = updated.name
WHEN NOT MATCHED THEN INSERT (customerId, name) VALUES (updated.customerId, updated.name)

System functions: Check the type and existence of properties in documents using functions like IS_DEFINED, IS_ARRAY, IS_STRING, and IS_OBJECT.

SELECT c.name, c.age
FROM customers c
WHERE IS_DEFINED(c.address)

SELECT c.name, c.orders
FROM customers c
WHERE IS_ARRAY(c.orders) = true

SELECT c.name, c.description
FROM products c
WHERE IS_STRING(c.description) = true

STRING_AGG function for string concatenation:

SELECT c.name, STRING_AGG(o.orderNumber, ', ') AS orderList
FROM customers c
JOIN o IN c.orders
GROUP BY c.name

 


More Reference Click Here.

We value your feedback and are always ready to assist you. Please feel free to Contact Us.


What is Azure CosmosDB?

Azure Cosmos DB is a globally distributed, multi-model database service provided by Microsoft Azure. It is designed to handle massive scale, high availability, and low-latency requirements of modern applications. Cosmos DB offers a schema-agnostic data model, allowing you to store and query various data types such as documents, key-value, graph, and columnar data. It provides rich APIs and SDKs for accessing data, including SQL-based queries, MongoDB API, Cassandra API, Gremlin API, and Azure Table Storage API.
Key features of Azure Cosmos DB include:
*  Global Distribution
*  Scalability
*  Consistency Models
*  Multi-Model Support
*  Security and Compliance
*  Integrated Analytics
Serverless Computing

What is CosmosDb SQL?

Cosmos DB SQL refers to the SQL-like query language used in Azure Cosmos DB, a globally distributed, multi-model database service provided by Microsoft Azure. Cosmos DB SQL is designed to query and manipulate data stored in Azure Cosmos DB containers.
Cosmos DB SQL is a fully managed query language that allows you to retrieve, filter, aggregate, and modify data in Azure Cosmos DB. It supports querying across various data models, including documents, key-value, graph, and columnar data.
Key features of Cosmos DB SQL include:
SQL-like Syntax: Cosmos DB SQL uses a syntax similar to SQL, making it familiar to developers experienced with traditional SQL databases. However, there are some differences and additional features specific to Cosmos DB.
Schema-Agnostic: Cosmos DB SQL is schema-agnostic, meaning it allows you to query data without requiring a predefined schema. You can query documents with different structures and retrieve only the properties you need.
Rich Query Capabilities: Cosmos DB SQL supports a wide range of query operations, including filtering, sorting, grouping, joining, aggregating, paging, and more. It also provides functions and operators for string manipulation, mathematical calculations, date-time operations, and geospatial queries.
Multi-Model Querying: With Cosmos DB SQL, you can write queries that span across different data models within a single container. For example, you can perform queries that involve documents, key-value pairs, or graph relationships.
User-Defined Functions (UDFs): Cosmos DB SQL allows you to define and use user-defined functions to encapsulate custom logic within your queries. UDFs can be written in JavaScript and used to perform complex transformations or calculations.
Query Performance and Optimization: Cosmos DB SQL offers features such as indexing, partitioning, and query metrics to optimize query performance. You can create and manage indexes to speed up query execution and monitor query performance using metrics provided by Cosmos DB.
Integration with SDKs and APIs: Cosmos DB SQL can be used through SDKs and APIs provided by Azure Cosmos DB, making it easy to integrate query capabilities into your applications written in various programming languages.
Cosmos DB SQL provides a powerful and flexible querying mechanism for developers to retrieve and manipulate data stored in Azure Cosmos DB, enabling efficient and scalable data access in globally distributed applications.

Leave a Reply

Your email address will not be published. Required fields are marked *

Databricks Lakehouse features