Endpoint Query documents using Cosmos DB SQL query language

Name

query_documents

Description

Gets data based on the specified SQL query. [API reference]

Parameters

Output Columns

Input Columns

Examples

SSIS

Use Cosmos DB Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

API Source - Cosmos DB

Read and write Azure Cosmos DB data effortlessly. Query, integrate, and manage databases, containers, documents, and users — almost no coding required.

Cosmos DB

Query documents using Cosmos DB SQL query language

Required Parameters
Table Name (Case-Sensitive)	Fill-in the parameter...
SQL Query	Fill-in the parameter...
Filter	Fill-in the parameter...
Optional Parameters
Database Name (keep blank to use default) Case-Sensitive
Allow Query Scan	true
Allow Cross Partition Query	true
Cross Partition Key Range Id	0

API Destination

API Destination - Cosmos DB

Read and write Azure Cosmos DB data effortlessly. Query, integrate, and manage databases, containers, documents, and users — almost no coding required.

Cosmos DB

Query documents using Cosmos DB SQL query language

Required Parameters
Table Name (Case-Sensitive)	Fill-in the parameter...
SQL Query	Fill-in the parameter...
Filter	Fill-in the parameter...
Optional Parameters
Database Name (keep blank to use default) Case-Sensitive
Allow Query Scan	true
Allow Cross Partition Query	true
Cross Partition Key Range Id	0

ODBC application

Use these SQL queries in your ODBC application data source:

Query documents with Cosmos DB SQL

<p>Runs a Cosmos DB SQL query against a container via the <code>query_documents</code> endpoint. Supply the container name, the query text, and optionally <code>Meta</code> to define result columns and types. Use for filtered or ordered queries, or when you need a custom SELECT. Cross-partition and scan behavior can be set in <code>WITH</code>.</p><p>For query syntax see <a href="https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/select">Cosmos DB SQL query reference</a>.</p>

SELECT *
FROM query_documents
WITH(
  --  Database='TestDB', --if you dont supply connection level Default Database name is used
	  Table='TestContainer',  
	  Query='select * from root Where root.id!=null order by root._ts desc',
	  Meta='id:string(50);name:string(50);city;age'
	  --Meta='id; name; city; age' -- no types at all. Default is string(2000)
	  --Meta='id; name:string(50); city; age: int'   --Mixed types. If type is missing default string(2000) used
	  --check below URL for more information on Query Language Syntax
	  --https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/select
)

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Query documents with Cosmos DB SQL

<p>Runs a Cosmos DB SQL query against a container via the <code>query_documents</code> endpoint. Supply the container name, the query text, and optionally <code>Meta</code> to define result columns and types. Use for filtered or ordered queries, or when you need a custom SELECT. Cross-partition and scan behavior can be set in <code>WITH</code>.</p><p>For query syntax see <a href="https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/select">Cosmos DB SQL query reference</a>.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM query_documents
WITH(
  --  Database=''TestDB'', --if you dont supply connection level Default Database name is used
	  Table=''TestContainer'',  
	  Query=''select * from root Where root.id!=null order by root._ts desc'',
	  Meta=''id:string(50);name:string(50);city;age''
	  --Meta=''id; name; city; age'' -- no types at all. Default is string(2000)
	  --Meta=''id; name:string(50); city; age: int''   --Mixed types. If type is missing default string(2000) used
	  --check below URL for more information on Query Language Syntax
	  --https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/select
)';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];