SQL++ Query Strings

How to use SQL++ query strings to build effective queries with Kotbase

Note

The examples used in this topic are based on the Travel Sample app and data introduced in the Couchbase Mobile Workshop tutorial.

Introduction

Developers using Kotbase can provide SQL++ query strings using the SQL++ Query API. This API uses query statements of the form shown in Example 2.

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

The database can create a query object with the SQL++ string. See Query Result Sets for how to work with result sets.

Example 1. Running a SQL++ Query

val query = database.createQuery(
    "SELECT META().id AS id FROM _ WHERE type = \"hotel\""
)
return query.execute().use { rs -> rs.allResults() }

We are accessing the current database 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.

Example 2. Query Format

SELECT ____
FROM 'data-source'
WHERE ____,
JOIN ____
GROUP BY ____
ORDER BY ____
LIMIT ____
OFFSET ____

Query Components

Component	Description
SELECT statement	The document properties that will be returned in the result set
FROM	The data source to be queried
WHERE statement	The query criteria The `SELECT`ed properties of documents matching this criteria will be returned in the result set
JOIN statement	The criteria for joining multiple documents
GROUP BY statement	The criteria used to group returned items in the result set
ORDER BY statement	The criteria used to order the items in the result set
LIMIT statement	The maximum number of results to be returned
OFFSET statement	The number of results to be skipped before starting to return results

Tip

We recommend working through the SQL++ Tutorials to build your SQL++ skills.

SELECT statement

Purpose

Projects the result returned by the query, identifying the columns it will contain.

Syntax

Example 3. SQL++ Select Syntax

select = SELECT _ ( DISTINCT | ALL )? selectResult

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 return ALL results (the default).
- The optional DISTINCT argument specifies that the query should remove duplicated results.
selectResults is a list of columns projected in the query result. Each column is an expression which could be a property expression or any expressions or functions. You can use the wildcard * to select all columns — see Select Wildcard.
Use the optional AS argument to provide an alias name for a property. Each property can be aliased by putting the AS <alias name> after the column name.

Select Wildcard

When using the SELECT * option the column name (key) of the SQL++ string 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 Couchbase Server SQL++ — see example in Table 1.

Table 1. Example Column Names for SELECT *

Query	Column Name
`SELECT * AS data FROM _`	`data`
`SELECT * FROM _`	`_`
`SELECT * FROM _default`	`_default`
`SELECT * FROM db`	`db`
`SELECT * FROM db AS store`	`store`

Example

Example 4. SELECT properties

SELECT *

SELECT db.* AS data

SELECT name fullName

SELECT db.name fullName

SELECT DISTINCT address.city

Use the * wildcard to select all properties.
Select all properties from the db data source. Give the object an alias name of data.
Select a pair of properties.
Select a specific property from the db data source.
Select the property item city from its parent property address.

See Query Result Sets for more on processing query results.

FROM

Purpose

Specifies the data source, or sources, and optionally applies an alias (AS). It is mandatory.

Syntax

FROM dataSource
      (optional JOIN joinClause )

Datasource

A datasource can be:

< database-name > : default collection
_ (underscore) : default collection
< scope-name >.< collection-name > : a collection in a scope
< collection-name > : a collection in the default scope

Arguments

Here dataSource is the database name against which the query is to run or the .. Use AS to give the database an alias you can use within the query.
To use the current database, without specifying a name, use _ as the datasource.
JOIN joinclause — use this optional argument to link data sources — see JOIN statement.

Example

Example 5. FROM clause

SELECT name FROM db
SELECT name FROM scope.collection
SELECT store.name FROM db AS store
SELECT store.name FROM db store
SELECT name FROM _
SELECT store.name FROM _ AS store
SELECT store.name FROM _ store

JOIN statement

Purpose

The JOIN clause enables you to select data from multiple data sources linked by criteria specified in the JOIN statement.

Currently only self-joins are supported. For example to combine airline details with route details, linked by the airline id — see Example 6.

Syntax

joinClause = ( join )*

join = joinOperator _ dataSource _  (constraint)?

joinOperator = ( LEFT (OUTER)? | INNER | CROSS )? JOIN

dataSource = databaseName ( ( AS | _ )? databaseAlias )?

constraint ( ON expression )?

Arguments

The join clause starts with a JOIN operator followed by the data source.
Five JOIN operators are supported:
JOIN, LEFT JOIN, LEFT OUTER JOIN, INNER JOIN, and CROSS JOIN. Note: JOIN and INNER JOIN are the same, LEFT JOIN and LEFT OUTER JOIN are the same.
The join constraint starts with the ON keyword followed by the expression that defines the joining constraints.

