CosmosSQL Extract

This extract retrieves data from Azure Cosmos database services with SQL model/API. It allows selecting columns with aliases, filtering with WHERE clauses, and sorting. 

Main Settings
Connection Connection of type CosmosDB
Collection
Available collections from Azure
Output Columns  Columns that should be returned by relational table extract
Where-Filter

Operators:

  • Equals: (=)
  • Not equals: (<>)
  • Less than: (<)
  • Less or equal: (<=)
  • Greater than: (>)
  • Greater or equal: (>=)
  • IN: the filter value is a list of values. The condition is true if the value equals any one of the specified values. Example: ‘California’, ‘New York’
  • Not IN: The filter value is a list of values. The condition is true if the value does not equal any one of the specified values.
  • Is Null
  • Is Not Null
Logical Filter Expression

A Boolean condition can be applied for if several filter conditions have been defined. Valid symbols are: [0-9] ( ) AND OR NOT

Examples:

  • (1 AND 2) OR 3: Retrieves records that match both the first two filter rows or the third.
  • 1 AND (2 OR 3): Retrieves records that match both the first filter row and one of the last two.

If no logical filter expression is defined an AND filter is applied, i.e. 1 AND 2 AND … AND n.

Sorting Ascending or descending order of the entered column.
Advanced Settings
Query Attachment

An optional part of a SQL query with additional options that are appended to the generated SQL statement.

Example: LIMIT 10 OFFSET 10

Use Caching

Memory, disk or none (default). See article Caching in Extracts.

Note: CosmosSQL does not support the following functions: LIKE, HAVING, DISTINCT, MULTIPLE ORDER-BY, GROUP BY and AGGREGATE.