Miscellaneous Utility Functions

Capella Operational

reference

Miscellaneous utility functions enable you to perform tasks beyond the usual evaluation and transformation of data. For example, there are functions to retrieve information about a document or item, perform base64 encoding and decoding, generate UUIDs, and control the flow of a query.

ABORT(`expression`)

Description

Generates an error. The error message contains the text specified by the given expression.

This function is useful for flow control when creating inline user-defined functions. You can use conditional operators to check the parameters, and use this function to generate an error if something is wrong.

Arguments

expression: An expression resolving to a string.

Return Value

The function does not return a return value.

If this function is executed in a query, it causes the query to halt with an error code 5011. The error message contains the text specified by the given expression.

When this function is executed by a user-defined function, it causes the query to halt with an error code 10109. The error message shows the name of the user-defined function and contains the text specified by the given expression.

Examples

Abort a query

Query

SELECT ABORT("Something went wrong");

Result

[
  {
    "code": 5011,
    "msg": "Abort: \"Something went wrong\". - cause: \"Something went wrong\""
  }
]

User-defined function with error checking

Function

CREATE OR REPLACE FUNCTION rstr(vString, vLen) LANGUAGE INLINE AS
CASE
  WHEN NOT IS_STRING(vString)
    THEN ABORT("Search string is not a string")
  WHEN NOT IS_NUMBER(vLen)
    THEN ABORT("Substring length is not a number")
  WHEN vLen > LENGTH(vString)
    THEN ABORT("Substring longer than search string")
  ELSE SUBSTR(vString, LENGTH(vString) - vLen, vLen)
END;

Test invalid string argument

EXECUTE FUNCTION rstr(100, 4);

Result

[
  {
    "code": 10109,
    "msg": "Error executing function rstr : \"Search string is not a string\" - cause: \"Search string is not a string\""
  }
]

Test invalid number argument

EXECUTE FUNCTION rstr("Couchbase", "foo");

Result

[
  {
    "code": 10109,
    "msg": "Error executing function rstr : \"Substring length is not a number\" - cause: \"Substring length is not a number\""
  }
]

Test out-of-range value

EXECUTE FUNCTION rstr("Couchbase", 10);

Result

[
  {
    "code": 10109,
    "msg": "Error executing function rstr : \"Substring longer than search string\" - cause: \"Substring longer than search string\""
  }
]

Test with valid arguments

EXECUTE FUNCTION rstr("Couchbase", 4);

Result

[
  "base"
]

BASE64(`expression`)

Alias: BASE64_ENCODE()

Description

Returns the base64 encoding of the given expression.

Arguments

expression: An expression representing any supported SQL++ datatype.

Return Value

A string representing the base64 encoding of the input expression. If the input expression is missing, the return value is also missing.

Example

Query

SELECT BASE64([1, 2, 3, 4]) AS `array`,
       BASE64(false) AS `boolean`,
       BASE64(missing) AS `missing`,
       BASE64(null) AS `null`,
       BASE64(1234) AS `number`,
       BASE64( {"a": 1, "b": 2, "c": [1, 2, 3]} ) AS `object`,
       BASE64("Couchbase") AS `string`;

Result

[
  {
    "array": "WzEsMiwzLDRd",
    "boolean": "ZmFsc2U=",
    "null": "bnVsbA==",
    "number": "MTIzNA==",
    "object": "eyJhIjoxLCJiIjoyLCJjIjpbMSwyLDNdfQ==",
    "string": "IkNvdWNoYmFzZSI="
  }
]

BASE64_ENCODE(`expression`)

Alias of BASE64().

BASE64_DECODE(`expression`)

Description

Reverses the encoding done by the BASE64() or BASE64_ENCODE() functions.

Arguments

expression: An expression representing a valid base64-encoded string.

Return Value

The decoded value of the input expression. If the input expression is missing, the return value is also missing.

Example

Query

