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 | Label | Required | Options | Description | ||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Table | Table Name (Case-Sensitive) | YES | ||||||||
| Query | SQL Query | YES | Query for Cosmos DB [API reference] | |||||||
| Database | Database Name (keep blank to use default) Case-Sensitive | NO | Leave blank to use default DB set on connection screen | |||||||
| AllowScan | Allow Query Scan | NO |
|
|||||||
| AllowCrossPartition | Allow Cross Partition Query | NO |
|
|||||||
| PartitionKeyRangeId | Cross Partition Key Range Id | NO | ||||||||
| MaxItemCount | Max Rows Per Page (Adjust for Speed) | NO | An integer indicating the maximum number of items to be returned per page. |
Output Columns
| Label | Data Type (SSIS) | Data Type (SQL) | Length | Raw | Description | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Input Columns
| Label | Data Type (SSIS) | Data Type (SQL) | Length | Raw | 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 component to read data or in API Destination component to read/write data:
Query documents using Cosmos DB SQL query language using API Source
Cosmos DB
Query documents using Cosmos DB SQL query language
Query documents using Cosmos DB SQL query language using API Destination
Cosmos DB
Query documents using Cosmos DB SQL query language
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];