appRules
  • Welcome to appRules®
  • PLATFORM
    • appRules® Concepts & Features
    • Platform Overview
    • Platform Features
      • Sourced Values
        • Overview
        • Sourced Values List
      • Actions
        • Overview
        • Action Categories
          • Actions-DataSource
            • Accounting
            • AIMachineLearning
            • Analytics
            • ApplicationPlatforms
            • BigData
            • CalendarsAndContactMgrs
            • CloudStorage
            • Collaboration
            • CRM
            • CustomerService
            • Databases
            • DataWarehouse
            • ECommercePlatforms
            • EmailSystems
            • ERP
            • ExpenseManagement
            • Files
            • Financial
            • Marketing
            • Miscelleanous
            • NoSQL
            • PaymentProcessing
            • PLM
            • ProjectManagement
            • Search
            • Services
            • ShippingAndMailing
            • Social
            • Spreadsheets
          • Actions-Execution
          • Actions-Fields-Field
          • Actions-Fields-Library-Financial
          • Actions-Fields-Library-Math
          • Actions-Utility-Automation
          • Actions-Utility-FileSystem
      • Conditions
        • Overview
        • Condition Categories
          • Conditions-Cache
          • Conditions-Custom
          • Conditions-Fields
          • Conditions-FileSystem
          • Conditions-Records
          • Conditions-Results
          • Conditions-Workflow
      • Workflow Activities
        • Control Flow Activities
        • Base Activities
        • Dialog Activities
        • Base Data Activities
      • System Settings
        • Runtime Settings
        • Users
        • Data Sources
          • Overview
          • Configuring Connection Settings
          • Generating Metadata
          • Connection Settings
            • Accounting
              • Exact Online
              • Raisers Edge NXT
              • Xero
              • Quickbooks
              • Quickbooks Online
              • Quickbooks POS
              • Freshbooks
              • Sage Business Cloud Accounting
              • Sage Intacct
              • Sage50 UK
              • Reckon
            • Application Platforms
              • Alfresco Platform
              • Kintone Platform
              • Quickbase Platform
              • Veeva Vault
            • AI & Machine Learning
              • DataRobot
              • Salesforce Einstein
              • Splunk
            • Analytics
              • Google Analytics
              • Adobe Analytics
                • Adobe Custom oAuth App
              • Azure Analysis Services
              • SAS
              • Youtube Analytics
            • Big Data
              • Apache HBase
              • Apache Hive
              • Apache HDFS
              • Apache Spark
              • Greenplum
              • HPCC LexisNexis
            • Calendar & Contact Managers
              • Google Calendar
              • Google Contacts
            • Cloud Storage
              • Azure Table Storage
              • Amazon S3
              • IBM Cloud Object Storage
              • Wasabi
            • Collaboration
              • Airtable
              • Asana
              • Box
              • Dropbox
              • Exchange
              • Google Drive
              • Microsoft Teams
              • Office365
              • Microsoft OneDrive
              • Microsoft OneNote
              • Salesforce Chatter
              • SharePoint
              • Slack
              • Smartsheet
            • CRM
              • Act! CRM
              • Bullhorn CRM
              • Microsoft Dynamics 365 Sales
              • Microsoft Dynamics CRM
              • Highrise
              • Oracle Sales Cloud
              • Salesforce
              • SAP Hybris C4C
              • Sugar CRM
              • Suite CRM
              • Veeva CRM
              • Zoho CRM
            • Customer Service & Support
              • ServiceNow
              • Zendesk
              • Jira
            • Data Warehouse
              • Azure Data Lake Storage Gen1
              • Azure Data Lake Storage Gen2
              • Google BigQuery
              • Redshift
              • Databricks
              • Snowflake
              • Azure Synapse
            • E-Commerce Platforms
              • Amazon MarketPlace
              • Magento
              • Shopify
              • Woocommerce
            • Electronic Signature
              • Docusign
            • Email
              • Gmail
              • Email
            • ERP
              • NetSuite
              • Odoo
              • Acumatica
              • Dynamics NAV
              • Dynamics GP
              • Microsoft Dynamics 365 FinOp
              • Microsoft Dynamics365 Business Central
                • Business Central Endpoints
              • SAP Business One
              • SAP ERP
            • Expense Management
              • SAP Concur
            • Files
              • CSV Database
              • Fixed Length File
              • Delimited File
            • Marketing
              • Bing Ads
              • Marketo
              • MailChimp
              • HubSpot
              • Eloqua
              • Salesforce Marketing Cloud
            • NoSQL
              • Cassandra
                • Cassandra Advanced Settings
              • Azure CosmosDB
              • DynamoDB
              • Couchbase
              • MongoDB
              • IBM Cloudant
              • Redis
            • Payment Processing
              • Authorize.Net
              • Square
              • Paypal
              • Stripe
            • Product Lifecycle Management (PLM)
              • Propel
            • Relational Databases
              • appRules Embedded Database
              • Firebird
              • IBM DB2
              • IBM Informix
              • Microsoft Access
              • MySQL
              • MariaDB
              • Oracle
              • PostgreSQL
              • Microsoft SQL Server
              • Microsoft SQL Server CE
              • SQL Azure
              • SQLlite
              • Vertica
            • Search
              • ElasticSearch
              • Bing
              • Google Search
            • Shipping and Mailing
              • Fedex
              • UPS
              • USPS
            • Social Networks
              • Linkedin
              • Facebook
              • Instagram
              • Twitter
            • Services
              • REST
              • RSS
              • XML
                • Connecting to XML Datasources
                • Parsing Hierarchical Data
                • Raw Data
              • Active Directory
              • Amazon Athena
              • Microsoft Dataverse
              • JSON
              • LDAP
              • GraphQL
            • Spreadsheets
              • Excel
              • Excel Services
              • Excel Online
              • GoogleSheets
          • SQL Compliance
            • Accounting
              • Exact Online
              • Raisers Edge NXT
              • Xero
              • Quickbooks
              • Quickbooks Online
              • QuickBooks POS
              • Freshbooks
              • Reckon
              • Sage Intacct
              • Sage Business Cloud Accounting
              • Sage50 UK
            • AI & Machine Learning
              • Salesforce Einstein
              • DataRobot
              • Splunk
            • Analytics
              • Google Analytics
              • Youtube Analytics
              • Azure Analysis Services
              • Adobe Analytics
              • SAS
            • Application Platforms
              • Alfresco Platform
              • Kintone Platform
              • Quick Base Platform
              • Veeva Vault
            • Big Data
              • Apache HBase
              • Apache HDFS
              • Apache Hive
              • Apache Spark
              • Greenplum
              • HPCC Systems
            • Calendar & Contact Managers
              • Google Calendar
              • Google Contacts
            • Cloud Storage
              • Azure Table Storage
              • Amazon S3
              • Wasabi
              • IBM Cloud Object Storage
            • Collaboration
              • Airtable
              • Asana
              • Box
              • Dropbox
              • Google Drive
              • Microsoft Teams
              • Microsoft OneDrive
              • Microsoft OneNote
              • Microsoft Exchange
              • Office365
              • Salesforce Chatter
              • SharePoint
              • Slack
              • Smartsheet
            • CRM
              • Act! CRM
              • Bullhorn CRM
              • Highrise
              • Microsoft Dynamics CRM
              • Microsoft Dynamics 365 Sales
              • Oracle Sales Cloud
              • Salesforce
              • SAP Hybris C4C
              • Sugar CRM
              • Veeva CRM
              • Zoho CRM
            • Customer Service & Support
              • ServiceNow
              • Zendesk
              • Jira
            • E-commerce Platforms
              • Woocommerce
              • Magento
              • Shopify
              • Amazon Marketplace
            • DataWarehouse
              • Azure Data Lake Storage Gen1
              • Azure Data Lake Storage Gen2
              • Azure Synapse
              • Databricks
              • Google BigQuery
              • Redshift
              • Snowflake
            • Email
              • Gmail
            • ERP
              • Acumatica
              • NetSuiteSQL
              • NetSuiteTalk
              • Odoo
              • Dynamics NAV
              • Dynamics GP
              • Microsoft Dynamics 365 FinOp
              • Microsoft Dynamics365 Business Central
              • SAP Business One
              • SAP NetWeaver
              • Workday
            • Expense Management
              • SAP Concur
            • Files
              • CSV Database
            • Marketing
              • Bing Ads
              • Google Ads
              • HubSpot
              • MailChimp
              • Marketo
              • Oracle Eloqua
              • Salesforce Marketing Cloud
              • Survey Monkey
              • Youtube 2020
            • NoSQL
              • Cassandra
              • Azure CosmosDB
              • Amazon SimpleDB
              • Amazon DynamoDB
              • Couchbase
              • MongoDB
              • IBM Cloudant
              • Redis
            • Payment Processing
              • Authorize.Net
              • Paypal
              • Stripe
              • Square
            • Product Lifecycle Management (PLM)
              • Propel
            • Project Management
              • Basecamp
              • Microsoft Project
            • Search
              • ElasticSearch
              • Bing
              • Google Search
            • Shipping and Mailing
              • FedEx
              • UPS
              • USPS
            • Spreadsheets
              • Excel Online
              • Excel
              • Excel Services
              • GoogleSheets
            • Social Networks
              • Facebook
              • Instagram
              • Linkedin
              • Twitter
            • Services
              • Active Directory
              • Amazon Athena
              • JSON
              • LDAP
              • Microsoft Dataverse
              • OData
              • REST
              • RSS
              • XML
        • Projects
          • Overview
          • Project Settings
      • Utilities
        • DataSource Browser
        • Job Monitor
        • Logs/Statistics
        • CheckIn/CheckOut
        • Master Admin Options
      • Creating & Running Projects
        • Creating a New Project
        • Orchestrating Workflows with Designer
          • Designer Home Page Overview
          • Using the appRules Designer
        • Reading Data
        • Mapping Data
        • Writing Data
        • Project Validation
        • Running Projects
          • Running from the Home Page
          • Using the Web API
          • Using the Scheduler
        • Monitoring Jobs
        • Logging
      • Flow Data Migration
        • Overview
        • Basic Flow Data Migrations
        • Bulk Loader Flow Data Migrations
    • Self-Hosting
      • Software Installation
        • Installation Prerequisites
        • Running the Installer
        • HostService Settings
        • IIS Settings
        • Web API Settings
        • Scheduler
        • Creating a New Project Database
      • Application Configuration
      • appRules Embedded Database DBA Tool
  • PLATFORM PRODUCTS
    • appRules Data Migration Engine
      • Overview
      • Flow Data Migration
      • The Migrate Data Activity
        • Overview
        • Initializing the Migrate Data Activity
        • Target Entities Collection
          • Overview
          • Toolbar
          • Target Entity Properties
        • Basic Data Migration
        • Matching Schema Migration
        • One-To-Many Migration
        • Entities to Csv Migration
        • Data Replication
        • Data Synchronization
        • Bidirectional Data Synchronization
      • Field Mapping & Transformation
      • Reviewing the Project Settings
      • Validations, Error Handling & Notifications
      • Job Execution & Monitoring
      • Evaluating Data Migration Job Results
    • appRules Integration & Automation Engine
      • Overview
      • Flow Data Migration
      • Process Automation, Business Rules & Decision Support
        • Overview
        • Control Flow Activities
          • If
          • While
        • Conditions & Actions
          • Overview
          • Conditions
          • Actions
        • Evaluating Conditions
        • Performing Actions
        • Decision Tables
        • Executing Rule Sets
    • appRules Enterprise 360
      • Overview
      • Data Migration
      • Integration & Automation
  • Samples
    • Samples Overview
    • Sample Projects
      • Data Migration, Replication & Synchronization
        • Replicate Dynamics365 In Snowflake
        • Migrate Parent/Child Records Using Target / Bulk Loader
        • Export SQL Table to CSV Using Flow
        • One-to-Many Data Migration
        • Batch Data Source Entities to Files
      • Integration & Automation
        • Decision Table - Customer Discount
        • Iterate Deleted Salesforce Records
        • Use CSV Folder As SQL Database
        • Conditional Actions Execution
        • Round Robin Lead Assignment
      • Snippets
        • Snippet - Arguments, Variables, Dynamic Values & Sourced Values
        • Snippet - Counters & Running Totals
        • Snippet - Executing Financial Functions
        • Snippet - Infinite Loop
        • Using Composite Keys
      • Miscellaneous
        • Custom Queries
        • Parent-Child: Child Project
        • Parent-Child: Parent Project
        • Scheduled System Maintenance
        • Target Activity Errors & Logs
        • Task Parallelism
  • APPENDIX
    • Miscellaneous
      • Common Connection Properties
      • Troubleshooting a Connection
      • Dynamics365 - Connection Settings
      • Dynamics365 App Registration
      • Getting the oAuth Access and Refresh Tokens
      • Editing Windows Environment Variables
      • PickLists
      • Creating a Custom Azure oAuth App
      • Using Kerberos
      • Stored Procedures
    • FAQs