Example

SELECT db.prop1, other.prop2 FROM db JOIN db AS other ON db.key = other.key

SELECT db.prop1, other.prop2 FROM db LEFT JOIN db other ON db.key = other.key

SELECT * FROM route r JOIN airline a ON r.airlineid = meta(a).id WHERE a.country = "France"

Example 6. Using JOIN to Combine Document Details

This example JOINS the document of type route with documents of type airline using the document ID (_id) on the airline document and airlineid on the route document.

SELECT * FROM travel-sample r JOIN travel-sample a ON r.airlineid = a.meta.id WHERE a.country = "France"

Array UNNEST

Purpose

You can use UNNEST in queries to unpack arrays within a document into individual rows. This functionality makes it possible to join them with its parent object in the query.

UNNEST is used within the FROM clause and can be chained to perform multi-level UNNEST.

You can also use a new type of index, the Array Index, to allow querying with UNNEST more efficiently.

Note

Couchbase Lite currently supports inner UNNEST only.

Syntax

The syntax for UNNEST is shown below:

unnestClause = UNNEST expr ( ‘AS’? alias)?

Caution

"unnest" will be defined as a new keyword in the SQL++ syntax. You cannot use the term as an identifier for a property name or data source unless you escape it using backticks.

Examples

For examples of using Array Indexes in conjunction with UNNEST, see Array Index.

We are also accessing the current database using the shorthand notation _ — see the FROM clause for more on data source selection and Query Parameters for more on parameterized queries.

The following examples will use the example JSON document below to query results from:

{
  "Name": "Sam",
  "contacts": [
    {
      "type": "primary",
      "address": { "street": "1 St", "city": "San Pedro", "state": "CA" },
      "phones": [
        { "type": "home", "number": "310-123-4567" },
        { "type": "mobile", "number": "310-123-6789" }
      ]
    },
    {
      "type": "secondary",
      "address": { "street": "5 St", "city": "Seattle", "state": "WA" },
      "phones": [
        { "type": "home", "number": "206-123-4567" },
        { "type": "mobile", "number": "206-123-6789" }
      ]
    }
  ],
  "likes": ["soccer", "travel"]
}

Using the document above we can perform queries on a single nested array like so:

SELECT name, interest FROM _ UNNEST likes as interest WHERE interest = "travel"

The query above will produce the following output from the document:

{ "name": "Sam", "like": "travel" }

You can perform similar operations on nested arrays:

SELECT name, contact.type, phone.number
FROM profiles
UNNEST contacts as contact
UNNEST contact.phones as phone
WHERE phone.type = "mobile"

The query above will then produce the following output:

{ "name": "Sam", "type": "primary", "number": "310-123-6789" }
{ "name": "Sam", "type": "secondary", "number": "206-123-6789" }

The output demonstrates retrieval of both primary and secondary contact numbers listed as type "mobile".

Important

Array literals are not supported in CBL 3.2. Attempting to create a query with array literals will return an error.

WHERE statement

Purpose

Specifies the selection criteria used to filter results.

As with SQL, use the WHERE statement to choose which documents are returned by your query.

Syntax

where = WHERE expression

Arguments

WHERE evaluates expression to a BOOLEAN value. You can chain any number of expressions in order to implement sophisticated filtering capabilities.

See also — Operators for more on building expressions and Query Parameters for more on parameterized queries.

Examples

SELECT name FROM db WHERE department = 'engineer' AND group = 'mobile'

GROUP BY statement

Purpose

Use GROUP BY to arrange values in groups of one or more properties.

Syntax

groupBy = grouping _( having )?

grouping = GROUP BY expression( _ ',' _ expression )*

having = HAVING expression

Arguments

The group by clause starts with the GROUP BY keyword followed by one or more expressions.
grouping — the group by clause is normally used together with the aggregate functions (e.g. COUNT, MAX, MIN, SUM, AVG).
having — allows you to filter the result based on aggregate functions — for example, HAVING count(empnum)>100.

Examples

SELECT COUNT(empno), city FROM db GROUP BY city

SELECT COUNT(empno), city FROM db GROUP BY city HAVING COUNT(empno) > 100

SELECT COUNT(empno), city FROM db GROUP BY city HAVING COUNT(empno) > 100 WHERE state = 'CA'

ORDER BY statement

Purpose

Sort query results based on a given expression result.

Syntax

