SQL++ for Mobile
N1QL is Couchbase's implementation of the developing SQL++ standard. As such the terms N1QL and SQL++ are used interchangeably in all Couchbase documentation unless explicitly stated otherwise.
Introduction
Developers using Couchbase Lite for Dart can provide SQL++ query strings using the SQL++ Query API. This API uses query statements of the form shown in Example 1. The structure and semantics of the query format are based on that of Couchbase Server's SQL++ query language — see SQL++ Reference Guide and SQL++ Data Model.
Running
Use Database.createQuery
to define a query through an SQL++ string. Then
run the query using the Query.execute()
method.
final database = await Database.openAsync('hotels');
final query = await database.createQuery(
'SELECT META().id AS docId FROM _ WHERE type = "hotel"',
);
final resultSet = await query.execute();
Here we are accessing the default collection using the shorthand notation (_
)
— see the FROM clause for more on data source selection and
Query Parameters for more on parameterized queries.
Query Format
The API uses query statements of the form shown in Example 2.
SELECT ____
FROM ____
JOIN ____
WHERE ____
GROUP BY ____
ORDER BY ____
LIMIT ____
OFFSET ____
Query Components
- The
SELECT
clause specifies the data to be returned in the result set. - The
FROM
clause specifies the collection to query the documents from. - The
JOIN
clause specifies the criteria for joining multiple documents. - The
WHERE
clause specifies the query criteria. TheSELECT
ed properties of documents matching this criteria will be returned in the result set. - The
GROUP BY
clause specifies the criteria used to group returned items in the result set. - The
ORDER BY
clause specifies the criteria used to order the items in the result set. - The
LIMIT
clause specifies the maximum number of results to be returned. - The
OFFSET
clause specifies the number of results to be skipped before starting to return results.
We recommend working through the SQL++ Tutorials as a good way to build your SQL++ skills.
SELECT Clause
Purpose
Projects the result returned by the query, identifying the columns it will contain.
Syntax
select = SELECT _ ( ( DISTINCT | ALL ) _ )? selectResults
selectResults = selectResult ( _? ',' _? selectResult )*
selectResult = expression ( ( _ AS )? _ columnAlias )?
columnAlias = IDENTIFIER
Arguments
- The select clause begins with the
SELECT
keyword.- The optional
ALL
argument is used to specify that the query should returnALL
results (the default). - The optional
DISTINCT
argument is used to specify that the query should return distinct results.
- The optional
selectResults
is a list of columns projected in the query result. Each column is an expression which could be a property expression or any expression or function. You can use the*
expression, to select all columns.- Use the optional
AS
argument to provides an alias for a column. Each column can be aliased by putting the alias name after the column name.
SELECT Wildcard
When using the *
expression, the column name is one of:
- The alias name, if one was specified.
- The data source name (or its alias if provided) as specified in the FROM clause.
This behavior is inline with that of SQL++ for Server — see example in Table 1.
Query | Column Name |
---|---|
SELECT * AS data FROM _ | data |
SELECT * FROM _ | _ |
SELECT * FROM _default | _default |
SELECT * FROM users | users |
SELECT * FROM users AS user | user |
Example
SELECT * ...;
SELECT user.* AS data ...;
SELECT name fullName ...;
SELECT user.name ...;
SELECT DISTINCT address.city ...;
- Use the
*
expression to select all columns. - Select all properties from the
user
data source. Give the object an alias ofdata
. - Select a pair of properties.
- Select a specific property from the
user
data source. - Select the property
city
from theaddress
data source.
FROM Clause
Purpose
Specifies the data source and optionally applies an alias (AS
). It is
mandatory.
Syntax
from = FROM _ dataSource
dataSource = collectionName ( ( _ AS )? _ collectionAlias )?
collectionName = IDENTIFIER
collectionAlias = IDENTIFIER
Here dataSource
is the collection name against which the query is to run. Use
AS
to give the collection an alias you can use within the query. To use the
default collection, without specifying a name, use _
as the data source.
Example
SELECT name FROM user;
SELECT user.name FROM users AS user;
SELECT user.name FROM users user;
SELECT name FROM _;
SELECT user.name FROM _ AS user;
SELECT user.name FROM _ user;
JOIN Clause
Purpose
The JOIN
clause enables you to select data from multiple data sources linked
by criteria specified in the ON
constraint. Currently only self-joins are
supported. For example to combine airline details with route details, linked by
the airline id — see Example 7.
Syntax
join = joinOperator _ dataSource ( _ constraint )?
joinOperator = ( ( LEFT ( _ OUTER )? | INNER | CROSS ) _ )? JOIN
dataSource = collectionName ( ( _ AS )? _ collectionAlias )?
constraint = ON _ expression
collectionName = IDENTIFIER
collectionAlias = IDENTIFIER
Arguments
- The
JOIN
clause starts with aJOIN
operator followed by the data source. - Five
JOIN
operators are supported:JOIN
,LEFT JOIN
,LEFT OUTER JOIN
,INNER JOIN
, andCROSS JOIN
.- Note:
JOIN
andINNER JOIN
are the same, andLEFT JOIN
andLEFT OUTER JOIN
are the same.
- The
JOIN
constraint starts with theON
keyword followed by the expression that defines the joining constraints.
Example
SELECT users.prop1, other.prop2
FROM users
JOIN users AS other ON users.key = other.key;
SELECT users.prop1, other.prop2
FROM users
LEFT JOIN users AS other ON users.key = other.key;
This example joins the documents from the routes
collections with documents
from the airlines
collection using the document ID (id
) of the airline
document and the airlineId
property of the route document.
SELECT *
FROM routes r
JOIN airlines a ON r.airlineId = META(a).id
WHERE a.country = "France";
WHERE Clause
Purpose
Specifies the selection criteria used to filter results. As with SQL, use the
WHERE
clause to choose which results are returned by your query.
Syntax
where = WHERE _ expression
Arguments
WHERE
evalates the expression to aBOOLEAN
value. You can combine any number of expressions through logical operators, in order to implement sophisticated filtering capabilities.
Example
SELECT name
FROM employees
WHERE department = "engineer" AND group = "mobile";
GROUP BY Clause
Purpose
Use GROUP BY
to group results for aggreation, based on one or more
expressions.
Syntax
groupBy = grouping ( _ having )?
grouping = GROUP BY _ expression ( _? ',' _? expression )*
having = HAVING _ expression
Arguments
- The
GROUP BY
clause starts with theGROUP BY
keyword followed by one or more expressions. - The
GROUP BY
clause is normally used together with aggregate functions (e.g.COUNT
,MAX
,MIN
,SUM
,AVG
). - The
HAVING
clause allows you to filter the results based on aggregate functions — for example,HAVING COUNT(airlineId) > 100
.
Example
SELECT COUNT(airlineId), destination
FROM routes
GROUP BY destination;
SELECT COUNT(airlineId), destination
FROM routes
GROUP BY destination
HAVING COUNT(airlineId) > 100;
SELECT COUNT(airlineId), destination
FROM routes
WHERE destinationState = "CA"
GROUP BY destination
HAVING COUNT(airlineId) > 100;
ORDER BY Clause
Purpose
Sort query results based on a expression.
Syntax
orderBy = ORDER BY _ ordering ( _? ',' _? ordering )*
ordering = expression ( _ order )?
order = ( ASC | DESC )
Arguments
- The
ORDER BY
clause starts with theORDER BY
keyword followed by one or more ordering expressions. - An ordering expression specifies an expressions to use for ordering the results.
- For each ordering expression, the sorting direction can be specified using
the optional
ASC
(ascending) orDESC
(descending) directives. Default isASC
.
Example
SELECT name
FROM users
ORDER BY name;
SELECT name
FROM users
ORDER BY name DESC;
SELECT name, score
FROM users
ORDER BY name ASC, score DESC;
LIMIT Clause
Purpose
Specifies the maximum number of results to be returned by the query.
Syntax
limit = LIMIT _ expression
Arguments
- The
LIMIT
clause starts with theLIMIT
keyword followed by an expression that will be evaluated as a number.
Example
SELECT name
FROM users
LIMIT 10;
OFFSET Clause
Purpose
Specifies the number of results to be skipped by the query.
Syntax
offset = OFFSET _ expression
Arguments
- The offset clause starts with the
OFFSET
keyword followed by an expression that will be evaluated as a number that represents the number of results to be skipped before the query begins returning results.
Example
SELECT name
FROM users
OFFSET 10;
SELECT name
FROM users
LIMIT 10
OFFSET 10;
Expressions
An expression is a specification for a value that is resolved when executing a query. This section, together with Operators and Functions, which are covered in their own sections, covers all the available types of expressions.
Literals
Boolean
Purpose
Represents a true or false value.
Syntax
boolean = ( TRUE | FALSE )
Example
SELECT fullTime
FROM users
WHERE fullTime = true;
SELECT fullTime
FROM users
WHERE fullTime = false;
Numeric
Purpose
Represents a numeric value. Numbers may be signed or unsigned digits. They have optional fractional and exponent components.
Syntax
numeric = -? ( ( . digit+ ) | ( digit+ ( . digit* )? ) ) ( ( E | e ) ( - | + )? digit+ )?
digit = /[0-9]/
Example
SELECT
10,
0,
-10,
10.25,
10.25e2,
10.25E2,
10.25E+2,
10.25E-2
FROM _;
String
Purpose
The string literal represents a string or sequence of characters.
Syntax
string = ( " character* " | ' character* ' )
character = ( escapeSequence | any codepoint except ", ' or control characters )
escapeSequence = \ ( " | ' | \ | / | b | f | n | r | t | u hex hex hex hex )
hex = hexDigit hexDigit
hexDigit = /[0-9a-fA-F]/
The string literal can be double-quoted as well as single-quoted.
Example
SELECT firstName, lastName
FROM users
WHERE middleName = "middle" AND lastName = 'last';
NULL
Purpose
Represents the absence of a value.
Syntax
null = NULL
Example
SELECT firstName, lastName
FROM users
WHERE middleName IS NULL;
MISSING
Purpose
Represents a missing name-value pair in a dictionary.
Syntax
missing = MISSING
Example
SELECT firstName, lastName
FROM users
WHERE middleName IS MISSING;
Array
Purpose
Represents an array.
Syntax
array = [ ( _? expression ( _? ',' _? expression )* _? )? ]
Example
SELECT ["a", "b", "c"]
FROM _;
SELECT [property1, property2, property3]
FROM _;
Dictionary
Purpose
Represents a dictionary.
Syntax
dictionary = { ( _? string _? : _? expression ( _? , _? string _? : _? expression )* _? )? }
Example
SELECT { 'name': 'James', 'department': 10 }
FROM _;
SELECT { 'name': 'James', 'department': dept }
FROM _;
SELECT { 'name': 'James', 'phones': ['650-100-1000', '650-100-2000'] }
FROM _;
Identifier
Purpose
An identifier references an entity by its symbolic name. Use an identifier for example to identify:
- Column alias names
- Collection names
- Collection alias names
- Property names
- Parameter names
- Function names
- FTS index names
Syntax
identifier = ( plainIdentifier | quotedIdentifier )
plainIdentifier = /[a-zA-Z_][a-zA-Z0-9_$]*/
quotedIdentifier = /`[^`]+`/
To use other than basic characters in the identifier, surround the identifier with the backticks ` character. For example, to use a hyphen (-) in an identifier, use backticks to surround the identifier.
Example
SELECT *
FROM _;
SELECT *
FROM `users-1`;
SELECT key
FROM users;
SELECT key$1
FROM users_1;
SELECT `key-1`
FROM users;
Property Expression
Purpose
The property expression is used to reference a property of a dictionary.
Syntax
property = ( * | dataSourceName . _? * | propertyPath )
propertyPath = propertyName ( ( . _? propertyName ) | ( [ _? numeric _? ] _? ) )*
propertyName = IDENTIFIER
- Prefix the property expression with the data source name or alias to indicate its origin.
- Use dot syntax to refer to nested properties in the propertyPath.
- Use bracket (
[index]
) syntax to refer to an item in an array. - Use the asterisk (
*
) character to represents all properties. This can only be used in the result list of theSELECT
clause.
Example
SELECT *
FROM users
WHERE contact.name = 'daniel';
SELECT users.*
FROM users
WHERE contact.name = 'daniel';
SELECT users.contact.address.city
FROM users
WHERE contact.name = 'daniel';
SELECT contact.address.city, contact.phones[0]
FROM users
WHERE contact.name = 'daniel';
Any and Every Expression
Purpose
Evaluates expressions over items in an array.
Syntax
arrayExpression = anyEvery _ variableName _ IN _ expression _ SATISFIES _ expression _ END
anyEvery = ( anyOrSome AND EVERY | anyOrSome | EVERY )
anyOrSome = ( ANY | SOME )
variableName = IDENTIFIER
The array expression starts with
anyEvery
, where each possible combination has a different function as described below, and is terminated byEND
.ANY
orSOME
: ReturnsTRUE
if at least one item in the array satisfies the expression, otherwise returnsFALSE
.noteANY
andSOME
are interchangeable.EVERY
: ReturnsTRUE
if all items in the array satisfies the expression, otherwise returnsFALSE
. If the array is empty, returnsTRUE
.( ANY | SOME ) AND EVERY
: Same asEVERY
but returnsFALSE
if the array is empty.
The
variableName
represents each item in the array.The
IN
keyword is used to specify the array to be evaluated.The
SATISFIES
keyword is used to specify the expression to evaluate for each item in the array.END
terminates the array expression.
Example
SELECT name
FROM users
WHERE
ANY contact IN contacts
SATISFIES contact.city = 'San Mateo'
END;
Parameter Expression
Purpose
A parameter expression references a value from the Parameters
assigned to
the query before execution.
If a parameter is specified in the query string, but no value has been provided, an error will be thrown when executing the query.
Syntax
parameter = $ IDENTIFIER
Example
SELECT name
FROM epmolyees
WHERE department = $department;
final query = await db.createQuery(
r'''
SELECT name
FROM employees
WHERE department = $department
''',
);
query.parameters = Parameters({'department': 'E001'});
final results = query.execute();
Parenthesis Expression
Purpose
Use parentheses to group expressions together to make them more readable or to establish operator precedence.
Example
SELECT (value1 + value2) * value 3
FROM _;
SELECT *
FROM _
WHERE ((value1 + value2) * value3) + value4 = 10;
SELECT *
FROM _
WHERE (value1 = value2)
OR (value3 = value4);
Operators
Binary Operators
Maths
Op | Description | Example |
---|---|---|
+ | Add | WHERE v1 + v2 = 10 |
- | Subtract | WHERE v1 - v2 = 10 |
* | Multiply | WHERE v1 \* v2 = 10 |
/ | Divide - see 1 | WHERE v1 / v2 = 10 |
% | Modulus | WHERE v1 % v2 = 0 |
- If both operands are integers, integer division is used, but if one is a
floating number, then float division is used. This differs from SQL++ for
Server, which performs float division regardless. Use
DIV(x, y)
to force float division in SQL++ for Mobile.
Comparison Operators
Purpose
The comparison operators can for example be used in the WHERE
clause to
specify the condition on which to match documents.
Op | Description | Example |
---|---|---|
= or == | Equals | WHERE v1 = v2<br/> WHERE v1 == v2 |
!= or <> | Not Equal to | WHERE v1 != v2<br/> WHERE v1 <> v2 |
> | Greater than | WHERE v1 > v2 |
>= | Greater than or equal to | WHERE v1 >= v2 |
< | Less than | WHERE v1 < v2 |
<= | Less than or equal to | WHERE v1 <= v2 |
IN | Returns TRUE if the value is in the list or array of values specified by the right hand side expression; Otherwise returns FALSE . | WHERE 'James' IN contactsList |
LIKE | String wildcard pattern matching, comparison - see 2. Two wildchards are supported: • % Matches zero or more characters. • _` Matches a single character. | WHERE name LIKE 'a%' WHERE name LIKE '%a' WHERE name LIKE '%or%' WHERE name LIKE 'a%o%' WHERE name LIKE '%_r%' WHERE name LIKE '%a_%' WHERE name LIKE '%a__%' WHERE name LIKE 'aldo' |
MATCH | String matching using FTS | WHERE v1-index MATCH "value" |
BETWEEN | Logically equivalent to v1 >= start AND v1 <= end | WHERE v1 BETWEEN 10 AND 100 |
IS NULL - see 3 | Equal to null | WHERE v1 IS NULL |
IS NOT NULL | Not equal to null | WHERE v1 IS NOT NULL |
IS MISSING | Equal to MISSING | WHERE v1 IS MISSING |
IS NOT MISSING | Not equal to MISSING | WHERE v1 IS NOT MISSING |
IS VALUED | Logically equivalent to IS NOT NULL AND MISSING | WHERE v1 IS VALUED |
IS NOT VALUED | Logically equivalent to IS NULL OR MISSING | WHERE v1 IS NOT VALUED |
- Matching is case-insensitive for ASCII characters, case-sensitive for non-ASCII.
- Use of
IS
andIS NOT
is limited to comparingNULL
andMISSING
values (this encompassesVALUED
). This is different fromQueryBuilder
, in which they operate as equivalents of==
and!=
.
Op | Non-NULL Value | NULL | MISSING |
---|---|---|---|
IS NULL | FALSE | TRUE | MISSING |
IS NOT NULL | TRUE | FALSE | MISSING |
IS MISSING | FALSE | FALSE | TRUE |
IS NOT MISSING | TRUE | TRUE | FALSE |
IS VALUED | TRUE | FALSE | FALSE |
IS NOT VALUED | FALSE | TRUE | TRUE |
Logical Operators
Purpose
Logical operators combine expressions using the following boolean logic rules:
TRUE
isTRUE
, andFALSE
isFALSE
.- Numbers
0
or0.0
areFALSE
. - Arrays and dictionaries are
FALSE
. - Strings and Blobs are
TRUE
if the values are casted as a non-zero orFALSE
if the values are casted as0
or0.0
. NULL
isFALSE
.MISSING
isMISSING
.
This is different from SQL++ for Server, where:
MISSING
,NULL
andFALSE
areFALSE
.- Numbers
0
isFALSE
. - Empty strings, arrays, and objects are
FALSE
. - All other values are
TRUE
.
Use the TOBOOLEAN(expr)
function to convert a value based on SQL++ for Server
boolean value rules.
Op | Description | Example |
---|---|---|
AND | Returns TRUE if the operand expressions evaluate to TRUE ; otherwise FALSE .If an operand is MISSING and the other is TRUE returns MISSING , if the other operand is FALSE it returns FALSE .If an operand is NULL and the other is TRUE returns NULL , if the other operand is FALSE it returns FALSE . | WHERE city = 'San Francisco' AND status = TRUE |
OR | Returns TRUE if one of the operand expressions is evaluated to TRUE ; otherwise returns FALSE If an operand is MISSING , the operation will result in MISSING if the other operand is FALSE or TRUE if the other operand is TRUE .If an operand is NULL , the operation will result in NULL if the other operand is FALSE or TRUE if the other operand is TRUE . | WHERE city = 'San Francisco' OR city = 'Santa Clara' |
a | b | a AND b | a OR b |
---|---|---|---|
TRUE | TRUE | TRUE | TRUE |
FALSE | FALSE | TRUE | |
NULL | FALSE , see 5 | TRUE | |
MISSING | MISSING | TRUE | |
FALSE | TRUE | FALSE | TRUE |
FALSE | FALSE | FALSE | |
NULL | FALSE | FALSE , see 5 | |
MISSING | FALSE | MISSING | |
NULL | TRUE | FALSE , see 5 | TRUE |
FALSE | FALSE | FALSE , see 5 | |
NULL | FALSE , see 5 | FALSE , see 5 | |
MISSING | FALSE , see 6 | MISSING , see 7 | |
MISSING | TRUE | MISSING | TRUE |
FALSE | FALSE | MISSING | |
NULL | FALSE , see 6 | FALSE , see 7 | |
MISSING | MISSING | MISSING |
This differs from SQL++ for Server in the following instances:
- Server will return:
NULL
instead ofFALSE
.
- Server will return:
- Server will return:
MISSING
instead ofFALSE
.
- Server will return:
- Server will return:
NULL
instead ofMISSING
.
- Server will return:
String Operators
Purpose
A single string operator is provided. It enables string concatenation.
Op | Description | Example |
---|---|---|
|| | Concatenating | SELECT firstName || lastName AS fullName FROM db |
Unary Operators
Purpose
Three unary operators are provided. They operate by modifying an expression,
making it numerically positive or negative, or by logically negating its value
(TRUE
becomes FALSE
).
Syntax
unaryOperator = ( + | - | NOT ) _ expression
Op | Description | Example |
---|---|---|
+ | Positive value | WHERE v1 = +10 |
- | Negative value | WHERE v1 = -10 |
NOT | Logical Negate operator, see 8 | WHERE "James" NOT IN contactsList |
- The
NOT
operator is often used in conjunction with operators such asIN
,LIKE
,MATCH
, andBETWEEN
operators.NOT
operation onNULL
value returnsNULL
.NOT
operation onMISSING
value returnsMISSING
.
a | NOT a |
---|---|
TRUE | FALSE |
FALSE | TRUE |
NULL | FALSE |
MISSING | MISSING |
COLLATE
Operator
Purpose
Collate operators specify how a string comparison is conducted.
Usage
The collate operator is used in conjunction with string comparison expressions
and ORDER BY
clauses. It allows for one or more collations. If multiple
collations are used, the collations need to be specified in a parenthesis. When
only one collation is used, the parenthesis is optional.
Collation is not supported by SQL++ for Server.
Syntax
collate = COLLATE _ ( collation | '(' collation ( _ collation )+ ')' )
collation = NO? (UNICODE | CASE | DIACRITICS)
Arguments
- The available collation options are:
UNICODE
: Conduct a Unicode comparison; the default is to do ASCII comparison.CASE
: Conduct case-sensitive comparisonDIACRITIC
: Take accents and diacritics into account in the comparison; On by default.NO
: This can be used as a prefix to the other collations, to disable them. For example, useNOCASE
to enable case-insensitive comparison.
Example
SELECT department
FROM employees
WHERE name = "fred" COLLATE UNICODE;
SELECT department
FROM employees
WHERE name = "fred" COLLATE (UNICODE CASE);
SELECT name
FROM employees
ORDER BY name COLLATE (UNICODE DIACRITIC);
Conditional Operator
Purpose
The conditional (or CASE
) operator evaluates conditional logic in a similar
way to the IF
/ELSE
operator.
Syntax
case = CASE _ ( expression _ )?
( WHEN _ expression _ THEN _ expression _ )+
( ELSE _ expression _)?
END
Both Simple Case and Searched Case expressions are supported. The syntactic
difference being that the Simple Case expression has an expression after the
CASE
keyword.
- Simple Case Expression
- If the
CASE
expression is equal to the firstWHEN
expression, the result is theTHEN
expression. - Otherwise, any subsequent
WHEN
clauses are evaluated in the same way. - If no match is found, the result of the
CASE
expression is theELSE
expression, orNULL
if noELSE
expression was provided.
- If the
- Searched Case Expression
- If the first
WHEN
expression isTRUE
, the result of this expression is itsTHEN
expression. - Otherwise, subsequent
WHEN
clauses are evaluated in the same way. - If no
WHEN
clause evaluate toTRUE
, then the result of the expression is theELSE
expression, orNULL
if noELSE
expression was provided.
- If the first
Examples
SELECT
CASE state
WHEN 'CA'
THEN 'Local'
ELSE 'Non-Local'
END
FROM user;
Examples
SELECT
CASE
WHEN shippedOn IS NOT NULL
THEN 'SHIPPED'
ELSE 'NOT-SHIPPED'
END
FROM orders;
Functions
Purpose
Functions provide specialised operations through a generalized syntax.
Syntax
The function syntax is the same as C-style language function syntax. It starts with the function name, followed by optional arguments inside parentheses.
function = functionName _? '(' ( _? expression ( _? ',' _? expression )* _? )? ')'
functionName = IDENTIFIER
Aggregation Functions
Function | Description |
---|---|
AVG(value) | Returns the average of all numeric values in the group. |
COUNT(value) | Returns the count of all values in the group. |
MIN(value) | Returns the minimum numeric value in the group. |
MAX(value) | Returns the maximum numeric value in the group. |
SUM(value) | Returns the sum of all numeric values in the group. |
Array Functions
Function | Description |
---|---|
ARRAY_AGG(value) | Returns an array of the non-MISSING group values in the input expression, including NULL values. |
ARRAY_AVG(value) | Returns the average of all non-NULL number values in the array; or NULL if there are none. |
ARRAY_CONTAINS(value) | Returns TRUE if the value exists in the array; otherwise FALSE . |
ARRAY_COUNT(value) | Returns the number of non-NULL values in the array. |
ARRAY_IFNULL(value) | Returns the first non-NULL value in the array. |
ARRAY_MAX(value) | Returns the largest non-NULL , non_MISSING value in the array. |
ARRAY_MIN(value) | Returns the smallest non-NULL , non_MISSING value in the array. |
ARRAY_LENGTH(value) | Returns the length of the array. |
ARRAY_SUM(value) | Returns the sum of all non-NULL numeric value in the array. |
Conditional Functions
Function | Description |
---|---|
IFMISSING(value, ...) | Returns the first non-MISSING value, or NULL if all values are MISSING . |
IFMISSINGORNULL(value, ...) | Returns the first non-NULL and non-MISSING value, or NULL if all values are NULL or MISSING . |
IFNULL(value, ...) | Returns the first non-NULL , or NULL if all values are NULL . |
MISSINGIF(value, other) | Returns MISSING when value = other ; otherwise returns value . Returns MISSING if either or both expressions are MISSING .Returns NULL if either or both expressions are NULL . |
NULLIF(value, other) | Returns NULL when value = other ; otherwise returns value . Returns MISSING if either or both expressions are MISSING .Returns NULL if either or both expressions are NULL . |
Date and Time Functions
Function | Description |
---|---|
STR_TO_MILLIS(value) | Returns the number of milliseconds since the unix epoch of the given ISO 8601 date input string. |
STR_TO_UTC(value) | Returns the ISO 8601 UTC date time string of the given ISO 8601 date input string. |
MILLIS_TO_STR(value) | Returns a ISO 8601 date time string in device local timezone of the given number of milliseconds since the unix epoch expression. |
MILLIS_TO_UTC(value) | Returns the UTC ISO 8601 date time string of the given number of milliseconds since the unix epoch expression. |
Full Text Search Functions
Function | Description | Example |
---|---|---|
MATCH(indexName, term )` | Returns TRUE if term expression matches the FTS indexed document. indexName identifies the FTS index to search for matches. | WHERE MATCH(description, 'couchbase') |
RANK(indexName) | Returns a numeric value indicating how well the current query result matches the full-text query when performing the MATCH. indexName is an IDENTIFIER for the FTS index. | WHERE MATCH(description, 'couchbase') ORDER BY RANK(description) |
Maths Functions
Function | Description |
---|---|
ABS(value) | Returns the absolute value of a number. |
ACOS(value) | Returns the arc cosine in radians. |
ASIN(value) | Returns the arcsine in radians. |
ATAN(value) | Returns the arctangent in radians. |
ATAN2(a, b) | Returns the arctangent of a / b . |
CEIL(value) | Returns the smallest integer not less than the number. |
COS(value) | Returns the cosine of an angle in radians. |
DIV(a, b) | Returns float division of a and b . Both a and b are cast to a double number before division. The returned result is always a double. |
DEGREES(value) | Converts radians to degrees. |
E() | Returns the e constant, which is the base of natural logarithms. |
EXP(value) | Returns the natural exponential of a number. |
FLOOR(value) | Returns largest integer not greater than the number. |
IDIV(a, b) | Returns integer division of a and b . |
LN(value) | Returns log base e. |
LOG(value) | Returns log base 10. |
PI() | Returns the pi constant. |
POWER(a, b) | Returns a to the b th power. |
RADIANS(value) | Converts degrees to radians. |
ROUND(value (, digits)?) | Returns the rounded value to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits are 0 if not given. The function uses Rounding Away From Zero convention to round midpoint values to the next number away from zero (so, for example, ROUND(1.75) returns 1.8 but ROUND(1.85) returns 1.9. |
ROUND_EVEN(value (, digits)?) | Returns rounded value to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits are 0 if not given. The function uses Rounding to Nearest Even (Banker's Rounding) convention which rounds midpoint values to the nearest even number (for example, both ROUND_EVEN(1.75) and ROUND_EVEN(1.85) return 1.8). |
SIGN(value) | Returns -1 for negative, 0 for zero, and 1 for positive numbers. |
SIN(value) | Returns sine of an angle in radians. |
SQRT(value) | Returns the square root. |
TAN(value) | Returns tangent of an angle in radians. |
TRUNC(value (, digits)?) | Returns a truncated number to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits are 0 if not given. |
The behavior of the ROUND()
function is different from SQL++ for Server
ROUND()
, which rounds the midpoint values using Rounding to Nearest Even
convention.
Pattern Searching Functions
Function | Description |
---|---|
REGEXP_CONTAINS(value, pattern) | Returns TRUE if the string value contains any sequence that matches the regular expression pattern. |
REGEXP_LIKE(value, pattern) | Return TRUE if the string value exactly matches the regular expression pattern. |
REGEXP_POSITION(value, pattern) | Returns the first position of the occurrence of the regular expression pattern within the input string expression. Returns -1 if no match is found. Position counting starts from zero. |
REGEXP_REPLACE(value, pattern, repl [, n]) | Returns a new string with occurrences of pattern replaced with repl . If n is given, at the most n replacements are performed. If n is not given, all matching occurrences are replaced. |
String Functions
Function | Description |
---|---|
CONTAINS(value, substring) | Returns TRUE if the substring exists within the input string, otherwise returns FALSE . |
LENGTH(value) | Returns the length of a string. The length is defined as the number of characters within the string. |
LOWER(value) | Returns the lower-case string of the input string. |
LTRIM(value) | Returns the string with all leading whitespace characters removed. |
RTRIM(value) | Returns the string with all trailing whitespace characters removed. |
TRIM(value) | Returns the string with all leading and trailing whitespace characters removed. |
UPPER(value) | Returns the upper-case string of the input string. |
Type Checking Functions
Function | Description |
---|---|
ISARRAY(value) | Returns TRUE if value is an array, otherwise returns MISSING , NULL or FALSE . |
ISATOM(value) | Returns TRUE if value is a boolean, number, or string, otherwise returns MISSING , NULL or FALSE . |
ISBOOLEAN(value) | Returns TRUE if value is a boolean, otherwise returns MISSING , NULL or FALSE . |
ISNUMBER(value) | Returns TRUE if value is a number, otherwise returns MISSING , NULL or FALSE . |
ISOBJECT(value) | Returns TRUE if value is an object (dictionary), otherwise returns MISSING , NULL or FALSE . |
ISSTRING(value) | Returns TRUE if value is a string, otherwise returns MISSING , NULL or FALSE . |
TYPE(value) | Returns one of the following strings, based on the value of value :• "missing" • "null" • "boolean" • "number" • "string" • "array" • "object" • "binary" |
Type Conversion Functionsunctions
Function | Description |
---|---|
TOARRAY(value) | Returns MISSING if the value is MISSING .Returns NULL if the value is NULL .Returns an array value as is. Returns all other values wrapped in an array. |
TOATOM(value) | Returns MISSING if the value is MISSING .Returns NULL if the value is NULL .Returns an array of a single item if the value is an array. Returns an object of a single key/value pair if the value is an object. Returns a boolean, number, or string value as is. Returns NULL for all other values. |
TOBOOLEAN(value) | Returns MISSING if the value is MISSING .Returns NULL if the value is NULL .Returns FALSE if the value is FALSE .Returns FALSE if the value is 0 or NaN .Returns FALSE if the value is an empty string, array, and object.Return TRUE for all other values. |
TONUMBER(value) | Returns MISSING if the value is MISSING .Returns NULL if the value is NULL .Returns 0 if the value is FALSE .Returns 1 if the value is TRUE .Returns a number value as is. Parses a string value in to a number. Returns NULL for all other values. |
TOOBJECT(value) | Returns MISSING if the value is MISSING .Returns NULL if the value is NULL .Returns an object value as is. Returns an empty object for all other values. |
TOSTRING(value) | Returns MISSING if the value is MISSING .Returns NULL if the value is NULL .Returns "false" if the value is FALSE .Returns "true" if the value is TRUE .Returns a string representation of a number value. Returns a string value as is. Returns NULL for all other values. |
QueryBuilder Differences
SQL++ for Mobile queries support all QueryBuilder
features. See Table 20
for the features supported by SQL++ for Mobile but not by QueryBuilder
.
Category | Components |
---|---|
Conditional Operator | CASE(WHEN ... THEN ... ELSE ...) |
Array Functions | ARRAY_AGG , ARRAY_AVG , ARRAY_COUNT , ARRAY_IFNULL , ARRAY_MAX , ARRAY_MIN , ARRAY_SUM |
Conditional Functions | IFMISSING , IFMISSINGORNULL , IFNULL , MISSINGIF , NULLIF , MATCH , RANK , DIV , IDIV , ROUND_EVEN |
Pattern Matching Functions | REGEXP_CONTAINS , REGEXP_LIKE , REGEXP_POSITION , REGEXP_REPLACE |
Type Checking Functions | ISARRAY , ISATOM , ISBOOLEAN , ISNUMBER , ISOBJECT , ISSTRING , TYPE |
Type Conversion Functions | TOARRAY , TOATOM , TOBOOLEAN , TONUMBER , TOOBJECT , TOSTRING |
Query Parameters
You can provide runtime parameters to your SQL++ query to make it more flexible.
To specify substitutable parameters within your query string prefix the name
with $
— see: Example 51.
final db = await Database.openAsync('hotel');
final query = await db.createQuery(
r'''
SELECT META().id AS docId
FROM hotel
WHERE country = $country
''',
);
query.parameters = Parameters({'country': 'France'});
final resultSet = query.execute();
- Define a parameter placeholder
$country
. - Set the value of the
country
parameter.