Powered by GitBook
On this page
  • SELECT Statements
  • SELECT Syntax
  • Aggregate Functions
  • JOIN Queries
  • Date Literal Functions
  • Projection Functions
  • Predicate Functions
Export as PDF
  1. PLATFORM
  2. Platform Features
  3. System Settings
  4. Data Sources
  5. SQL Compliance
  6. NoSQL

Azure CosmosDB

SELECT Statements

A SELECT statement can consist of the following basic clauses.

  • SELECT

  • INTO

  • FROM

  • JOIN

  • WHERE

  • GROUP BY

  • HAVING

  • UNION

  • ORDER BY

  • LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the provider:

SELECT {

[ TOP <numeric_literal> | DISTINCT ]

{

*

| {

<expression> [ [ AS ] <column_reference> ]

| { <table_name> | <correlation_name> } .*

} [ , ... ]

}

[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]

{

FROM <table_reference> [ [ AS ] <identifier> ]

} [ , ... ]

[ [

INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }

] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]

] [ ... ]

[ WHERE <search_condition> ]

[ GROUP BY <column_reference> [ , ... ]

[ HAVING <search_condition> ]

[ UNION [ ALL ] <select_statement> ]

[

ORDER BY

<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

]

[

LIMIT <expression>

[

{ OFFSET | , }

<expression>

]

]

} | SCOPE_IDENTITY()

