Query documents with Cosmos DB SQL

Runs a Cosmos DB SQL query against a container via the query_documents endpoint. Supply the container name, the query text, and optionally Meta 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 WITH.

For query syntax see Cosmos DB SQL query reference.

Standard SQL query example

This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.

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
)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_COSMOS_DB_IN_GATEWAY], '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
)')

Using EXEC in SQL Server (handling larger SQL text)

The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements. This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).

Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_COSMOS_DB_IN_GATEWAY] syntax.

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]