Couchbase

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

Return all columns:
SELECT * FROM Customer
Rename a column:
SELECT [TotalDue] AS MY_TotalDue FROM Customer
Cast a column's data as a different data type:
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Customer
Search data:
SELECT * FROM Customer WHERE CustomerId = '12345'
The Couchbase APIs support the following operators in the WHERE clause: =, !=, <, <=, >, >=, IS NULL, LIKE, AND, OR, NOT, IN.
SELECT * FROM Customer WHERE CustomerId = '12345';
Return the number of items matching the query criteria:
SELECT COUNT(*) AS MyCount FROM Customer
Return the number of unique items matching the query criteria:
SELECT COUNT(DISTINCT TotalDue) FROM Customer
Return the unique items matching the query criteria:
SELECT DISTINCT TotalDue FROM Customer
Summarize data:
SELECT TotalDue, MAX(AnnualRevenue) FROM Customer GROUP BY TotalDue
See Aggregate Functions below for details.
Retrieve data from multiple tables.
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
See JOIN Queries below for details.
Sort a result set in ascending order:
SELECT Name, TotalDue FROM Customer ORDER BY TotalDue ASC
Restrict a result set to the specified number of rows:
SELECT Name, TotalDue FROM Customer LIMIT 10
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
SELECT * FROM Customer WHERE CustomerId = @param

Aggregate Functions

Examples of Aggregate Functions

Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM Customer WHERE CustomerId = '12345'

COUNT(DISTINCT)

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

SELECT COUNT(DISTINCT Name) AS DistinctValues FROM Customer WHERE CustomerId = '12345'

AVG

Returns the average of the column values.

SELECT TotalDue, AVG(AnnualRevenue) FROM Customer WHERE CustomerId = '12345' GROUP BY TotalDue

MIN

Returns the minimum column value.

SELECT MIN(AnnualRevenue), TotalDue FROM Customer WHERE CustomerId = '12345' GROUP BY TotalDue

MAX

Returns the maximum column value.

SELECT TotalDue, MAX(AnnualRevenue) FROM Customer WHERE CustomerId = '12345' GROUP BY TotalDue

SUM

Returns the total sum of the column values.