<expression> ::=

| <column_reference>

| @ <parameter>

| ?

| COUNT( * | { [ DISTINCT ] <expression> } )

| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )

| NULLIF ( <expression> , <expression> )

| COALESCE ( <expression> , ... )

| CASE <expression>

WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]

[ ELSE { <expression> | NULL } ]

END

| <literal>

| <sql_function>

<search_condition> ::=

{

<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]

} [ { AND | OR } ... ]

Examples

  1. Return all columns:

    SELECT * FROM [CData].[Entities].Customers

  2. Rename a column:

    SELECT [CompanyName] AS MY_CompanyName FROM [CData].[Entities].Customers

  3. Cast a column's data as a different data type:

    SELECT CAST(Balance AS VARCHAR) AS Str_Balance FROM [CData].[Entities].Customers

  4. Search data:

    SELECT * FROM [CData].[Entities].Customers WHERE Country = 'US'

  5. Return the number of items matching the query criteria:

    SELECT COUNT(*) AS MyCount FROM [CData].[Entities].Customers

  6. Return the number of unique items matching the query criteria:

    SELECT COUNT(DISTINCT CompanyName) FROM [CData].[Entities].Customers

  7. Return the unique items matching the query criteria:

    SELECT DISTINCT CompanyName FROM [CData].[Entities].Customers

  8. Summarize data:

    SELECT CompanyName, MAX(Balance) FROM [CData].[Entities].Customers GROUP BY CompanyName

    See Aggregate Functions below for details.

  9. Retrieve data from multiple tables.

    SELECT restaurants.name, zips.city FROM restaurants INNER JOIN zips ON restaurants.address_zipcode = zips.C_id

    See JOIN Queries below for details.

  10. Sort a result set in ascending order:

    SELECT City, CompanyName FROM [CData].[Entities].Customers ORDER BY CompanyName ASC

  11. Restrict a result set to the specified number of rows:

    SELECT City, CompanyName FROM [CData].[Entities].Customers LIMIT 10

  12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.

    SELECT * FROM [CData].[Entities].Customers WHERE Country = @param