SELECT BASE64_DECODE("WzEsMiwzLDRd") AS `array`,
       BASE64_DECODE("ZmFsc2U=") AS `boolean`,
       BASE64_DECODE(missing) AS `missing`,
       BASE64_DECODE("bnVsbA==") AS `null`,
       BASE64_DECODE("MTIzNA==") AS `number`,
       BASE64_DECODE("eyJhIjoxLCJiIjoyLCJjIjpbMSwyLDNdfQ==") AS `object`,
       BASE64_DECODE("IkNvdWNoYmFzZSI=") AS `string`;

Result

[
  {
    "array": [
      1,
      2,
      3,
      4
    ],
    "boolean": false,
    "null": null,
    "number": 1234,
    "object": {
      "a": 1,
      "b": 2,
      "c": [
        1,
        2,
        3
      ]
    },
    "string": "Couchbase"
  }
]

CURRENT_USERS()

Description

Returns the authenticated users for the current statement.

Arguments

None.

Return Value

An array of strings, each representing a user name.

Example

Query

SELECT CURRENT_USERS() as current_users;

Results

[
  {
    "current_users": [
      "local:<redacted-UUID>"
    ]
  }
]

DS_VERSION()

Description

Returns the Couchbase Server version.

Arguments

None.

Return Value

Returns string containing the Couchbase Server version.

Example

Query

SELECT DS_VERSION() as server_version;

Results

[
  {
    "server_version": "7.6.0-1886-enterprise"
  }
]

FLATTEN_KEYS(`expr1` [ `modifiers` ], `expr2` [ `modifiers` ], …)

Description

This function can only be used when defining an index key for an array index.

If you need to index multiple fields within an array, this function enables you to flatten the specified expressions, and index them as if they were separate index keys. All subsequent index keys are accordingly moved to the right. Queries will be sargable and will generate spans.

Arguments

expr1, expr2, …: [At least 1 and at most 32 argument-values are required] Each argument is an expression over a field within an array, which constitutes an array index key.
modifiers: [Optional] Arguments can be modified with ASC or DESC to specify the sort order of the index key. If this modifier is omitted, the default sort order is ASC.

The first argument may be also modified with IGNORE MISSING. This modifier may only be used when the function is being used in the definition of the leading index key. If this modifier is present, documents which do not contain the specified field are indexed anyway. If this modifier is omitted, documents which do not contain the specified field are not indexed.

When the IGNORE MISSING modifier and the ASC or DESC modifier are used together, the order of the modifiers does not matter.

Note that FLATTEN_KEYS() cannot be used recursively.

Return Value

The return value is a flattened list of array elements for use in an array index key.

Examples

For examples, refer to Array Indexing Examples.

FORMALIZE(`statement` [ `,query_context` ])

Description

Fully expands all references within a query, using the specified query context.

This function has a synonym FORMALISE().

Arguments

statement: A string containing the statement to formalize.
query_context: [ Optional ] A string query context value for the function to use when formalizing.

Return Value

Returns a query with all references fully specified.

Examples

Formalize a query

Query

SELECT formalize("SELECT * FROM landmark WHERE country = 'United Kingdom'","default:`travel-sample`.inventory")

Results

[
  {
    "$1": "select self.* from `default`:`travel-sample`.`inventory`.`landmark` where ((`landmark`.`country`) = \"United Kingdom\")"
  }
]

Formalize recently completed requests

Query

SELECT statement,
       NVL(queryContext,"") AS queryContext,
       formalize(statement, queryContext) AS formalized
FROM system:completed_requests;

Results

[
  {
      "statement": "select * from `travel-sample`.inventory.landmark where country = 'United Kingdom' limit 1;",
      "queryContext": "",
      "formalized": "select self.* from `default`:`travel-sample`.`inventory`.`landmark` where ((`landmark`.`country`) = \"United Kingdom\") limit 1"
  },
  {
      "statement": "select * from landmark where country = 'United Kingdom' limit 1;",
      "queryContext": "`travel-sample`.inventory",
      "formalized": "select self.* from `default`:`travel-sample`.`inventory`.`landmark` where ((`landmark`.`country`) = \"United Kingdom\") limit 1"
  },
  // ...
]

LEN(`expression`)

Description

A general function to return the length of an item.

Arguments

expression: An expression representing any supported SQL++ datatype.

