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

1. Return all columns:

   SELECT * FROM Customer
2. Rename a column:

   SELECT [TotalDue] AS MY_TotalDue FROM Customer
3. Cast a column's data as a different data type:

   SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Customer
4. Search data:

   SELECT * FROM Customer WHERE CustomerId = '12345'
5. The Couchbase APIs support the following operators in the WHERE clause: =, !=, <, <=, >, >=, IS NULL, LIKE, AND, OR, NOT, IN.

   SELECT * FROM Customer WHERE CustomerId = '12345';
6. Return the number of items matching the query criteria:

   SELECT COUNT(*) AS MyCount FROM Customer
7. Return the number of unique items matching the query criteria:

   SELECT COUNT(DISTINCT TotalDue) FROM Customer
8. Return the unique items matching the query criteria:

   SELECT DISTINCT TotalDue FROM Customer
9. Summarize data:

   SELECT TotalDue, MAX(AnnualRevenue) FROM Customer GROUP BY TotalDue

   See Aggregate Functions below for details.

10. Retrieve data from multiple tables.

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

    See JOIN Queries below for details.

11. Sort a result set in ascending order:

    SELECT Name, TotalDue FROM Customer ORDER BY TotalDue ASC
12. Restrict a result set to the specified number of rows:

    SELECT Name, TotalDue FROM Customer LIMIT 10
13. 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])

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.