Aggregate Functions

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM [CData].[Entities].Customers WHERE Country = 'US'

COUNT(DISTINCT)

Returns the number of distinct, non-null field values matching the query criteria.

SELECT COUNT(DISTINCT City) AS DistinctValues FROM [CData].[Entities].Customers WHERE Country = 'US'

AVG

Returns the average of the column values.

SELECT CompanyName, AVG(Balance) FROM [CData].[Entities].Customers WHERE Country = 'US' GROUP BY CompanyName

MIN

Returns the minimum column value.

SELECT MIN(Balance), CompanyName FROM [CData].[Entities].Customers WHERE Country = 'US' GROUP BY CompanyName

MAX

Returns the maximum column value.

SELECT CompanyName, MAX(Balance) FROM [CData].[Entities].Customers WHERE Country = 'US' GROUP BY CompanyName

SUM

Returns the total sum of the column values.

SELECT SUM(Balance) FROM [CData].[Entities].Customers WHERE Country = 'US'

JOIN Queries

The Provider for Azure Cosmos DB supports joins of a nested array with its parent document and joins of multiple collections.

Joining Nested Structures

The provider expects the left part of the join is the array document you want to flatten vertically. Disable SupportEnhancedSQL to join nested Azure Cosmos DB documents. This type of query is supported through the Azure Cosmos DB API.

For example, consider the following query from Azure Cosmos DB's restaurants collection:

SELECT [restaurants].[restaurant_id], [restaurants].name, [restaurants.grades].*FROM [restaurants.grades]JOIN [restaurants]WHERE [restaurants].name = 'Morris Park Bake Shop'

Joining Multiple Collections

You can join multiple collections just like you would join tables in a relational database. Set SupportEnhancedSQL to True to execute these types of joins. The following examples use the restaurants and zips collections available in the Azure Cosmos DB documentation.

The query below returns the restaurant records that exist, if any, for each ZIP code:

SELECT z.city, r.name, r.borough, r.cuisine, r.[address.zipcode]FROM zips zLEFT JOIN restaurants rON r.[address.zipcode] = z._id

The query below returns records from both tables that match the join condition:

SELECT z.city, r.name, r.borough, r.cuisine, r.[address.zipcode]FROM restaurants rINNER JOIN zips zON r.[address.zipcode] = z._id

Date Literal Functions

The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.

L_TODAY()

The current day.

SELECT * FROM MyTable WHERE MyDateField = L_TODAY()

L_YESTERDAY()

The previous day.

SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()

L_TOMORROW()

The following day.

SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()

L_LAST_WEEK()

Every day in the preceding week.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()

L_THIS_WEEK()

Every day in the current week.

SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()

L_NEXT_WEEK()

Every day in the following week.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()