Return Value

The return value is usually a number, depending on the datatype of the input expression.

Input Expression Return Value

Input Expression	Return Value
String	The number of code points in the string — equivalent to LENGTH().
Object	The field count — equivalent to OBJECT_LENGTH().
Array	The number of elements — equivalent to ARRAY_LENGTH().
Binary	The size of the binary object.
Boolean	`1`
Number	The number of characters in the number’s text representation.
MISSING	`missing`
NULL	`null`

String

The number of code points in the string — equivalent to LENGTH().

Object

The field count — equivalent to OBJECT_LENGTH().

Array

The number of elements — equivalent to ARRAY_LENGTH().

Binary

The size of the binary object.

Boolean

1

Number

The number of characters in the number’s text representation.

MISSING

missing

NULL

null

For any item not listed above, the return value is null.

Example

Query

SELECT LEN([1, 2, 3, 4]) AS `array`,
       LEN(false) AS `boolean`,
       LEN(missing) AS `missing`,
       LEN(null) AS `null`,
       LEN(1234) AS `number`,
       LEN( {"a": 1, "b": 2, "c": [1, 2, 3]} ) AS `object`,
       LEN("Couchbase") AS `string`;

Result

[
  {
    "array": 4,
    "boolean": 1,
    "null": null,
    "number": 4,
    "object": 3,
    "string": 9
  }
]

META( [ `keyspace_expr` ] ) [ .`property` ]

Description

This function returns the metadata for the document or keyspace specified by keyspace_expr. The metadata is returned as a JSON object.

To return a single property from the metadata, you must use a nested expression containing the META() function and the required property, for example META().id. The supported metadata properties are described below.

You can use the META() function with a property to index metadata information. Only certain metadata properties are indexable; these are indicated in the description below.

You can also use the META() function with a property in the predicate of an ANSI JOIN Clause.

If your cluster is running Couchbase Server version 7.6.2 and later, use the META() function with the SEARCH() function when you want to return XATTRs data through the Search Service and do not have a suitable Search index for your query.

Arguments

keyspace_expr

[Optional. Default is current keyspace.]

String or an expression that results in a keyspace or a document. This argument is not required when creating an index, since the META() function implicitly uses the keyspace being indexed.

property

[Optional] The name of a single metadata property. The property name must be separated from the META() function by a dot (.) and may be one of the following:

cas: Value representing the current state of an item which changes every time the item is modified. For details, refer to Concurrent Document Mutations.

This property is indexable.
expiration: Value representing a document’s expiration date. A value of 0 (zero) means no expiration date. For details, refer to KV Operations.

This property is indexable.
flags: Value set by the SDKs for non-JSON documents. For details, refer to Non-JSON Documents.

This property is not indexable. If you attempt to build an index on this property, an error is returned.
id: Value representing a document’s unique ID number.

This property is indexable.
type: Value for the type of document; currently only json is supported.

This property is not indexable. If you attempt to build an index on this property, an error is returned.

Return Value

The bare function returns a JSON object containing the specified document’s metadata. When the function is used with a property as part of a nested expression, the expression returns the JSON value of the property.

Examples

To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

Return all metadata

Query

SELECT META() AS metadata
FROM airline
LIMIT 3;

Results

[
  {
      "metadata": {
          "cas": 1583859008179798016,
          "expiration": 0,
          "flags": 33554432,
          "id": "airline_10",
          "type": "json"
      }
  },
  {
      "metadata": {
          "cas": 1583859008180846592,
          "expiration": 0,
          "flags": 33554432,
          "id": "airline_10123",
          "type": "json"
      }
  },
  {
      "metadata": {
          "cas": 1583859008181895168,
          "expiration": 0,
          "flags": 33554432,
          "id": "airline_10226",
          "type": "json"
      }
  }
]

Return a single metadata property

Query

SELECT META().id AS id
FROM airline
LIMIT 3;

Results

[
  {
    "id": "airline_10"
  },
  {
    "id": "airline_10123"
  },
  {
    "id": "airline_10226"
  }
]

Return a single metadata property for a specified keyspace

Query