orderBy = ORDER BY ordering ( _ ',' _ ordering )*

ordering = expression ( _ order )?

order = ( ASC / DESC )

Arguments

orderBy — The order by clause starts with the ORDER BY keyword followed by the ordering clause.
ordering — The ordering clause specifies the properties or expressions to use for ordering the results.
order — In each ordering clause, the sorting direction is specified using the optional ASC (ascending) or DESC (descending) directives. Default is ASC.

Examples

Example 7. Simple usage

SELECT name FROM db  ORDER BY name

SELECT name FROM db  ORDER BY name DESC

SELECT name, score FROM db  ORDER BY name ASC, score DESC

LIMIT statement

Purpose

Specifies the maximum number of results to be returned by the query.

Syntax

limit = LIMIT expression

Arguments

The limit clause starts with the LIMIT keyword followed by an expression that will be evaluated as a number.

Examples

Example 8. Simple usage

SELECT name FROM db LIMIT 10

Return only 10 results

OFFSET statement

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 ignored before the query begins returning results.

Examples

Example 9. Simple usage

SELECT name FROM db OFFSET 10

SELECT name FROM db  LIMIT 10 OFFSET 10

Ignore first 10 results
Ignore first 10 results then return the next 10 results

Expressions

Expressions are references to identifiers that resolve to values. Categories of expression comprise the elements covered in this section (see above), together with Operators and Functions, which are covered in their own sections.

Literals

Boolean

Purpose

Represents a true or false value.

Syntax

TRUE | FALSE

Example

SELECT value FROM db  WHERE value = true
SELECT value FROM db  WHERE value = false

Numeric

Purpose

Represents a numeric value. Numbers may be signed or unsigned digits. They have optional fractional and exponent components.

Syntax

'-'? (('.' DIGIT+) | (DIGIT+ ('.' DIGIT*)?)) ( [Ee] [-+]? DIGIT+ )? WB

DIGIT = [0-9]

Example

SELECT value FROM db  WHERE value = 10
SELECT value FROM db  WHERE value = 0
SELECT value FROM db WHERE value = -10
SELECT value FROM db WHERE value = 10.25
SELECT value FROM db WHERE value = 10.25e2
SELECT value FROM db WHERE value = 10.25E2
SELECT value FROM db WHERE value = 10.25E+2
SELECT value FROM db WHERE value = 10.25E-2

String

Purpose

The string literal represents a string or sequence of characters.

Syntax

"characters" | 'characters'

The string literal can be double-quoted as well as single-quoted.

Example

SELECT firstName, lastName FROM db WHERE middleName = "middle"
SELECT firstName, lastName FROM db WHERE middleName = 'middle'

NULL

Purpose

The literal NULL represents an empty value.

Syntax

NULL

Example

SELECT firstName, lastName FROM db WHERE middleName IS NULL

MISSING

Purpose

The MISSING literal represents a missing name-value pair in a document.

Syntax

MISSING

Example

SELECT firstName, lastName FROM db WHERE middleName IS MISSING

Array

Purpose

Represents an Array.

Syntax

arrayLiteral = '[' _ (expression ( _ ',' _ e2:expression )* )? ']'

Example

SELECT ["a", "b", "c"] FROM _
SELECT [ property1, property2, property3] FROM _

Dictionary

Purpose

Represents a dictionary literal.

Syntax

dictionaryLiteral = '{' _ ( STRING_LITERAL ':' e:expression
  ( _ ',' _ STRING_LITERAL ':' _ expression )* )?
   '}'

Example

SELECT { 'name': 'James', 'department': 10 } FROM db
SELECT { 'name': 'James', 'department': dept } FROM db
SELECT { 'name': 'James', 'phones': ['650-100-1000', '650-100-2000'] } FROM db

Identifiers

Purpose

Identifiers provide symbolic references. Use them for example to identify: column alias names, database names, database alias names, property names, parameter names, function names, and FTS index names.

Syntax