Also available:

  • L_LAST/L_THIS/L_NEXT MONTH

  • L_LAST/L_THIS/L_NEXT QUARTER

  • L_LAST/L_THIS/L_NEXT YEAR

L_LAST_N_DAYS(n)

The previous n days, excluding the current day.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)

L_NEXT_N_DAYS(n)

The following n days, including the current day.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)

Also available:

  • L_LAST/L_NEXT_90_DAYS

L_LAST_N_WEEKS(n)

Every day in every week, starting n weeks before current week, and ending in the previous week.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)

L_NEXT_N_WEEKS(n)

Every day in every week, starting the following week, and ending n weeks in the future.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)

Also available:

  • L_LAST/L_NEXT_N_MONTHS(n)

  • L_LAST/L_NEXT_N_QUARTERS(n)

  • L_LAST/L_NEXT_N_YEARS(n)

Projection Functions

ABS(numeric_expr)

Returns the absolute (positive) value of the specified numeric expression.

  • numeric_expr: A numeric expression.

ACOS(numeric_expr)

Returns the angle, in radians, whose cosine is the specified numeric expression; also called arccosine.

  • numeric_expr: A numeric expression.

ASIN(numeric_expr)

Returns the angle, in radians, whose sine is the specified numeric expression. This is also called arcsine.

  • numeric_expr: A numeric expression.

ATAN(numeric_expr)

Returns the angle, in radians, whose tangent is the specified numeric expression. This is also called arctangent.

  • numeric_expr: A numeric expression.

CEILING(numeric_expr)

Returns the smallest integer value greater than, or equal to, the specified numeric expression.

  • numeric_expr: A numeric expression.

COS(numeric_expr)

Returns the trigonometric cosine of the specified angle, in radians, in the specified expression.

  • numeric_expr: A numeric expression.

COT(numeric_expr)

Returns the trigonometric cotangent of the specified angle, in radians, in the specified numeric expression.

  • numeric_expr: A numeric expression.

DEGREES(numeric_expr)

Returns the corresponding angle in degrees for an angle specified in radians.

  • numeric_expr: A numeric expression.

FLOOR(numeric_expr)

Returns the largest integer less than or equal to the specified numeric expression.

  • numeric_expr: A numeric expression.

EXP(numeric_expr)

Returns the exponential value of the specified numeric expression.

  • numeric_expr: A numeric expression.

LOG10(numeric_expr)

Returns the base-10 logarithm of the specified numeric expression.

  • numeric_expr: A numeric expression.

RADIANS(numeric_expr)

Returns radians when a numeric expression, in degrees, is entered.

  • numeric_expr: A numeric expression.

RAND()

Returns a randomly generated numeric value from [0,1).

ROUND(numeric_expr)

Returns a numeric value, rounded to the closest integer value.

  • numeric_expr: A numeric expression.

SIGN(numeric_expr)

Returns the positive (+1), zero (0), or negative (-1) sign of the specified numeric expression.

  • numeric_expr: A numeric expression.

SIN(numeric_expr)

Returns the trigonometric sine of the specified angle, in radians, in the specified expression.

  • numeric_expr: A numeric expression.

SQRT(numeric_expr)

Returns the square root of the specified numeric value.

  • numeric_expr: A numeric expression.

SQUARE(numeric_expr)

Returns the square of the specified numeric value.

  • numeric_expr: A numeric expression.

TAN(numeric_expr)

Returns the tangent of the specified angle, in radians, in the specified expression.

  • numeric_expr: A numeric expression.

TRUNC(numeric_expr)

Returns a numeric value, truncated to the closest integer value.

  • numeric_expr: A numeric expression.

ATN2(y_expr, x_expr)

Returns the principal value of the arc tangent of y/x, expressed in radians.

  • y_expr: The y numeric expression.

  • x_expr: The x numeric expression.

LOG(numeric_expr [, base])

Returns the natural logarithm of the specified numeric expression.

  • numeric_expr: A numeric expression.

  • base: Optional numeric argument that sets the base for the logarithm.

PI()

Returns the constant value of PI.

POWER(numeric_expr, power_expr)

Returns the value of the specified expression to the specified power.

  • numeric_expr: A numeric expression.

  • power_expr: Is the power to which to raise numeric_expr.

IS_ARRAY(expr)

Returns a Boolean value indicating if the type of the specified expression is an array.

  • expr: Any valid expression.

IS_BOOL(expr)

Returns a Boolean value indicating if the type of the specified expression is a Boolean.

  • expr: Any valid expression.

IS_DEFINED(expr)

Returns a Boolean indicating if the property has been assigned a value.

  • expr: Any valid expression.

IS_NULL(expr)

Returns a Boolean value indicating if the type of the specified expression is null.

  • expr: Any valid expression.

IS_NUMBER(expr)

Returns a Boolean value indicating if the type of the specified expression is a number.

  • expr: Any valid expression.

