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