<[a-zA-Z_] [a-zA-Z0-9_$]*> _ | "`" ( [^`] | "``"   )* "`"  _

The identifier allows a-z, A-Z, 0-9, _ (underscore), and $ character.
The identifier is case-sensitive.

Tip

To use other characters in the identifier, surround the identifier with the backtick ` character.

Example

Example 10. Identifiers

SELECT * FROM _

SELECT * FROM `db-1`

SELECT key FROM db

SELECT key$1 FROM db_1

SELECT `key-1` FROM db

Use of backticks allows a hyphen as part of the identifier name.

Property Expressions

Purpose

The property expression is used to reference a property in a document.

Syntax

property = '*'| dataSourceName '.' _ '*'  | propertyPath

propertyPath = propertyName (
    ('.' _ propertyName ) |
    ('[' _ INT_LITERAL _ ']' _  )
    )*

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 the SELECT clause.

Example

Example 11. Property Expressions

SELECT *
  FROM db
  WHERE contact.name = "daniel"

SELECT db.*
  FROM db
  WHERE collection.contact.name = "daniel"

SELECT collection.contact.address.city
  FROM scope.collection
  WHERE collection.contact.name = "daniel"

SELECT contact.address.city
  FROM scope.collection
  WHERE contact.name = "daniel"

SELECT contact.address.city, contact.phones[0]
  FROM db
  WHERE contact.name = "daniel"

Any and Every Expressions

Purpose

Evaluates expressions over items in an array object.

Syntax

arrayExpression = 
  anyEvery _ variableName 
     _ IN  _ expression 
       _ SATISFIES _ expression 
    END 

anyEvery = anyOrSome AND EVERY | anyOrSome | EVERY

anyOrSome = ANY | SOME

The array expression starts with ANY/SOME, EVERY, or ANY/SOME AND EVERY, each of which has a different function as described below, and is terminated by END
- ANY/SOME: Returns TRUE if at least one item in the array satisfies the expression, otherwise returns FALSE.
  NOTE: ANY and SOME are interchangeable.
- EVERY: Returns TRUE if all items in the array satisfies the expression, otherwise return FALSE. If the array is empty, returns TRUE.
- ANY/SOME AND EVERY: Same as EVERY but returns FALSE if the array is empty.
The variable name represents each item in the array.
The IN keyword is used for specifying the array to be evaluated.
The SATISFIES keyword is used for evaluating each item in the array.
END terminates the array expression.

Example

Example 12. ALL and Every Expressions

SELECT name
  FROM db
  WHERE ANY v
          IN contacts
          SATISFIES v.city = 'San Mateo'
        END

Parameter Expressions

Purpose

Parameter expressions specify a value to be assigned from the parameter map presented when executing the query.

Note

If parameters are specified in the query string, but the parameter and value mapping is not specified in the query object, an error will be thrown when executing the query.

Syntax

$IDENTIFIER

Examples

Example 13. Parameter Expression

SELECT name
  FROM db
  WHERE department = $department

Example 14. Using a Parameter

val query = database.createQuery($$"SELECT name WHERE department = $department")
query.parameters = Parameters().setValue("department", "E001")
val result = query.execute()

The query resolves to SELECT name WHERE department = "E001"

Parenthesis Expressions

Purpose

Use parentheses to group expressions together to make them more readable or to establish operator precedences.

Example

Example 15. Parenthesis Expression

-- Establish the desired operator precedence; do the addition before the multiplication
SELECT (value1 + value2) * value 3
  FROM db

SELECT *
  FROM db
  WHERE ((value1 + value2) * value3) + value4 = 10

SELECT *
  FROM db
  -- Clarify the conditional grouping
  WHERE (value1 = value2)
     OR (value3 = value4)

Operators

In this section
Binary Operators | Unary Operators | COLLATE Operators | CONDITIONAL Operator

Binary Operators

Maths | Comparison Operators | Logical Operators | String Operator

Maths

Table 2. Maths Operators

Op	Desc	Example
`+`	Add	`WHERE v1 + v2 = 10`
`-`	Subtract	`WHERE v1 - v2 = 10`
`*`	Multiply	`WHERE v1 * v2 = 10`
`/`	Divide — see note ¹	`WHERE v1 / v2 = 10`
`%`	Modulo	`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 Server SQL++, which performs float division regardless. Use DIV(x, y) to force float division in CBL SQL++.

Comparison Operators

Purpose

The comparison operators are used in the WHERE statement to specify the condition on which to match documents.

Table 3. Comparison Operators

Op	Desc	Example
`=` or `==`	Equals	`WHERE v1 = v2` `WHERE v1 == v2`
`!=` or `<>`	Not Equal to	`WHERE v1 != v2` `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. Two wildcards 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 see Full Text Search Functions	`WHERE v1-index MATCH "value"`
`BETWEEN`	Logically equivalent to `v1>=X and v1<=Y`	`WHERE v1 BETWEEN 10 and 100`
`IS NULL` ³	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`	`IS NOT NULL AND MISSING`	`WHERE v1 IS VALUED`
`IS NOT VALUED`	`IS NULL OR MISSING`	`WHERE v1 IS NOT VALUED`

² Matching is case-insensitive for ASCII characters, case-sensitive for non-ASCII.

³ Use of IS and IS NOT is limited to comparing NULL and MISSING values (this encompasses VALUED). This is different from QueryBuilder, in which they operate as equivalents of == and !=.

Table 4. Comparing NULL and MISSING values using IS

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 is TRUE, and FALSE is FALSE
Numbers 0 or 0.0 are FALSE
Arrays and dictionaries are FALSE
String and Blob are TRUE if the values are casted as a non-zero or FALSE if the values are casted as 0 or 0.0
NULL is FALSE
MISSING is MISSING

Note

This is different from Server SQL++, where:

MISSING, NULL and FALSE are FALSE
Numbers 0 is FALSE
Empty strings, arrays, and objects are FALSE
All other values are TRUE

Tip

Use TOBOOLEAN(expr) function to convert a value based on Server SQL++ boolean value rules.

Table 5. Logical Operators

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"`

Table 6. Logical Operation Table

a	b	a AND b	a OR b
TRUE	TRUE	TRUE	TRUE
	FALSE	FALSE	TRUE
	NULL	FALSE ⁵⁻¹	TRUE
	MISSING	MISSING	TRUE
FALSE	TRUE	FALSE	TRUE
	FALSE	FALSE	FALSE
	NULL	FALSE	FALSE ⁵⁻¹
	MISSING	FALSE	MISSING
NULL	TRUE	FALSE ⁵⁻¹	TRUE
	FALSE	FALSE	FALSE ⁵⁻¹
	NULL	FALSE ⁵⁻¹	FALSE ⁵⁻¹
	MISSING	FALSE ⁵⁻²	MISSING ⁵⁻³
MISSING	TRUE	MISSING	TRUE
	FALSE	FALSE	MISSING
	NULL	FALSE ⁵⁻²	MISSING ⁵⁻³
	MISSING	MISSING	MISSING

Note

This differs from Server SQL++ in the following instances:
⁵⁻¹ Server will return: NULL instead of FALSE
⁵⁻² Server will return: MISSING instead of FALSE
⁵⁻³ Server will return: NULL instead of MISSING

String Operator

Purpose

A single string operator is provided. It enables string concatenation.

Table 7. String Operators

Op	Description	Example
`\|\|`	Concatenating	`SELECT firstnm \|\| lastnm 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

// UNARY_OP _ expr

Table 8. Unary Operators

Op	Description	Example
`+`	Positive value	`WHERE v1 = +10`
`-`	Negative value	`WHERE v1 = -10`
`NOT`	Logical Negate operator ^*	`WHERE "James" NOT IN contactsList`

^* The NOT operator is often used in conjunction with operators such as IN, LIKE, MATCH, and BETWEEN operators.
NOT operation on NULL value returns NULL.
NOT operation on MISSING value returns MISSING.

Table 9. NOT Operation TABLE

a	NOT a
TRUE	FALSE
FALSE	TRUE
NULL	FALSE
MISSING	MISSING

COLLATE Operators

Purpose

Collate operators specify how the 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.

Note

Collate is not supported by Server SQL++

Syntax

collate = COLLATE collation | '(' collation (_ collation )* ')'

collation = NO? (UNICODE | CASE | DIAC) WB

Arguments

The available collation options are:

UNICODE: Conduct a Unicode comparison; the default is to do ASCII comparison.
CASE: Conduct case-sensitive comparison.
DIAC: Take account of accents and diacritics in the comparison; on by default.
NO: This can be used as a prefix to the other collations, to disable them (for example: NOCASE to enable case-insensitive comparison)

Example

SELECT department FROM db WHERE (name = "fred") COLLATE UNICODE

SELECT department FROM db WHERE (name = "fred")
COLLATE (UNICODE)

SELECT department FROM db WHERE (name = "fred") COLLATE (UNICODE CASE)

SELECT name FROM db ORDER BY name COLLATE (UNICODE DIAC)

CONDITIONAL Operator

Purpose

The Conditional (or CASE) operator evaluates conditional logic in a similar way to the IF/ELSE operator.

Syntax

CASE (expression) (WHEN expression THEN expression)+ (ELSE expression)? END

CASE (expression)? (!WHEN 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 first WHEN expression, the result is the THEN expression.
- Otherwise, any subsequent WHEN clauses are evaluated in the same way.
- If no match is found, the result of the CASE expression is the ELSE expression, NULL if no ELSE expression was provided.
Searched Case Expression
- If the first WHEN expression is TRUE, the result of this expression is its THEN expression.
- Otherwise, subsequent WHEN clauses are evaluated in the same way. If no WHEN clause evaluate to TRUE, then the result of the expression is the ELSE expression, or NULL if no ELSE expression was provided.

Example

Example 16. Simple Case

SELECT CASE state WHEN ‘CA’ THEN ‘Local’ ELSE ‘Non-Local’ END FROM DB

Example 17. Searched Case

SELECT CASE WHEN shippedOn IS NOT NULL THEN ‘SHIPPED’ ELSE "NOT-SHIPPED" END FROM db

Functions

Purpose

Functions are also expressions.

Syntax

The function syntax is the same as Java’s method syntax. It starts with the function name, followed by optional arguments inside parentheses.

function = functionName parenExprs

functionName  = IDENTIFIER

parenExprs = '(' ( expression (_ ',' _ expression )* )? ')'

Aggregation Functions

Table 10. Aggregation Functions

Function	Description
`AVG(expr)`	Returns average value of the number values in the group
`COUNT(expr)`	Returns a count of all values in the group
`MIN(expr)`	Returns the minimum value in the group
`MAX(expr)`	Returns the maximum value in the group
`SUM(expr)`	Returns the sum of all number values in the group

Array Functions

Table 11. Array Functions

Function	Description
`ARRAY_AGG(expr)`	Returns an array of the non-MISSING group values in the input expression, including NULL values.
`ARRAY_AVG(expr)`	Returns the average of all non-NULL number values in the array; or NULL if there are none
`ARRAY_CONTAINS(expr)`	Returns TRUE if the value exists in the array; otherwise FALSE
`ARRAY_COUNT(expr)`	Returns the number of non-null values in the array
`ARRAY_IFNULL(expr)`	Returns the first non-null value in the array
`ARRAY_MAX(expr)`	Returns the largest non-NULL, non_MISSING value in the array
`ARRAY_MIN(expr)`	Returns the smallest non-NULL, non_MISSING value in the array
`ARRAY_LENGTH(expr)`	Returns the length of the array
`ARRAY_SUM(expr)`	Returns the sum of all non-NULL numeric value in the array

Conditional Functions

Table 12. Conditional Functions

Function	Description
`IFMISSING(expr1, expr2, …)`	Returns the first non-MISSING value, or NULL if all values are MISSING
`IFMISSINGRONULL(expr1, expr2, …)`	Returns the first non-NULL and non-MISSING value, or NULL if all values are NULL or MISSING
`IFNULL(expr1, expr2, …)`	Returns the first non-NULL, or NULL if all values are NULL
`MISSINGIF(expr1, expr2)`	Returns MISSING when expr1 = expr2; otherwise returns expr1. Returns MISSING if either or both expressions are MISSING. Returns NULL if either or both expressions are NULL.+
`NULLF(expr1, expr2)`	Returns NULL when expr1 = expr2; otherwise returns expr1. Returns MISSING if either or both expressions are MISSING. Returns NULL if either or both expressions are NULL.+

Date and Time Functions

Table 13. Date and Time Functions

Function	Arguments	Return Value
`STR_TO_MILLIS(date1)` Coverts a date string to Epoch/UNIX milliseconds.	`date1` - A valid date string.	Returns an integer containing the converted date string into Epoch/UNIX milliseconds.
`STR_TO_UTC(date1)` Converts a date string into the equivalent date in UTC.	`date1` - A valid date string.	Returns a date string representing the date string converted to UTC. The output date format follows the date format of the input date. Returns `null` if an invalid date format is provided.
`STR_TO_TZ(date1, tz)` Converts a date string to it’s equivalent in the specified timezone.	`date1` - A valid date string. This is converted to UTC. `tz` - An integer that represents minutes offset from UTC. For example, `UTC-5` would be represented as `-300`.	Returns a date string representing the date string converted to the specified timezone. Returns `null` if an invalid date format is provided.
`MILLIS_TO_STR(date1)` Converts an Epoch/UNIX timestamp into the specified date string format.	`date1` - An integer representing an Epoch/UNIX timestamp in millseconds.	Returns a date string representing the local date. Returns `null` if an invalid timestamp is provided.
`MILLIS_TO_UTC(date1)` Converts an Epoch/UNIX timestamp into a local time date string.	`date1` - An integer representing an Epoch/UNIX timestamp in millseconds.	Returns a date string representing the date in UTC. Returns `null` if an invalid timestamp is provided.
`MILLIS_TO_TZ(date1,tz, [fmt])` Converts an Epoch/UNIX timestamp into the specified time zone in the specified date string format.	`date1` - An integer representing an Epoch/UNIX timestamp in milliseconds. `tz` - An integer that represents minutes offset from UTC. For example, `UTC-5` would be represented as `-300`. `fmt` - An optional string parameter representing a date format to output the result as.	Returns a date string representing the date in the specified timezone in the specified format. If `fmt` is not specified, the output default to the combined full date and time.
`DATE_DIFF_STR(date1, date2, part)` Finds the elapsed time between two date strings. This is measured from `date2` to `date1`.	`date1` - A valid date string. This is converted to UTC. `date2` - A valid date string. This is converted to UTC. `part` - A string representing the date component units to return.	Returns an integer representing the elapsed time measured from `date2` to `date1` (in units based on the specified `part`) between both dates. The value is positive if `date1` is greater than `date2`, negative otherwise. Returns `null` if any of the parameters are invalid.
`DATE_DIFF_MILLIS(date1, date2, part)` Finds the elapsed time between two Epoch/UNIX timestamps.	`date1` - An integer representing an Epoch/UNIX timestamp in milliseconds. `date2` - An integer representing an Epoch/UNIX timestamp in milliseconds. `part` - A string representing the date component units to return.	Returns an integer representing the elapsed time measured from `date2` to `date1` (in units based on the specified `part`) between both dates. The value is positive if `date1` is greater than `date2`, negative otherwise. Returns `null` if any of the parameters are invalid.
`DATE_ADD_STR(date1, n, part)` Performs date arithmetic on a date string. For example `DATE_ADD_STR("2024-03-20T15:43:01+0000", 3, "day")` adds 3 days to the provided date.	`date1` - A valid date string. This is converted to UTC. `n` - An integer or expression that evaluates to an integer. A positive value will increment the date component whereas a negative value will decrement the date component. `part` - A string representing the component of the date to increment.	Returns an integer representing the calculation result as an Epoch/UNIX timestamp in milliseconds. Returns `null` if any of the parameters are invalid.
`DATE_ADD_MILLIS(date1, n, part)` Performs date arithmetic on a particular component of an Epoch/UNIX timestamp value. For example `DATE_ADD_STR(1710946158819, 3, 'day')` adds 3 days to the provided date.	`date1` - An integer representing an Epoch/UNIX timestamp in milliseconds. `n` - An integer or expression that evaluates to an integer. A positive value will increment the date component whereas a negative value will decrement the date component. `part` - A string representing the component of the date to increment.	Returns an integer representing the calculation result as an Epoch/UNIX timestamp in milliseconds. Returns `null` if any of the parameters are invalid.

Full Text Search Functions

Table 14. FTS Functions

Function	Description	Example
`MATCH(indexName, term)`	Returns `TRUE` if `term` expression matches the FTS indexed term. `indexName` identifies the FTS index, `term` expression to search for matching.	`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

Table 15. Maths Functions

Function	Description
`ABS(expr)`	Returns the absolute value of a number.
`ACOS(expr)`	Returns the arc cosine in radians.
`ASIN(expr)`	Returns the arcsine in radians.
`ATAN(expr)`	Returns the arctangent in radians.
`ATAN2(expr1,expr2)`	Returns the arctangent of expr1/expr2.
`CEIL(expr)`	Returns the smallest integer not less than the number.
`COS(expr)`	Returns the cosine value of the expression.
`DIV(expr1, expr2)`	Returns float division of expr1 and expr2. Both expr1 and expr2 are cast to a double number before division. The returned result is always a double.
`DEGREES(expr)`	Converts radians to degrees.
`E()`	Returns base of natural logarithms.
`EXP(expr)`	Returns expr value
`FLOOR(expr)`	Returns largest integer not greater than the number.
`IDIV(expr1, expr2)`	Returns integer division of expr1 and expr2.
`LN(expr)`	Returns log base e value.
`LOG(expr)`	Returns log base 10 value.
`PI()`	Return PI value.
`POWER(expr1, expr2)`	Returns expr1expr2 value.
`RADIANS(expr)`	Returns degrees to radians.
`ROUND(expr (, digits_expr)?)`	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(expr (, digits_expr)?)`	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(expr)`	Returns -1 for negative, 0 for zero, and 1 for positive numbers.
`SIN(expr)`	Returns sine value.
`SQRT(expr)`	Returns square root value.
`TAN(expr)`	Returns tangent value.
`TRUNC (expr (, digits, expr)?)`	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 Server SQL++ ROUND(), which rounds the midpoint values using Rounding to Nearest Even convention.

Metadata Functions

Table 16. Metadata Functions

Function	Description	Example
`META(dataSourceName?)`	Returns a dictionary containing metadata properties including: id : document identifier sequence : document mutating sequence number deleted : flag indicating whether document is deleted or not expiration : document expiration date in timestamp format The optional dataSourceName identifies the database or the database alias name. To access a specific metadata property, use the dot expression.	`SELECT META() FROM db` `SELECT META().id, META().sequence, META().deleted, META().expiration FROM db` `SELECT p.name, r.rating FROM product as p INNER JOIN reviews AS r ON META(r).id IN p.reviewList WHERE META(p).id = "product320"`

Pattern Searching Functions

Table 17. Pattern Searching Functions

Function	Description
`REGEXP_CONTAINS(expr, pattern)`	Returns TRUE if the string value contains any sequence that matches the regular expression pattern.
`REGEXP_LIKE(expr, pattern)`	Return TRUE if the string value exactly matches the regular expression pattern.
`REGEXP_POSITION(expr, pattern)`	Returns the first position of the occurrence of the regular expression pattern within the input string expression. Return -1 if no match is found. Position counting starts from zero.
`REGEXP_REPLACE(expr, pattern, repl [, n])`	Returns 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

Table 18. String Functions

Function	Description
`CONTAINS(expr, substring_expr)`	Returns true if the substring exists within the input string, otherwise returns false.
`LENGTH(expr)`	Returns the length of a string. The length is defined as the number of characters within the string.
`LOWER(expr)`	Returns the lowercase string of the input string.
`LTRIM(expr)`	Returns the string with all leading whitespace characters removed.
`RTRIM(expr)`	Returns the string with all trailing whitespace characters removed.
`TRIM(expr)`	Returns the string with all leading and trailing whitespace characters removed.
`UPPER(expr)`	Returns the uppercase string of the input string.

Type Checking Functions

Table 19. Type Checking Functions

Function	Description
`ISARRAY(expr)`	Returns TRUE if expression is an array, otherwise returns MISSING, NULL or FALSE.
`ISATOM(expr)`	Returns TRUE if expression is a Boolean, number, or string, otherwise returns MISSING, NULL or FALSE.
`ISBOOLEAN(expr)`	Returns TRUE if expression is a Boolean, otherwise returns MISSING, NULL or FALSE.
`ISNUMBER(expr)`	Returns TRUE if expression is a number, otherwise returns MISSING, NULL or FALSE.
`ISOBJECT(expr)`	Returns TRUE if expression is an object (dictionary), otherwise returns MISSING, NULL or FALSE.
`ISSTRING(expr)`	Returns TRUE if expression is a string, otherwise returns MISSING, NULL or FALSE.
`TYPE(expr)`	Returns one of the following strings, based on the value of expression: “missing” “null” “boolean” “number” “string” “array” “object” “binary”

Type Conversion Functions

Table 20. Type Conversion Functions

Function	Description
`TOARRAY(expr)`	Returns MISSING if the value is MISSING. Returns NULL if the value is NULL. Returns the array itself. Returns all other values wrapped in an array.
`TOATOM(expr)`	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 boolean, numbers, or strings Returns NULL for all other values.
`TOBOOLEAN(expr)`	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(expr)`	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 NUMBER if the value is NUMBER. Returns NUMBER parsed from the string value. Returns NULL for all other values.
`TOOBJECT(expr)`	Returns MISSING if the value is MISSING. Returns NULL if the value is NULL. Returns the object if the value is an object. Returns an empty object for all other values.
`TOSTRING(expr)`	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 NUMBER in String if the value is NUMBER. Returns the string value if the value is a string. Returns NULL for all other values.

QueryBuilder Differences

Couchbase Lite SQL++ Query supports all QueryBuilder features, except Predictive Query and Index. See Table 21 for the features supported by SQL++ but not by QueryBuilder.

Table 21. QueryBuilder Differences

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`
Math Functions	`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 $, $type — see Example 18.

Example 18. Running a SQL++ Query

val query = database.createQuery(
    $$"SELECT META().id AS id FROM _ WHERE type = $type"
) 

query.parameters = Parameters().setString("type", "hotel") 

return query.execute().allResults()

Define a parameter placeholder $type
Set the value of the $type parameter