IS_OBJECT(expr)

Returns a Boolean value indicating if the type of the specified expression is a JSON object.

  • expr: Any valid expression.

IS_PRIMITIVE(expr)

Returns a Boolean value indicating if the type of the specified expression is a primitive (string, Boolean, numeric, or null).

  • expr: Any valid expression.

IS_STRING(expr)

Returns a Boolean value indicating if the type of the specified expression is a string.

  • expr: Any valid expression.

CONCAT(str1, str2 [, str3] [, ...])

Returns a string that is the result of concatenating two or more string values.

  • str1: The first string to concatenate.

  • str2: The second string to concatenate.

  • str3: The third string to concatenate.

CONTAINS(str1, str2)

Returns a Boolean indicating whether the first string expression contains the second.

  • str1: The string to search in.

  • str2: The string to search for.

ENDSWITH(str1, str2)

Returns a Boolean indicating whether the first string expression ends with the second.

  • str1: The string to search in.

  • str2: The string to search for.

INDEX_OF(str1, str2)

Returns the starting position of the first occurrence of the second string expression within the first specified string expression, or -1 if the string is not found.

  • str1: The string to search in.

  • str2: The string to search for.

LEFT(str, num_expr)

Returns the left part of a string with the specified number of characters.

  • str: A valid string expression.

  • num_expr: The number of characters to return.

LENGTH(str)

Returns the number of characters of the specified string expression.

  • str: Any valid string expression.

LOWER(str)

Returns a string expression after converting uppercase character data to lowercase.

  • str: Any valid string expression.

LTRIM(str)

Returns a string expression after it removes leading blanks.

  • str: Any valid string expression.

REPLACE(original_value, from_value, to_value)

Replaces all occurrences of a specified string value with another string value.

  • original_value: The string to search in.

  • from_value: The string to search for.

  • to_value: The string to replace instances of from_value.

REPLICATE(str, repeat_num)

Repeats a string value a specified number of times.

  • str: The string expression to repeat.

  • repeat_num: The number of times to repeat the str expression.

REVERSE(str)

Returns the reverse order of a string value.

  • str: Any valid string expression.

RIGHT(str, num_expr)

Returns the right part of a string with the specified number of characters.

  • str: Any valid string expression.

  • num_expr: The starting index.

RTRIM(str)

Returns a string expression after it removes trailing blanks.

  • str: Any valid string expression.

STARTSWITH(str1, str2)

Returns a Boolean indicating whether the first string expression starts with the second.

  • str1: The string to search in.

  • str2: The string to search for.

SUBSTRING(str, start_index, length)

Returns part of a string expression starting at the specified character zero-based position and continues to the specified length, or to the end of the string.

  • str: Any valid string expression.

  • start_index: The starting index.

  • length: The length of the string to return.

TOSTRING(expr)

Returns a string representation of scalar expression.

  • expr: Any valid expression.

TRIM(str)

Returns a string expression after it removes leading and trailing blanks.

  • str: Any valid string expression.

UPPER(str)

Returns a string expression after converting lowercase character data to uppercase.

  • str: Any valid string expression.

ARRAY_CONCAT(array_exp1, array_exp2 [, array_exp3])

Returns an array that is the result of concatenating two or more array values.

  • array_exp1: Any valid array expression.

  • array_exp2: Any valid array expression.

  • array_exp3: Any valid array expression.

ARRAY_CONTAINS(array_exp, expr [, bool_expr])

Returns a Boolean indicating whether the array contains the specified value. You can check for a partial or full match of an object by using a boolean expression within the command.

  • array_exp1: Any array expression.

  • expr: The expression to search for.

  • bool_expr: If it's set to 'true'and if the specified search value is an object, the command checks for a partial match (the search object is a subset of one of the objects). If it's set to 'false', the command checks for a full match of all objects within the array. The default value if not specified is false.

ARRAY_LENGTH(array_exp)

Returns the number of elements of the specified array expression.

  • array_exp: Any valid array expression.

ARRAY_SLICE(array_exp, start_index, max_size)

Returns part of an array expression.

  • array_exp: Any valid array expression.

  • start_index: Zero-based numeric index at which to begin the array. Negative values may be used to specify the starting index relative to the last element of the array i.e. -1 references the last element in the array.

  • max_size: Maximum number of elements in the resulting array.

ST_DISTANCE(spatial_expr1, spatial_expr2)

Returns the distance between the two GeoJSON Point, Polygon, or LineString expressions.

  • spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.

  • spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.

ST_WITHIN(spatial_expr1, spatial_expr2)

Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument is within the GeoJSON (Point, Polygon, or LineString) in the second argument.

  • spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.

  • spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.

ST_INTERSECTS(1, 2)

Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument intersects the GeoJSON (Point, Polygon, or LineString) in the second argument.

  • spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.

  • spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.