SELECT META(route).id AS id (1)
FROM route
JOIN airport
ON route.sourceairport = airport.faa
WHERE airport.city = "Paris"
LIMIT 3;

1	You must specify a keyspace for the `META()` function because there is more than one FROM term.

Results

[
  {
    "id": "route_10136"
  },
  {
    "id": "route_10137"
  },
  {
    "id": "route_10138"
  }
]

For examples showing how to index metadata information, refer to Indexing Meta Info.

For examples showing how to use metadata information in the predicate of an ANSI JOIN clause, refer to JOIN Clause.

NODE_NAME()

Description

Returns the name of the node on which the query is running.

Arguments

None.

Return Value

A string representing a node name.

Example

Query

SELECT NODE_NAME() AS node_name;

Results

[
  {
    "node_name": "<redacted>-node-001.<redacted>:8091"
  }
]

NODE_UUID(`expression`)

Description

Returns the UUID of a node.

Arguments

expression: A string, or an expression resolving to a string, representing a node name. To get the UUID of the node on which the query is running, use the empty string "".

Return Value

A string representing the node UUID.

If the input expression is not a string, the return value is null.

If the input expression is missing, the return value is also missing.

Example

Query

SELECT NODE_UUID("") AS from_empty_string,
       NODE_UUID("<redacted>-node-001.<redacted>:8091") AS from_node_name,
       NODE_UUID(NODE_NAME()) AS from_node_name_function;

Result

[
  {
    "from_empty_string": "<redacted UUID>",
    "from_node_name": "<redacted UUID>",
    "from_node_name_function": "<redacted UUID>"
  }
]

PAIRS(`obj`)

Description

This function generates an array of arrays of [field_name, value] pairs of all possible fields in the given JSON object obj.

Nested sub-object fields are explored recursively.

Arguments

obj: An expression resolving to an object.

Return Value

Array of [field_name, value] arrays for each field in the input object obj.

If obj has nested objects, then fields of such nested sub-objects are also explored and corresponding inner-array elements are produced.
If obj is an array, then each element of the array is explored and corresponding inner-array elements are produced.
If obj is a primitive data type of integer or string, then it returns NULL, as they don’t have a name.
If obj is an array of primitive data types, then it returns an empty array [].
If obj is an array of objects, then it returns an array of objects.

If you wrap an array of primitive data types in an object constructor, it’s treated as an object and returns an array; without the object constructor, it’s treated as an array of primitive data types and returns []. For example, in PAIRS() Example 2:

PAIRS(public_likes) returns []
PAIRS({public_likes}) returns an array

Examples

To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

Input value of a nested object

Query

SELECT t        AS orig_t,
       PAIRS(t) AS pairs_t
FROM   airport t
LIMIT  1;

Result

[
  {
    "orig_t": {
      "airportname": "Calais Dunkerque",
      "city": "Calais",
      "country": "France",
      "faa": "CQF",
      "geo": {
        "alt": 12,
        "lat": 50.962097,
        "lon": 1.954764
      },
      "icao": "LFAC",
      "id": 1254,
      "type": "airport",
      "tz": "Europe/Paris"
    },
    "pairs_t": [
      [
        "id",
        1254
      ],
      [
        "city",
        "Calais"
      ],
      [
        "faa",
        "CQF"
      ],
      [
        "geo",
        {
          "alt": 12,
          "lat": 50.962097,
          "lon": 1.954764
        }
      ],
      [
        "lon",
        1.954764
      ],
      [
        "alt",
        12
      ],
      [
        "lat",
        50.962097
      ],
      [
        "type",
        "airport"
      ],
      [
        "tz",
        "Europe/Paris"
      ],
      [
        "airportname",
        "Calais Dunkerque"
      ],
      [
        "country",
        "France"
      ],
      [
        "icao",
        "LFAC"
      ]
    ]
  }
]

Input value of an array

Query

SELECT public_likes          AS orig_t,
       PAIRS(public_likes)   AS pairs_array_t,
       PAIRS({public_likes}) AS pairs_obj_t
FROM   hotel
LIMIT  1;

Result