SELECT SUM(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM Customer WHERE CustomerId = '12345'

COUNT(DISTINCT)

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

SELECT COUNT(DISTINCT Name) AS DistinctValues FROM Customer WHERE CustomerId = '12345'

AVG

Returns the average of the column values.

SELECT TotalDue, AVG(AnnualRevenue) FROM Customer WHERE CustomerId = '12345' GROUP BY TotalDue

MIN

Returns the minimum column value.

SELECT MIN(AnnualRevenue), TotalDue FROM Customer WHERE CustomerId = '12345' GROUP BY TotalDue

MAX

Returns the maximum column value.

SELECT TotalDue, MAX(AnnualRevenue) FROM Customer WHERE CustomerId = '12345' GROUP BY TotalDue

SUM

Returns the total sum of the column values.

SELECT SUM(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'

JOIN Queries

The Provider for Couchbase supports standard SQL joins like the following examples.

Inner Join

An inner join selects only rows from both tables that match the join condition:

SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId

Left Join

A left join selects all rows in the FROM table and only matching rows in the JOIN table:

SELECT Customers.ContactName, Orders.OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerId=Orders.CustomerId

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

ARRAY_AGG(column)

Returns array of the non-MISSING values in the group, including NULL values.

column: Any column expression.

ARRAY_APPEND(column, value)

Returns new array with value appended.

column: Any column expression.
value: The value to be appended to the array.

ARRAY_CONCAT(column1, column2)

Returns new array with the concatenation of the input arrays.

column1: Any column expression.
column2: Any column expression.

ARRAY_DISTINCT(column)

Returns new array with distinct elements of input array.

column: Any column expression.

ARRAY_IFNULL(column)

Returns the first non-NULL value in the array, or NULL.

column: Any column expression.

ARRAY_PREPEND(column, value)

Returns new array with value pre-pended.

column: Any column expression.
value: The value to be pre-pended to the array.

ARRAY_PUT(column, value)

Returns new array with value appended, if value is not already present, otherwise returns the unmodified input array.

column: Any column expression.
value: The value to append to the array.

ARRAY_REMOVE(column, value)

Returns new array with all occurrences of value removed.

column: Any column expression.
value: The value to be removed from the array.

ARRAY_REPLACE(column, value1, value2 [, integer_n])

Returns new array with all occurrences of value removed.

column: Any column expression.
value1: The value to be replaced by value2.
value2: The value to replace value1.
integer_n: The maximum number of replacements to be performed.

ARRAY_REVERSE(column)

Returns new array with all elements in reverse order.

column: Any column expression.

ARRAY_SORT(column)

Returns new array with elements sorted in N1QL collation order.

column: Any column expression.

DECODE_JSON(column)

Unmarshals the JSON-encoded string into a N1QL value. The empty string is MISSING.

column: Any column expression.

ENCODE_JSON(column)

Marshals the N1QL value into a JSON-encoded string. MISSING becomes the empty string.

column: Any column expression.

ENCODED_SIZE(column)

Number of bytes in an uncompressed JSON encoding of the value. The exact size is implementation-dependent. Always returns an integer, and never MISSING or NULL. Returns 0 for MISSING.

column: Any column expression.

POLY_LENGTH(column)

Returns length of the value after evaluating the expression. The exact meaning of length depends on the type of the value: MISSING: MISSING; NULL: NULL; String: The length of the string.; Array: The number of elements in the array.; Object: The number of name/value pairs in the object; Any other value: NULL.

column: Any column expression.

OBJECT_LENGTH(column)

Returns number of name-value pairs in the object.

column: Any column expression.

OBJECT_NAMES(column)

Returns array containing the attribute names of the object, in N1QL collation order.

column: Any column expression.

OBJECT_PAIRS(column)

Returns array containing the attribute name and value pairs of the object, in N1QL collation order of the names.

column: Any column expression.

OBJECT_VALUES(column)

Returns array containing the attribute values of the object, in N1QL collation order of the corresponding names.

column: Any column expression.

ARRAY_AVG(column)

Returns arithmetic mean (average) of all the non-NULL number values in the array, or NULL if there are no such values.

column: Any column expression.

ARRAY_CONTAINS(column, value)

Returns true if the array contains value.

column: Any column expression.
value: The value contained within the array.

ARRAY_COUNT(column)

Returns count of all the non-NULL values in the array, or zero if there are no such values.

column: Any column expression.

ARRAY_LENGTH(column)

Returns the number of elements in the array.

column: Any column expression.

ARRAY_MAX(column)

Returns the largest non-NULL, non-MISSING array element, in N1QL collation order.

column: Any column expression.

ARRAY_MIN(column)

Returns smallest non-NULL, non-MISSING array element, in N1QL collation order.

column: Any column expression.

ARRAY_POSITION(column, value)

Returns the first position of value within the array, or -1. Array position is zero-based, i.e. the first position is 0.

column: Any column expression.
value: The value contained within the array.

ARRAY_SUM(column)

Sum of all the non-NULL number values in the array, or zero if there are no such values.

column: Any column expression.

GREATEST(column1, column2 [,column3 [,column4]])

Largest non-NULL, non-MISSING value if the values are of the same type; otherwise NULL.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

LEAST(column1, column2 [,column3 [,column4]])

Returns smallest non-NULL, non-MISSING value if the values are of the same type, otherwise returns NULL.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

IFMISSING(column1, column2 [,column3 [,column4]])

Returns the first non-MISSING value.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

IFMISSINGORNULL(column1, column2 [,column3 [,column4]])

Returns first non-NULL, non-MISSING value.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

IFNULL(column1, column2 [,column3 [,column4]])

Returns first non-NULL value. Note that this function might return MISSING if there is no non-NULL value.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

MISSINGIF(column1, column2)

Returns MISSING if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

column1: Any column expression.
column2: Any column expression.

NULLIF(column1, column2)

Returns NULL if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

column1: Any column expression.
column2: Any column expression.

IFINF(column1, column2 [,column3 [,column4]])

Returns first non-MISSING, non-Inf number. Returns MISSING or NULL if a non-number input is encountered first.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

IFNAN(column1, column2 [,column3 [,column4]])

Returns first non-MISSING, non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

IFNANORINF(column1, column2 [,column3 [,column4]])

Returns first non-MISSING, non-Inf, or non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

NANIF(column1, column2 [,column3 [,column4]])

Returns NaN if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

NEGINFIF(column1, column2 [,column3 [,column4]])

Returns NegInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

POSINFIF(column1, column2 [,column3 [,column4]])

Returns PosInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

CLOCK_MILLIS()

Returns system clock at function evaluation time, as UNIX milliseconds. Varies during a query.

CLOCK_STR([string_fmt])

Returns system clock at function evaluation time, as a string in a supported format. Varies during a query.

string_fmt: The datetime format to return the system clock in.

DATE_ADD_MILLIS(column, integer_n, string_part)

Performs date arithmetic, and returns result of computation. n and part are used to define an interval or duration, which is then added (or subtracted) to the UNIX time stamp, returning the result.

column: Any column expression.
integer_n: The number of string_part's to add to the column value.
string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_ADD_STR(column, integer_n, string_part)

Performs date arithmetic. n and part are used to define an interval or duration, which is then added (or subtracted) to the date string in a supported format, returning the result.

column: Any column expression.
integer_n: The number of string_part's to add to the column value.
string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_DIFF_MILLIS(column1, column2, string_part)

Performs date arithmetic. Returns the elapsed time between two UNIX time stamps as an integer whose unit is part.

column1: Any column expression.
column2: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_DIFF_STR(column1, column2, string_part)

Performs date arithmetic. Returns the elapsed time between two date strings in a supported format, as an integer whose unit is part.

column1: Any column expression.
column2: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_PART_MILLIS(column1, string_part [, tz])

Returns date part as an integer. The date expression is a number representing UNIX milliseconds, and part is one of the following date part strings.

column1: Any column expression.
string_part: The component of the date to extract. Available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.
tz: The timezone to convert the local time to. Default to the system timezone if not specified. If an incorrect time zone is provided, the null is returned.

DATE_PART_STR(column1, string_part)

Returns date part as an integer. The date expression is a string in a supported format, and part is one of the supported date part strings.

column1: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.

DATE_TRUNC_MILLIS(column1, string_part)

Returns UNIX time stamp that has been truncated so that the given date part string is the least significant.

column1: Any column expression.
string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_TRUNC_STR(column1, string_part)

Returns ISO 8601 time stamp that has been truncated so that the given date part string is the least significant.

column1: Any column expression.
string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

MILLIS(column1)

Returns date that has been converted in a supported format to UNIX milliseconds.

column1: Any column expression.

STR_TO_MILLIS(column1)

Returns date that has been converted in a supported format to UNIX milliseconds.

column1: Any column expression.

MILLIS_TO_STR(column [, string_fmt])

Returns the string in the supported format to which the UNIX milliseconds has been converted.

column1: Any column expression.
string_fmt: The datetime format to return the system clock in.

MILLIS_TO_UTC(column [, string_fmt])

Returns the UTC string to which the UNIX time stamp has been converted in the supported format.

column1: Any column expression.
string_fmt: The datetime format to return the system clock in.

MILLIS_TO_TZ(column, string_tzname [, string_fmt])

Converts the UNIX time stamp to a string in the named time zone, and returns the string.

column1: Any column expression.
string_tzname: The time zone name.
string_fmt: The datetime format to return the system clock in.

NOW_MILLIS()

Returns statement time stamp as UNIX milliseconds; does not vary during a query.

NOW_STR([string_fmt])

Returns statement time stamp as a string in a supported format; does not vary during a query.

string_fmt: The datetime format to return the timestamp in.

STR_TO_UTC(column1)

Converts the ISO 8601 time stamp to UTC.

column1: Any column expression.

STR_TO_ZONE_NAME(column, string_tzname)

Converts the supported time stamp string to the named time zone.

column1: Any column expression.
string_tzname: The time zone name.

BASE64(expression)

Returns base64 encoding of expression.

expression: Any column or literal expression.

ABS(expression)

Returns absolute value of the number.

expression: Any column or literal expression.

ACOS(expression)

Returns arccosine in radians.

expression: Any column or literal expression.

ASIN(expression)

Returns arcsine in radians.

expression: Any column or literal expression.

ATAN(expression)

Returns arctangent in radians.

expression: Any column or literal expression.

ATAN2(expression1, expression2)

Returns arctangent of expression2/expression1.

expression1: Any column or literal expression.
expression2: Any column or literal expression.

CEIL(expression)

Returns smallest integer not less than the number.

expression: Any column or literal expression.

COS(expression)

Returns cosine.

expression: Any column or literal expression.

DEGREES(expression)

Returns radians to degrees.

expression: Any column or literal expression.

E()

Base of natural logarithms.

EXP(expression)

Returns e^expression.

expression: Any column or literal expression.

LN(expression)

Returns log base e.

expression: Any column or literal expression.

LOG(expression)

Returns log base 10.

expression: Any column or literal expression.

FLOOR(expression)

Largest integer not greater than the number.

expression: Any column or literal expression.

PI()

Returns PI.

POWER(expression1, expression2)

Returns expression1^expression2.

expression1: Any column or literal expression.
expression2: Any column or literal expression.

RADIANS(expression)

Returns degrees to radians.

expression: Any column or literal expression.

RANDOM([expression])

Returns pseudo-random number with optional seed.

expression: Any column or literal expression.

ROUND(expression [, integer_digits])

Rounds the value to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

expression: Any column or literal expression.
integer_digits: The number of digits to round to.

SIGN(expression)

Valid values: -1, 0, or 1 for negative, zero, or positive numbers respectively.

expression: Any column or literal expression.

SIN(expression)

Returns sine.

expression: Any column or literal expression.

SQRT(expression)

Returns square root.

expression: Any column or literal expression.

TAN(expression)

Returns tangent.

expression: Any column or literal expression.

TRUNC(expression [, integer_digits])

Truncates the number to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

expression: Any column or literal expression.
integer_digits: The number of digits to truncate.

CONTAINS(column, string_substring)

True if the string contains the substring.

column: Any column or literal expression.
string_substring: The substring to search for.

INITCAP(column)

Converts the string so that the first letter of each word is uppercase and every other letter is lowercase.

column: Any column or literal expression.

LENGTH(column)

Returns length of the string value.

column: Any column or literal expression.

LOWER(column)

Returns lowercase of the string value.

column: Any column or literal expression.

LTRIM(column [, string_chars])

Returns string with all leading chars removed. White space by default.

column: Any column or literal expression.
string_chars: The leading characters to remove.

POSITION(column, string_substring)

Returns the first position of the substring within the string, or -1. The position is zero-based, i.e., the first position is 0.

column: Any column or literal expression.
string_substring: The substring to search for.

REPEAT(column, integer_n)

Returns string formed by repeating expression n times.

column: Any column or literal expression.
integer_n: The number of times to repeat column.

REPLACE(column, string_substring, string_replace [, integer_n])

Returns string with all occurrences of substr replaced with repl. If n is given, at most n replacements are performed.

column: The column expression.
string_substring: The regular expression to match.
string_replace: The value to replace the matched pattern.
integer_n: The maximum number of replacements to make.

RTRIM(column [, string_chars])

Returns string with all trailing chars removed. White space by default.

column: Any column or literal expression.
string_chars: The trailing characters to remove.

SPLIT(column [, string_sep])

Splits the string into an array of substrings separated by string_sep. If string_sep is not given, any combination of white space characters is used.

column: Any column or literal expression.
string_sep: The separator to split column on.

SUBSTR(column, integer_position [, integer_length])

Returns substring from the integer position of the given length, or to the end of the string. The position is zero-based, i.e. the first position is 0. If position is negative, it is counted from the end of the string; -1 is the last position in the string.

column: Any column or literal expression.
integer_position: The starting position.
integer_length: The total length of the substring to retrieve.

TRIM(column [, string_chars])

Returns string with all leading and trailing chars removed. White space by default.

column: Any column or literal expression.
string_chars: The leading and trailing characters to remove.

UPPER(column)

Returns uppercase of the string value.

column: Any column or literal expression.

TOARRAY(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays are themselves; All other values are wrapped in an array.

column: Any column expression.

TOATOM(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays of length 1 are the result of TOATOM() on their single element; Objects of length 1 are the result of TOATOM() on their single value; Booleans, numbers, and strings are themselves; All other values are NULL.

column: Any column expression.

TOBOOLEAN(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; False is false; Numbers +0, -0, and NaN are false; Empty strings, arrays, and objects are false; All other values are true.

column: Any column expression.

TONUMBER(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; False is 0; True is 1; Numbers are themselves; Strings that parse as numbers are those numbers; All other values are NULL.

column: Any column expression.

TOOBJECT(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; Objects are themselves; All other values are the empty object.

column: Any column expression.

TOSTRING(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; False is "false"; True is "true"; Numbers are their string representation; Strings are themselves; All other values are NULL.

column: Any column expression.

Predicate Functions

REGEXP_CONTAINS(column, string_pattern)

Returns True if the string value contains the regular expression pattern.

column: The column expression.
string_pattern: The regular expression to match.

REGEXP_LIKE(column, string_pattern)

Returns True if the string value matches the regular expression pattern.

column: The column expression.
string_pattern: The regular expression to match.

REGEXP_POSITION(column, string_pattern)

Returns first position of the regular expression pattern within the string, or -1.

column: The column expression.
string_pattern: The regular expression to match.

REGEXP_REPLACE(column, string_pattern, string_replace [, integer_n])

Returns new string with occurrences of pattern replaced with string_replace. If n is given, at most n replacements are performed.

column: The column expression.
string_pattern: The regular expression to match.
string_replace: The value to replace the matched pattern.
integer_n: The maximum number of replacements to make.

ISARRAY(column)

Returns True if expression is an array, otherwise returns MISSING, NULL or false.

column: Any column expression.

ISATOM(column)

Returns True if expression is a Boolean, number, or string, otherwise returns MISSING, NULL or false.

column: Any column expression.

ISBOOLEAN(column)

Returns True if expression is a Boolean, otherwise returns MISSING, NULL or false.

column: Any column expression.

ISNUMBER(column)

Returns True if expression is a number, otherwise returns MISSING, NULL or false.

column: Any column expression.

ISOBJECT(column)

Returns True if expression is an object, otherwise returns MISSING, NULL or false.

column: Any column expression.

ISSTRING(column)

Returns True if expression is a string, otherwise returns MISSING, NULL or false.

column: Any column expression.

TYPE(column)

Returns one of the following strings, based on the value of expression: missing, null, boolean, number, string, array, object, or binary.

column: Any column expression.

ARRAY_AVG(column)

Returns arithmetic mean (average) of all the non-NULL number values in the array, or NULL if there are no such values.

column: Any column expression.

ARRAY_CONTAINS(column, value)

Returns true if the array contains value.

column: Any column expression.
value: The value contained within the array.

ARRAY_COUNT(column)

Returns count of all the non-NULL values in the array, or zero if there are no such values.

column: Any column expression.

ARRAY_LENGTH(column)

Returns the number of elements in the array.

column: Any column expression.

ARRAY_MAX(column)

Returns the largest non-NULL, non-MISSING array element, in N1QL collation order.

column: Any column expression.

ARRAY_MIN(column)

Returns smallest non-NULL, non-MISSING array element, in N1QL collation order.

column: Any column expression.

ARRAY_POSITION(column, value)

Returns the first position of value within the array, or -1. Array position is zero-based, i.e. the first position is 0.

column: Any column expression.
value: The value contained within the array.

ARRAY_SUM(column)

Sum of all the non-NULL number values in the array, or zero if there are no such values.

column: Any column expression.

GREATEST(column1, column2 [,column3 [,column4]])

Largest non-NULL, non-MISSING value if the values are of the same type; otherwise NULL.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

LEAST(column1, column2 [,column3 [,column4]])

Returns smallest non-NULL, non-MISSING value if the values are of the same type, otherwise returns NULL.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

IFMISSING(column1, column2 [,column3 [,column4]])

Returns the first non-MISSING value.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

IFMISSINGORNULL(column1, column2 [,column3 [,column4]])

Returns first non-NULL, non-MISSING value.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

IFNULL(column1, column2 [,column3 [,column4]])

Returns first non-NULL value. Note that this function might return MISSING if there is no non-NULL value.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

MISSINGIF(column1, column2)

Returns MISSING if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

column1: Any column expression.
column2: Any column expression.

NULLIF(column1, column2)

Returns NULL if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

column1: Any column expression.
column2: Any column expression.

IFINF(column1, column2 [,column3 [,column4]])

Returns first non-MISSING, non-Inf number. Returns MISSING or NULL if a non-number input is encountered first.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

IFNAN(column1, column2 [,column3 [,column4]])

Returns first non-MISSING, non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

IFNANORINF(column1, column2 [,column3 [,column4]])

Returns first non-MISSING, non-Inf, or non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

NANIF(column1, column2 [,column3 [,column4]])

Returns NaN if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

NEGINFIF(column1, column2 [,column3 [,column4]])

Returns NegInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

POSINFIF(column1, column2 [,column3 [,column4]])

Returns PosInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

column1: Any column expression.
column2: Any column expression.
column3: Any column expression.
column4: Any column expression.

CLOCK_MILLIS()

Returns system clock at function evaluation time, as UNIX milliseconds. Varies during a query.

CLOCK_STR([string_fmt])

Returns system clock at function evaluation time, as a string in a supported format. Varies during a query.

string_fmt: The datetime format to return the system clock in.

DATE_ADD_MILLIS(column, integer_n, string_part)

Performs date arithmetic, and returns result of computation. n and part are used to define an interval or duration, which is then added (or subtracted) to the UNIX time stamp, returning the result.

column: Any column expression.
integer_n: The number of string_part's to add to the column value.
string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_ADD_STR(column, integer_n, string_part)

Performs date arithmetic. n and part are used to define an interval or duration, which is then added (or subtracted) to the date string in a supported format, returning the result.

column: Any column expression.
integer_n: The number of string_part's to add to the column value.
string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_DIFF_MILLIS(column1, column2, string_part)

Performs date arithmetic. Returns the elapsed time between two UNIX time stamps as an integer whose unit is part.

column1: Any column expression.
column2: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_DIFF_STR(column1, column2, string_part)

Performs date arithmetic. Returns the elapsed time between two date strings in a supported format, as an integer whose unit is part.

column1: Any column expression.
column2: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_PART_MILLIS(column1, string_part [, tz])

Returns date part as an integer. The date expression is a number representing UNIX milliseconds, and part is one of the following date part strings.

column1: Any column expression.
string_part: The component of the date to extract. Available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.
tz: The timezone to convert the local time to. Default to the system timezone if not specified. If an incorrect time zone is provided, the null is returned.

DATE_PART_STR(column1, string_part)

Returns date part as an integer. The date expression is a string in a supported format, and part is one of the supported date part strings.

column1: Any column expression.
string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.

DATE_TRUNC_MILLIS(column1, string_part)

Returns UNIX time stamp that has been truncated so that the given date part string is the least significant.

column1: Any column expression.
string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

DATE_TRUNC_STR(column1, string_part)

Returns ISO 8601 time stamp that has been truncated so that the given date part string is the least significant.

column1: Any column expression.
string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

MILLIS(column1)

Returns date that has been converted in a supported format to UNIX milliseconds.

column1: Any column expression.

STR_TO_MILLIS(column1)

Returns date that has been converted in a supported format to UNIX milliseconds.

column1: Any column expression.

MILLIS_TO_STR(column [, string_fmt])

Returns the string in the supported format to which the UNIX milliseconds has been converted.

column1: Any column expression.
string_fmt: The datetime format to return the system clock in.

MILLIS_TO_UTC(column [, string_fmt])

Returns the UTC string to which the UNIX time stamp has been converted in the supported format.

column1: Any column expression.
string_fmt: The datetime format to return the system clock in.

MILLIS_TO_TZ(column, string_tzname [, string_fmt])

Converts the UNIX time stamp to a string in the named time zone, and returns the string.

column1: Any column expression.
string_tzname: The time zone name.
string_fmt: The datetime format to return the system clock in.

NOW_MILLIS()

Returns statement time stamp as UNIX milliseconds; does not vary during a query.

NOW_STR([string_fmt])

Returns statement time stamp as a string in a supported format; does not vary during a query.

string_fmt: The datetime format to return the timestamp in.

STR_TO_UTC(column1)

Converts the ISO 8601 time stamp to UTC.

column1: Any column expression.

STR_TO_ZONE_NAME(column, string_tzname)

Converts the supported time stamp string to the named time zone.

column1: Any column expression.
string_tzname: The time zone name.

BASE64(expression)

Returns base64 encoding of expression.

expression: Any column or literal expression.

ABS(expression)

Returns absolute value of the number.

expression: Any column or literal expression.

ACOS(expression)

Returns arccosine in radians.

expression: Any column or literal expression.

ASIN(expression)

Returns arcsine in radians.

expression: Any column or literal expression.

ATAN(expression)

Returns arctangent in radians.

expression: Any column or literal expression.

ATAN2(expression1, expression2)

Returns arctangent of expression2/expression1.

expression1: Any column or literal expression.
expression2: Any column or literal expression.

CEIL(expression)

Returns smallest integer not less than the number.

expression: Any column or literal expression.

COS(expression)

Returns cosine.

expression: Any column or literal expression.

DEGREES(expression)

Returns radians to degrees.

expression: Any column or literal expression.

E()

Base of natural logarithms.

EXP(expression)

Returns e^expression.

expression: Any column or literal expression.

LN(expression)

Returns log base e.

expression: Any column or literal expression.

LOG(expression)

Returns log base 10.

expression: Any column or literal expression.

FLOOR(expression)

Largest integer not greater than the number.

expression: Any column or literal expression.

PI()

Returns PI.

POWER(expression1, expression2)

Returns expression1^expression2.

expression1: Any column or literal expression.
expression2: Any column or literal expression.

RADIANS(expression)

Returns degrees to radians.

expression: Any column or literal expression.

RANDOM([expression])

Returns pseudo-random number with optional seed.

expression: Any column or literal expression.

ROUND(expression [, integer_digits])

Rounds the value to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

expression: Any column or literal expression.
integer_digits: The number of digits to round to.

SIGN(expression)

Valid values: -1, 0, or 1 for negative, zero, or positive numbers respectively.

expression: Any column or literal expression.

SIN(expression)

Returns sine.

expression: Any column or literal expression.

SQRT(expression)

Returns square root.

expression: Any column or literal expression.

TAN(expression)

Returns tangent.

expression: Any column or literal expression.

TRUNC(expression [, integer_digits])

Truncates the number to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

expression: Any column or literal expression.
integer_digits: The number of digits to truncate.

CONTAINS(column, string_substring)

True if the string contains the substring.

column: Any column or literal expression.
string_substring: The substring to search for.

INITCAP(column)

Converts the string so that the first letter of each word is uppercase and every other letter is lowercase.

column: Any column or literal expression.

LENGTH(column)

Returns length of the string value.

column: Any column or literal expression.

LOWER(column)

Returns lowercase of the string value.

column: Any column or literal expression.

LTRIM(column [, string_chars])

Returns string with all leading chars removed. White space by default.

column: Any column or literal expression.
string_chars: The leading characters to remove.

POSITION(column, string_substring)

Returns the first position of the substring within the string, or -1. The position is zero-based, i.e., the first position is 0.

column: Any column or literal expression.
string_substring: The substring to search for.

REPEAT(column, integer_n)

Returns string formed by repeating expression n times.

column: Any column or literal expression.
integer_n: The number of times to repeat column.

REPLACE(column, string_substring, string_replace [, integer_n])

Returns string with all occurrences of substr replaced with repl. If n is given, at most n replacements are performed.

column: The column expression.
string_substring: The regular expression to match.
string_replace: The value to replace the matched pattern.
integer_n: The maximum number of replacements to make.

RTRIM(column [, string_chars])

Returns string with all trailing chars removed. White space by default.

column: Any column or literal expression.
string_chars: The trailing characters to remove.

SPLIT(column [, string_sep])

Splits the string into an array of substrings separated by string_sep. If string_sep is not given, any combination of white space characters is used.

column: Any column or literal expression.
string_sep: The separator to split column on.

SUBSTR(column, integer_position [, integer_length])

column: Any column or literal expression.
integer_position: The starting position.
integer_length: The total length of the substring to retrieve.

TRIM(column [, string_chars])

Returns string with all leading and trailing chars removed. White space by default.

column: Any column or literal expression.
string_chars: The leading and trailing characters to remove.

UPPER(column)

Returns uppercase of the string value.

column: Any column or literal expression.

TOARRAY(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays are themselves; All other values are wrapped in an array.

column: Any column expression.

TOATOM(column)

column: Any column expression.

TOBOOLEAN(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; False is false; Numbers +0, -0, and NaN are false; Empty strings, arrays, and objects are false; All other values are true.

column: Any column expression.

TONUMBER(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; False is 0; True is 1; Numbers are themselves; Strings that parse as numbers are those numbers; All other values are NULL.

column: Any column expression.

TOOBJECT(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; Objects are themselves; All other values are the empty object.

column: Any column expression.

TOSTRING(column)

Returns array as follows: MISSING is MISSING; NULL is NULL; False is "false"; True is "true"; Numbers are their string representation; Strings are themselves; All other values are NULL.

column: Any column expression.

PreviousAmazon DynamoDB NextMongoDB

Last updated 2 years ago