Reference

Endpoint Query documents using Cosmos DB SQL query language

Name

query_documents

Description

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

Parameters

Parameter Required Options
Name: Table

Label: Table Name (Case-Sensitive)

 YES
Name: Query

Label: SQL Query

Query for Cosmos DB 		YES
Name: Database

Label: Database Name (keep blank to use default) Case-Sensitive

Leave blank to use default DB set on connection screen
Name: AllowScan

Label: Allow Query Scan
Option Value
true true
false false
Name: AllowCrossPartition

Label: Allow Cross Partition Query
Option Value
true true
false false
Name: PartitionKeyRangeId

Label: Cross Partition Key Range Id
Name: MaxItemCount

Label: Max Rows Per Page (Adjust for Speed)

An integer indicating the maximum number of items to be returned per page.

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.
If the column you are looking for is missing, consider customizing Cosmos DB Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

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
Connect to your Azure Cosmos DB databases to read, query, create, update, and delete documents and more!
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
SSIS API Source - Read from table or endpoint

API Destination

API Destination - Cosmos DB
Connect to your Azure Cosmos DB databases to read, query, create, update, and delete documents and more!
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
SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Query Documents from Cosmos DB Table (Container) using SQL

Lists all users Cosmos DB Database 

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 Qyery 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 from Cosmos DB Table (Container) using SQL

Lists all users Cosmos DB Database 

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 Qyery Language Syntax
	  --https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/select
)';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];