[
  {
    "orig_t": [
      "Julius Tromp I",
      "Corrine Hilll",
      "Jaeden McKenzie",
      "Vallie Ryan",
      "Brian Kilback",
      "Lilian McLaughlin",
      "Ms. Moses Feeney",
      "Elnora Trantow"
    ],
    "pairs_array_t": [],
    "pairs_obj_t": [
      [
        "public_likes",
        [
          "Julius Tromp I",
          "Corrine Hilll",
          "Jaeden McKenzie",
          "Vallie Ryan",
          "Brian Kilback",
          "Lilian McLaughlin",
          "Ms. Moses Feeney",
          "Elnora Trantow"
        ]
      ],
      [
        "public_likes",
        "Julius Tromp I"
      ],
      [
        "public_likes",
        "Corrine Hilll"
      ],
      [
        "public_likes",
        "Jaeden McKenzie"
      ],
      [
        "public_likes",
        "Vallie Ryan"
      ],
      [
        "public_likes",
        "Brian Kilback"
      ],
      [
        "public_likes",
        "Lilian McLaughlin"
      ],
      [
        "public_likes",
        "Ms. Moses Feeney"
      ],
      [
        "public_likes",
        "Elnora Trantow"
      ]
    ]
  }
]

Input value of a primitive (field document string) data type

Query

SELECT country        AS orig_t,
       PAIRS(country) AS pairs_t
FROM   airport
LIMIT  1;

Result

[
  {
    "orig_t": "France",
    "pairs_t": null
  }
]

Input value of a primitive (constant) data type

Query

SELECT PAIRS("N1QL")             AS constant_string,
       PAIRS(4)                  AS constant_int,
       PAIRS([1,2,3])            AS constant_int_array,
       PAIRS({"name" : 3})       AS object_constant_int,
       PAIRS({"name" : [1,2,3]}) AS object_constant_int_array;

Result

[
  {
    "constant_int": null,
    "constant_int_array": [],
    "constant_string": null,
    "object_constant_int": [
      [
        "name",
        3
      ]
    ],
    "object_constant_int_array": [
      [
        "name",
        [
          1,
          2,
          3
        ]
      ],
      [
        "name",
        1
      ],
      [
        "name",
        2
      ],
      [
        "name",
        3
      ]
    ]
  }
]

Input value of an array of objects

Query

SELECT reviews[*].ratings,
       PAIRS({reviews[*].ratings}) AS pairs_t
FROM   hotel
LIMIT  1;

Result

[
  {
    "pairs_t": [
      [
        "ratings",
        [
          {
            "Cleanliness": 5,
            "Location": 4,
            "Overall": 4,
            "Rooms": 3,
            "Service": 5,
            "Value": 4
          },
          {
            "Business service (e.g., internet access)": 4,
            "Check in / front desk": 4,
            "Cleanliness": 4,
            "Location": 4,
            "Overall": 4,
            "Rooms": 3,
            "Service": 3,
            "Value": 5
          }
        ]
      ],
      [
        "ratings",
        {
          "Cleanliness": 5,
          "Location": 4,
          "Overall": 4,
          "Rooms": 3,
          "Service": 5,
          "Value": 4
        }
      ],
      [
        "ratings",
        {
          "Business service (e.g., internet access)": 4,
          "Check in / front desk": 4,
          "Cleanliness": 4,
          "Location": 4,
          "Overall": 4,
          "Rooms": 3,
          "Service": 3,
          "Value": 5
        }
      ],
      [
        "Cleanliness",
        5
      ],
      [
        "Location",
        4
      ],
      [
        "Overall",
        4
      ],
      [
        "Rooms",
        3
      ],
      [
        "Service",
        5
      ],
      [
        "Value",
        4
      ],
      [
        "Cleanliness",
        4
      ],
      [
        "Location",
        4
      ],
      [
        "Rooms",
        3
      ],
      [
        "Value",
        5
      ],
      [
        "Business service (e.g., internet access)",
        4
      ],
      [
        "Check in / front desk",
        4
      ],
      [
        "Overall",
        4
      ],
      [
        "Service",
        3
      ]
    ],
    "ratings": [
      {
        "Cleanliness": 5,
        "Location": 4,
        "Overall": 4,
        "Rooms": 3,
        "Service": 5,
        "Value": 4
      },
      {
        "Business service (e.g., internet access)": 4,
        "Check in / front desk": 4,
        "Cleanliness": 4,
        "Location": 4,
        "Overall": 4,
        "Rooms": 3,
        "Service": 3,
        "Value": 5
      }
    ]
  }
]

