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.

Settings

Connection Connection of type CosmosDB
Collection If one or more collections from Azure are available, they will be displayed in a dropdown menu.
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 Elements can be sorted in ascending or descending order, with or without case sensitivity.
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.

Updated March 19, 2024