ST_ISVALID(spatial_expr)

Returns a Boolean value indicating whether the specified GeoJSON Point, Polygon, or LineString expression is valid.

  • spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.

ST_ISVALIDDETAILED(spatial_expr)

Returns a JSON value containing a Boolean value if the specified GeoJSON Point, Polygon, or LineString expression is valid, and if invalid, additionally the reason as a string value.

  • spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.

Predicate Functions

ABS(numeric_expr)

Returns the absolute (positive) value of the specified numeric expression.

  • numeric_expr: A numeric expression.

ACOS(numeric_expr)

Returns the angle, in radians, whose cosine is the specified numeric expression; also called arccosine.

  • numeric_expr: A numeric expression.

ASIN(numeric_expr)

Returns the angle, in radians, whose sine is the specified numeric expression. This is also called arcsine.

  • numeric_expr: A numeric expression.

ATAN(numeric_expr)

Returns the angle, in radians, whose tangent is the specified numeric expression. This is also called arctangent.

  • numeric_expr: A numeric expression.

CEILING(numeric_expr)

Returns the smallest integer value greater than, or equal to, the specified numeric expression.

  • numeric_expr: A numeric expression.

COS(numeric_expr)

Returns the trigonometric cosine of the specified angle, in radians, in the specified expression.

  • numeric_expr: A numeric expression.

COT(numeric_expr)

Returns the trigonometric cotangent of the specified angle, in radians, in the specified numeric expression.

  • numeric_expr: A numeric expression.

DEGREES(numeric_expr)

Returns the corresponding angle in degrees for an angle specified in radians.

  • numeric_expr: A numeric expression.

FLOOR(numeric_expr)

Returns the largest integer less than or equal to the specified numeric expression.

  • numeric_expr: A numeric expression.

EXP(numeric_expr)

Returns the exponential value of the specified numeric expression.

  • numeric_expr: A numeric expression.

LOG10(numeric_expr)

Returns the base-10 logarithm of the specified numeric expression.

  • numeric_expr: A numeric expression.

RADIANS(numeric_expr)

Returns radians when a numeric expression, in degrees, is entered.

  • numeric_expr: A numeric expression.

RAND()

Returns a randomly generated numeric value from [0,1).

ROUND(numeric_expr)

Returns a numeric value, rounded to the closest integer value.

  • numeric_expr: A numeric expression.

SIGN(numeric_expr)

Returns the positive (+1), zero (0), or negative (-1) sign of the specified numeric expression.

  • numeric_expr: A numeric expression.

SIN(numeric_expr)

Returns the trigonometric sine of the specified angle, in radians, in the specified expression.

  • numeric_expr: A numeric expression.

SQRT(numeric_expr)

Returns the square root of the specified numeric value.

  • numeric_expr: A numeric expression.

SQUARE(numeric_expr)

Returns the square of the specified numeric value.

  • numeric_expr: A numeric expression.

TAN(numeric_expr)

Returns the tangent of the specified angle, in radians, in the specified expression.

  • numeric_expr: A numeric expression.

TRUNC(numeric_expr)

Returns a numeric value, truncated to the closest integer value.

  • numeric_expr: A numeric expression.

ATN2(y_expr, x_expr)

Returns the principal value of the arc tangent of y/x, expressed in radians.

  • y_expr: The y numeric expression.

  • x_expr: The x numeric expression.

LOG(numeric_expr [, base])

Returns the natural logarithm of the specified numeric expression.

  • numeric_expr: A numeric expression.

  • base: Optional numeric argument that sets the base for the logarithm.

PI()

Returns the constant value of PI.

POWER(numeric_expr, power_expr)

Returns the value of the specified expression to the specified power.

  • numeric_expr: A numeric expression.

  • power_expr: Is the power to which to raise numeric_expr.

IS_ARRAY(expr)

Returns a Boolean value indicating if the type of the specified expression is an array.

  • expr: Any valid expression.

IS_BOOL(expr)

Returns a Boolean value indicating if the type of the specified expression is a Boolean.

  • expr: Any valid expression.

IS_DEFINED(expr)

Returns a Boolean indicating if the property has been assigned a value.

  • expr: Any valid expression.

IS_NULL(expr)

Returns a Boolean value indicating if the type of the specified expression is null.

  • expr: Any valid expression.

IS_NUMBER(expr)

Returns a Boolean value indicating if the type of the specified expression is a number.

  • expr: Any valid expression.

IS_OBJECT(expr)

Returns a Boolean value indicating if the type of the specified expression is a JSON object.

  • expr: Any valid expression.

IS_PRIMITIVE(expr)

Returns a Boolean value indicating if the type of the specified expression is a primitive (string, Boolean, numeric, or null).

  • expr: Any valid expression.

IS_STRING(expr)

Returns a Boolean value indicating if the type of the specified expression is a string.

  • expr: Any valid expression.