UNNEST_POS(`expr`)

You can use the UNNEST_POS() function with the UNNEST Clause to return the position of each element in an unnested array.

This function has a synonym UNNEST_POSITION().

Description

The UNNEST_POS function takes an unnested array and returns the position value of each element in the array.

Arguments

expr: [Required] The alias of the unnested array from an UNNEST Clause.

Return Values

The UNNEST_POS function returns the position of each element in the unnested array, expr, as an integer. It returns each position value as a separate row in JSON format. The first element in the array is at position 0.

In all other cases, the UNNEST_POS function returns NULL or MISSING.

Example

In the following example, the UNNEST_POS function takes the result of an UNNEST Clause on a given array, a1. The UNNEST function returns the position of each element in the unnested a1 array , u, as the upos value.

SELECT UNNEST_POS(u) AS upos, u FROM [{"a1":[10,9,4]}] AS d UNNEST d.a1 AS u;

Results

[
    {
        "u": 10,
        "upos": 0
    },
    {
        "u": 9,
        "upos": 1
    },
    {
        "u": 4,
        "upos": 2
    }
]

UNNEST_POSITION(`expr`)

Synonym of UNNEST_POS().

UUID()

Description

Generates a universally unique identifier (UUID) according to RFC 4122.

Arguments

None.

Return Value

A string representing a version 4 UUID.

Example

This query will return a different UUID each time you run it.

Query

SELECT UUID() AS uuid;

Results

[
  {
    "uuid": "2ca78bd8-0a28-4d68-995f-0da5e20e0964"
  }
]

For further examples using UUID(), refer to the INSERT and MERGE statements.

VERSION()

Description

Returns SQL++ version.

Arguments

None.

Return Value

Returns string containing the SQL++ version.

Example

Query

SELECT VERSION() as language_version;

Results

[
  {
    "language_version": "7.6.0-N1QL"
  }
]

Miscellaneous Utility Functions

ABORT(expression)

Description

Arguments

Return Value

Examples

BASE64(expression)

Description

Arguments

Return Value

Example

BASE64_ENCODE(expression)

BASE64_DECODE(expression)

Description

Arguments

Return Value

Example

CURRENT_USERS()

Description

Arguments

Return Value

Example

DS_VERSION()

Description

Arguments

Return Value

Example

FLATTEN_KEYS(expr1 [ modifiers ], expr2 [ modifiers ], …​)

Description

Arguments

Return Value

Examples

FORMALIZE(statement [ ,query_context ])

Description

Arguments

Return Value

Examples

LEN(expression)

Description

Arguments

Return Value

Example

META( [ keyspace_expr ] ) [ .property ]

Description

Arguments

Return Value

Examples

NODE_NAME()

Description

Arguments

Return Value

Example

NODE_UUID(expression)

Description

Arguments

Return Value

Example

PAIRS(obj)

Description

Arguments

Return Value

Examples

UNNEST_POS(expr)

Description

Arguments

Return Values

Example

Related Clauses

UNNEST_POSITION(expr)

UUID()

Description

Arguments

Return Value

Example

VERSION()

Description

Arguments

Return Value

Example

ABORT(`expression`)

BASE64(`expression`)

BASE64_ENCODE(`expression`)

BASE64_DECODE(`expression`)

FLATTEN_KEYS(`expr1` [ `modifiers` ], `expr2` [ `modifiers` ], …)

FORMALIZE(`statement` [ `,query_context` ])

LEN(`expression`)

META( [ `keyspace_expr` ] ) [ .`property` ]

NODE_UUID(`expression`)

PAIRS(`obj`)

UNNEST_POS(`expr`)

UNNEST_POSITION(`expr`)