CONCAT(str1, str2 [, str3] [, ...])

Returns a string that is the result of concatenating two or more string values.

  • str1: The first string to concatenate.

  • str2: The second string to concatenate.

  • str3: The third string to concatenate.

CONTAINS(str1, str2)

Returns a Boolean indicating whether the first string expression contains the second.

  • str1: The string to search in.

  • str2: The string to search for.

ENDSWITH(str1, str2)

Returns a Boolean indicating whether the first string expression ends with the second.

  • str1: The string to search in.

  • str2: The string to search for.

INDEX_OF(str1, str2)

Returns the starting position of the first occurrence of the second string expression within the first specified string expression, or -1 if the string is not found.

  • str1: The string to search in.

  • str2: The string to search for.

LEFT(str, num_expr)

Returns the left part of a string with the specified number of characters.

  • str: A valid string expression.

  • num_expr: The number of characters to return.

LENGTH(str)

Returns the number of characters of the specified string expression.

  • str: Any valid string expression.

LOWER(str)

Returns a string expression after converting uppercase character data to lowercase.

  • str: Any valid string expression.

LTRIM(str)

Returns a string expression after it removes leading blanks.

  • str: Any valid string expression.

REPLACE(original_value, from_value, to_value)

Replaces all occurrences of a specified string value with another string value.

  • original_value: The string to search in.

  • from_value: The string to search for.

  • to_value: The string to replace instances of from_value.

REPLICATE(str, repeat_num)

Repeats a string value a specified number of times.

  • str: The string expression to repeat.

  • repeat_num: The number of times to repeat the str expression.

REVERSE(str)

Returns the reverse order of a string value.

  • str: Any valid string expression.

RIGHT(str, num_expr)

Returns the right part of a string with the specified number of characters.

  • str: Any valid string expression.

  • num_expr: The starting index.

RTRIM(str)

Returns a string expression after it removes trailing blanks.

  • str: Any valid string expression.

STARTSWITH(str1, str2)

Returns a Boolean indicating whether the first string expression starts with the second.

  • str1: The string to search in.

  • str2: The string to search for.

SUBSTRING(str, start_index, length)

Returns part of a string expression starting at the specified character zero-based position and continues to the specified length, or to the end of the string.

  • str: Any valid string expression.

  • start_index: The starting index.

  • length: The length of the string to return.

TOSTRING(expr)

Returns a string representation of scalar expression.

  • expr: Any valid expression.

TRIM(str)

Returns a string expression after it removes leading and trailing blanks.

  • str: Any valid string expression.

UPPER(str)

Returns a string expression after converting lowercase character data to uppercase.

  • str: Any valid string expression.

ARRAY_CONCAT(array_exp1, array_exp2 [, array_exp3])

Returns an array that is the result of concatenating two or more array values.

  • array_exp1: Any valid array expression.

  • array_exp2: Any valid array expression.

  • array_exp3: Any valid array expression.

ARRAY_CONTAINS(array_exp, expr [, bool_expr])

Returns a Boolean indicating whether the array contains the specified value. You can check for a partial or full match of an object by using a boolean expression within the command.

  • array_exp1: Any array expression.

  • expr: The expression to search for.

  • bool_expr: If it's set to 'true'and if the specified search value is an object, the command checks for a partial match (the search object is a subset of one of the objects). If it's set to 'false', the command checks for a full match of all objects within the array. The default value if not specified is false.

ARRAY_LENGTH(array_exp)

Returns the number of elements of the specified array expression.

  • array_exp: Any valid array expression.

ARRAY_SLICE(array_exp, start_index, max_size)

Returns part of an array expression.

  • array_exp: Any valid array expression.

  • start_index: Zero-based numeric index at which to begin the array. Negative values may be used to specify the starting index relative to the last element of the array i.e. -1 references the last element in the array.

  • max_size: Maximum number of elements in the resulting array.

ST_DISTANCE(spatial_expr1, spatial_expr2)

Returns the distance between the two GeoJSON Point, Polygon, or LineString expressions.

  • spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.

  • spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.

ST_WITHIN(spatial_expr1, spatial_expr2)

Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument is within the GeoJSON (Point, Polygon, or LineString) in the second argument.

  • spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.

  • spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.

ST_INTERSECTS(1, 2)

Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument intersects the GeoJSON (Point, Polygon, or LineString) in the second argument.

  • spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.

  • spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.

ST_ISVALID(spatial_expr)

Returns a Boolean value indicating whether the specified GeoJSON Point, Polygon, or LineString expression is valid.

  • spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.

ST_ISVALIDDETAILED(spatial_expr)

Returns a JSON value containing a Boolean value if the specified GeoJSON Point, Polygon, or LineString expression is valid, and if invalid, additionally the reason as a string value.

  • spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.

PreviousCassandraNextAmazon SimpleDB

Last updated 2 years ago