Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

Miscellaneous Utility Functions

  • 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": [
      "builtin:Administrator"
    ]
  }
]

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"
  }
]

EVALUATE(statement [ ,params ])

Description

This function enables you to execute a SQL++ statement provided as a string and returns the result as an array. It can be used as a part of larger query or request, or wherever arrays are permitted within a statement.

The function evaluates the statement dynamically using the same permissions as the invoking statement. The statement must be read-only. If it tries to modify data (like with UPDATE or INSERT), the function fails with error 5010, "not a readonly request”.

The results are materialized in memory, so large result sets may require a lot of memory. To limit memory usage, you can use quotas (the evaluated statement operates within the invoking statement’s quota).

Arguments

statement

A string containing the statement to evaluate.

params

Can be one of the following:

  • An object containing named parameters.

  • An array containing positional parameters.

Return Value

An array that contains the result of the evaluated statement.

Examples

Example 1: Using named parameters
Query
SELECT EVALUATE("SELECT $named_param AS example",
        {"named_param":"This is the named parameter's value"})
        eval_result;
Results
[
  {
    "eval_result": [
      {
        "example": "This is the named parameter's value"
      }
    ]
  }
]
Example 2: Using EVALUATE with INFER
Query
SELECT `Flavor` FROM EVALUATE("INFER `travel-sample`")[0] inf;
Results
[
  {
    "Flavor": "`type` = \"airport\""
  },
  {
    "Flavor": "`stops` = 0, `type` = \"route\""
  },
  {
    "Flavor": "`type` = \"landmark\""
  },
  {
    "Flavor": "`type` = \"hotel\""
  },
  {
    "Flavor": "`type` = \"airline\""
  }
]

EXTRACTDDL(filter [ ,options ])

Couchbase Server 8.0

Description

This function extracts Data Definition Language (DDL) statements of buckets and returns them as an array of strings. It retrieves definitions for buckets, scopes, collections, indexes, sequences, functions, and prepared statements.

You can use these definitions for purposes such as replication, backup, or auditing.

The function supports the following statements:

  • CREATE BUCKET

  • CREATE SCOPE

  • CREATE COLLECTION

  • CREATE INDEX

  • CREATE SEQUENCE

  • CREATE OR REPLACE FUNCTION Couchbase Server 8.0.1

  • PREPARE Couchbase Server 8.0.1

To execute this function, you must have the query_system_catalog role. Also, to extract DDLs from a specific bucket, you need necessary permissions on that bucket. For more information about roles and permissions, see Authorization.

Arguments

filter

[Required] A string pattern to match against bucket names using the LIKE operator. To match all bucket names, use an empty string ("").

options

[Optional] A JSON object specifying options for the function. If you omit this argument, the output includes all supported DDL statements.

Options

Name Description Schema

flags
optional

Specifies the types of DDL statements to extract.

Accepts either a number or an array of strings, but not both.

Statement String Number

CREATE BUCKET

"bucket"

1

CREATE SCOPE

"scope"

2

CREATE COLLECTION

"collection"

4

CREATE INDEX

"index"

8

CREATE SEQUENCE

"sequence"

16

CREATE OR REPLACE FUNCTION
Couchbase Server 8.0.1

"function"

32

PREPARE
Couchbase Server 8.0.1

"prepared"

64

To extract multiple statement types, specify an array of their string values or a single numeric value that represents the sum of their respective numeric values.

For example, to extract CREATE BUCKET and CREATE INDEX statements, input the value as 9 (sum of 1 + 8) or as an array of strings ["bucket", "index"].

String array or Number

Return Value

An array of strings, with each string containing a DDL statement.

Examples

Extract CREATE INDEX statements from the travel-sample bucket using a string flag
Query
SELECT extractddl("travel-sample",{"flags":["index"]});
Results
[
  {
    "$1": [
      "CREATE INDEX `def_airportname` ON `travel-sample`(`airportname`) ;",
      "CREATE INDEX `def_city` ON `travel-sample`(`city`) ;",
      "CREATE INDEX `def_faa` ON `travel-sample`(`faa`) ;",
      "CREATE INDEX `def_icao` ON `travel-sample`(`icao`) ;",
      ...
    ]
  }
]
Extract CREATE INDEX statements from the travel-sample bucket using a numeric flag
Query
SELECT extractddl("travel-sample", {"flags":8});
Results
[
  {
    "$1": [
      "CREATE INDEX `def_airportname` ON `travel-sample`(`airportname`) ;",
      "CREATE INDEX `def_city` ON `travel-sample`(`city`) ;",
      "CREATE INDEX `def_faa` ON `travel-sample`(`faa`) ;",
      "CREATE INDEX `def_icao` ON `travel-sample`(`icao`) ;",
      ...
    ]
  }
]
Extract CREATE BUCKET and CREATE SCOPE statements from the travel-sample bucket using a numeric flag
Query
SELECT extractddl("travel-sample",{"flags":3});

In this query, the value 3 represents the sum of the flags for bucket (1) and scope (2).

Results
[
  {
    "$1": [
      "CREATE BUCKET `travel-sample` WITH {
              'evictionPolicy':'fullEviction',
              'numVBuckets':128,
              'ramQuota':200,
              'replicaNumber':0,
              'storageBackend':'magma'
          };",
      "CREATE SCOPE `travel-sample`.`inventory`;",
      "CREATE SCOPE `travel-sample`.`tenant_agent_00`;",
      "CREATE SCOPE `travel-sample`.`tenant_agent_01`;",
      "CREATE SCOPE `travel-sample`.`tenant_agent_02`;",
      "CREATE SCOPE `travel-sample`.`tenant_agent_03`;",
      "CREATE SCOPE `travel-sample`.`tenant_agent_04`;"
    ]
  }
]
Extract CREATE FUNCTION and PREPARE statements from the travel-sample bucket
Query
SELECT extractddl("travel-sample",{"flags":["function","prepared"]});
Results
[
  {
    "$1": [
      "CREATE OR REPLACE FUNCTION `celsius`(...)
          LANGUAGE INLINE AS (args[0] - 32) * 5/9;",
      "PREPARE SELECT * FROM route\n
              WHERE airline = \"FL\";",
      "PREPARE NameParam AS\nSELECT * FROM hotel\n
          WHERE city=$city AND country=$country;"
    ]
  }
]
Extract all supported DDL statements from the travel-sample bucket
Query
SELECT extractddl("travel-sample");
Results
[
  {
    "$1": [
      "CREATE OR REPLACE FUNCTION `celsius`(...)
          LANGUAGE INLINE AS (args[0] - 32) * 5/9;",
      "CREATE BUCKET `travel-sample`
           WITH {'evictionPolicy':'fullEviction',
                  'numVBuckets':128,
                  'ramQuota':200,
                  'replicaNumber':0,
                  'storageBackend':'magma'
                };",
      "CREATE SCOPE `travel-sample`.`inventory`;",
      "CREATE COLLECTION `travel-sample`.`inventory`.`airline;",
      "CREATE COLLECTION `travel-sample`.`inventory`.`airport;",
      "CREATE COLLECTION `travel-sample`.`inventory`.`hotel;",
      "CREATE COLLECTION `travel-sample`.`inventory`.`landmark;",
      "CREATE COLLECTION `travel-sample`.`inventory`.`route;",
      "CREATE SCOPE `travel-sample`.`tenant_agent_00`;",
      ...
      "CREATE INDEX `def_airportname`
          ON `travel-sample`(`airportname`) ;",
      "CREATE INDEX `def_city`
          ON `travel-sample`(`city`) ;",
      ...
    ]
  }
]
Extract DDL statements from all buckets
Query
SELECT extractddl("",{"flags":["bucket","scope"]});
Results
[
  {
    "$1": [
      "CREATE BUCKET `travel-sample`
           WITH {'evictionPolicy':'fullEviction',
                'numVBuckets':128,
                'ramQuota':200,
                'replicaNumber':0,
                'storageBackend':'magma'
              };",
      "CREATE SCOPE `travel-sample`.`inventory`;",
      "CREATE SCOPE `travel-sample`.`tenant_agent_00`;",
      "CREATE SCOPE `travel-sample`.`tenant_agent_01`;",
      "CREATE SCOPE `travel-sample`.`tenant_agent_02`;",
      "CREATE SCOPE `travel-sample`.`tenant_agent_03`;",
      "CREATE SCOPE `travel-sample`.`tenant_agent_04`;"
    ]
  }
]

FINDERR(expression)

Description

Returns the full details of any Query service or cbq shell error.

Arguments

expression

One of the following:

  • A number representing an error code. In this case, the function returns the full details of the error matching the error code.

  • A string. In this case, the function searches for the target string in all of the error message fields except for user_error, and returns the full details of any errors that match the string.

  • A regular expression. In this case, the function searches for the regular expression in all of the error message fields except for user_error, and returns the full details of any errors that match the pattern.

Return Value

The return value is an array of one or more objects, each of which contains the details of an error that matches the find expression.

For each error, the function returns the following fields.

Name Description Schema

applies_to
required

One of the following:

  • cbq-shell: The error applies to the cbq shell.

  • Server: The error applies to the server.

enum (cbq-shell, Server)

code
required

A number representing the error.

Integer

description
required

Message describing why the error occurred.

String

reason
optional

List of possible causes of the error.

String array

user_action
optional

List of possible steps a user can take to mitigate the error.

String array

user_error
optional

One of the following:

  • Yes: The error was caused by the user.

  • No: The error was caused by other services, or was internal to the server.

  • Maybe: A combination of both.

enum (Yes, No, Maybe)
The error details also include a symbol field, which contains a representation string for the error. This field is for internal use only, and is not shown in the results. However, the FINDERR function does search this field when the find expression is a string or a regular expression.

Examples

Find error details by code number
Query
SELECT FINDERR(5011);
Results
[
  {
    "$1": [
      {
        "applies_to": "Server",
        "code": 5011,
        "description": "Abort: «reason»",
        "reason": [
          [
            "The SQL++ abort() function was called in the statement.",
            "e.g. SELECT abort('An example cause')"
          ]
        ],
        "user_error": "Yes"
      }
    ]
  }
]
Find error details by matching a string
Query
SELECT FINDERR("A semantic error is present in the statement.");
Results
[
  {
    "$1": [
      {
        "applies_to": "Server",
        "code": 3100,
        "description": "A semantic error is present in the statement.",
        "reason": [
          "The statement includes portions that violate semantic constraints."
        ],
        "user_action": [
          "The cause will contain more detail on the violation; revise the statement and re-submit."
        ],
        "user_error": "Yes"
      }
    ]
  }
]
Find multiple error details by matching a string
Query
SELECT FINDERR("semantic");
Results
[
  {
    "$1": [
      {
        "applies_to": "Server",
        "code": 3100,
        "description": "A semantic error is present in the statement.",
        "reason": [
          "The statement includes portions that violate semantic constraints."
        ],
        "user_action": [
          "The cause will contain more detail on the violation; revise the statement and re-submit."
        ],
        "user_error": "Yes"
      },
      {
        "applies_to": "Server",
        "code": 3220,
        "description": "«name» window function «clause» «reason»",
        "reason": [
          "A violation of the window function semantic restrictions was present in the statement."
        ],
        "user_action": [
          "Revise the statement to remove the violation."
        ],
        "user_error": "Yes"
      },
      {
        "applies_to": "Server",
        "code": 3300,
        "description": "recursive_with semantics: «cause»",
        "reason": [
          "The statement specifies restricted syntax in a recursive common table expression definition."
        ],
        "user_action": [
          "Revise the statement removing the restricted syntax."
        ],
        "user_error": "Yes"
      }
    ]
  }
]
Find multiple error details by matching a regular expression
Query
SELECT FINDERR("[IU][NP]SERT");
Results
[
  {
    "$1": [
      {
        "applies_to": "Server",
        "code": 3150,
        "description": "MERGE with ON KEY clause cannot have document key specification in INSERT action.",
        "reason": [
          [
            "A lookup merge statement specified a document key.",
            "e.g. MERGE INTO default USING [{},{}] AS source ON KEY 'aaa' WHEN NOT MATCHED THEN INSERT ('key',{})"
          ]
        ],
        "user_action": [
          "Refer to the documentation for lookup merge statements."
        ],
        "user_error": "Yes"
      },
// ...
      {
        "applies_to": "Server",
        "code": 5072,
        "description": "No UPSERT key for «value»",
        "user_action": [
          "Contact support."
        ]
      },
// ...
      {
        "applies_to": "Server",
        "code": 15005,
        "description": "No keys to insert «details»"
      }
    ]
  }
]

See Also

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"
  },
  // ...
]

HASHBYTES(input, [ options ])

Description

This function returns a binary hash value for a given input using a specified hashing algorithm. By using this function, you can verify or compare data quickly, or protect your data by masking its original form while still allowing verification or comparison.

Arguments

input

A binary object or any SQL++ data type. The JSON marshalled value of the data is used as the input.

options

[Optional] An object that specifies the hashing algorithm and other options for the function. If omitted, the default hashing algorithm is sha256.

Options

Name Description Schema

algorithm
required

Specifies the hashing algorithm to be used.

Supported algorithms are: crc32, md4, md5, sha224, sha256, sha384, sha512, sha512/224, and sha512/256.

String

polynomial
optional

The polynomial to use. This only applies if the algorithm is crc32. This property may have one of the following values:

  • ieee (the default value)

  • castagnoli

  • koopman

  • A valid 32-bit integer, provided either as a JSON number (decimal) or a string that can be parsed as a numeric value (supports hexadecimal with a "0x" prefix)

Default: ieee

String or integer

Return Value

A binary hash value. The size or length of the value depends on the algorithm you choose.

Examples

Find the hash value using the sha256 algorithm
Query
SELECT HASHBYTES('Hello World', {"algorithm":"sha256"});
Results
[
  {
    "$1": "a591a6d40bf420404a011733cfb7b190d62c65bf0bcda32b57b277d9ad9f146e"
  }
]
Find the hash value using the crc32 algorithm
Query
SELECT HASHBYTES("hello world", {"algorithm":"crc32", "polynomial":"koopman"});
Results
[
  {
    "$1": "df373d3c"
  }
]

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

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 database 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.

xattrs

Value representing extended attributes (XATTRs) of a document.

To access XATTRs, use the syntax META().xattrs.<attribute>[.<path>], where:

  • <attribute> is a top-level attribute name or key of the XATTR object.

  • <path> is an optional subpath within that attribute.

While you can create an index on a specific extended attribute like META().xattrs.attr1, you cannot create an index on the entire META().xattrs object itself.

Attempting to select the entire META().xattrs object will return an empty result.

  • Starting with Couchbase Server 8.0, you can include up to 15 XATTRs per query.

  • You can also use the META().xattrs property to access virtual XATTRs (see Example 5). However, this is an expensive operation and may increase query latency.

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"
  }
]
Return a specific XATTR

To run this example, you must first create a document with key landmark_1001 and add an XATTR attribute metadata to it.

Query to insert
INSERT INTO `travel-sample`.`inventory`.`landmark` (KEY, VALUE, OPTIONS)
VALUES (
    "landmark_1001",
    {
        "title": "Gillingham (Kent)",
        "name": "Hollywood Bowl",
        "alt": null,
        "address": "4 High Street, ME7 1BB",
        "directions": null,
        "phone": null,
        "tollfree": null,
        "email": null,
        "url": "http://www.thehollywoodbowl.co.uk",
        "hours": null,
        "image": null,
        "price": null,
        "content": "A newly extended lively restaurant
              located in the high street,
              an American Hollywood style restaurant
              beautifully decorated with old photos
              and a great menu including burgers and ribs.",
        "geo": {
            "lat": 51.38937,
            "lon": 0.5427,
            "accuracy": "RANGE_INTERPOLATED"
        },
        "activity": "eat",
        "type": "landmark",
        "id": 10020,
        "country": "United Kingdom",
        "city": "Gillingham",
        "state": null
    },
    {
        "xattrs": {
            "metadata": {
                "created_by": "admin",
                "created_at": "2026-01-05"
            }
        }
    }
);

Next, run the following query to retrieve the XATTR attribute metadata.

Query to retrieve
SELECT META().xattrs.metadata
FROM `travel-sample`.`inventory`.`landmark`
USE KEYS ["landmark_1001"];
Results
[{
    "metadata": {
        "created_at": "2026-01-05",
        "created_by": "admin"
    }
}]
Return a virtual XATTR
Query
SELECT META().xattrs.`$document`
FROM airline
USE KEYS ["airline_10123"];
Results
[{
    "$document": {
        "CAS": "0x1877771a5c980000",
        "datatype": [
            "snappy",
            "json"
        ],
        "deleted": false,
        "exptime": 0,
        "flags": 0,
        "last_modified": "1763008734",
        "revid": "1",
        "seqno": "0x0000000000000025",
        "value_bytes": 118,
        "value_crc32c": "0x85aa593e",
        "vbucket_uuid": "0x0000e287931a14fb"
    }
}]

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": "127.0.0.1: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("127.0.0.1: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
      }
    ]
  }
]

REDACT (expression [ , filter-obj1, filter-obj2, …​ ] )

Description

Redacts field names or values in a JSON object based on specific filtering criteria.

Use this function to protect sensitive data such as credit card numbers or personally identifiable information (PII). You can either mask data with a specified string or omit it entirely from the output.

Masking applies to only string values. The function cannot mask non-string data types such as numbers, booleans, or dates. To redact non-string values, you must omit the fields entirely from the output instead.

Arguments

expression

[Required] An expression resolving to a JSON object that you want to redact.

filter-obj1, filter-obj2, …​

[Optional] One or more JSON objects that specify the filtering criteria, such as which fields to redact, how to redact them, and whether to redact field names, values, or both.

Each object can include the following fields:

Name Description Schema

pattern
optional

A pattern to match against the field names in the object to redact.

The function only redacts fields that match the pattern and only supports single-level field names. It does not support nested field paths. For example, the pattern "parent.child" cannot match the nested field { "parent": { "child": "value" } }.

If you do not specify a pattern, the function redacts all fields based on other filters.

String

regex
optional

Specifies whether the pattern is a regular expression or a simple string.

If true, treats the pattern as a regular expression.

If false, treats the pattern as a simple string.

Default: false

Boolean

exact
optional

Specifies whether the pattern must match the entire field name or just a portion of it.

If true, the pattern must match the entire field name.

If false, the pattern can match any part of the field name.

Default: false

Boolean

ignorecase
optional

Specifies whether pattern matching is case-sensitive.

If true, the pattern matching ignores case.

If false, the pattern matching is case-sensitive.

Default: false

Boolean

name
optional

Specifies whether to redact field names in addition to field values.

If true, redacts both field names and values.

If false, redacts only field values.

Default: false

Boolean

strict
optional

Specifies whether to apply strict value redaction.

If true, applies the mask to all characters including whitespaces and special characters. See Example 6.

If false, applies the mask only to alphanumeric characters and preserves whitespaces and special characters.

Default: false

Boolean

omit
optional

Specifies whether to omit fields entirely from the output.

If true, removes matching fields entirely.

If false, the fields remain in the output but their values are redacted according to the other filters.

Default: false

Boolean

mask
optional

The string to mask the redacted string values.

Default: "x"

String

fixedlength
optional

Specifies whether to replace string values with a single instance of the mask.

If true, replaces the entire string with one instance of the mask.

If false, replaces each character in the string with the mask, preserving the original length of the string.

Default: false

Boolean

exclude
optional

Specifies whether to invert the redaction logic.

If true, the function redacts fields that do not match the pattern and excludes fields that do match.

When you use exclude, the function ignores any remaining filters. Place this filter last in the list or use it as the only filter to ensure the function applies all other rules.

If false, the function redacts fields that match the pattern.

Default: false

Boolean

Usage

The function applies redaction based on the filter objects you specify. If you do not specify a filter object, it redacts only field values. The field names remain visible and the function replaces values with a default mask, "x". See Example 1.

By default, the function masks only to alphanumeric characters while preserving whitespaces and special characters (such as @, #). To redact all characters in a value, including symbols and spaces, set "strict": true in the filter object. See Example 6.

To redact both field names and values, set "name": true in the filter object. See Example 3.

When you provide multiple filter objects, the function evaluates them in the order you specify. For each field in the input object, the function applies the first filter that matches the field’s criteria. Once applied, it does not consider any other filter objects for that field, even if they also match. See Example 4.

For nested objects, the nested fields inherit the redaction behavior of their parent by default. However, if you define a filter that specifically targets a nested field, that filter overrides the parent field’s filter.

Return Value

A JSON object with field names/values redacted.

Examples

Redact field values using default behavior
Query
WITH sample AS ([
  {
    "flight": "AA123",
    "passenger": "Alex Johnson",
    "email": "alex.johnson@example.com",
    "passport": "X12345678",
    "address": "123 Maple Street, New York, NY"
  }
])
SELECT REDACT(sample) AS redacted_data;
Results
[{
    "redacted_data": [{
        "address": "xxx xxxxx xxxxxx, xxx xxxx, xx",
        "email": "xxxx.xxxxxxx@xxxxxxx.xxx",
        "flight": "xxxxx",
        "passenger": "xxxx xxxxxxx",
        "passport": "xxxxxxxxx"
    }]
}]
Redact values using custom patterns and mask
Query
WITH sample AS ([
  {
    "flight": "AA123",
    "passenger": "Alex Johnson",
    "email": "alex.johnson@example.com",
    "passport": "X12345678",
    "address": "123 Maple Street, New York, NY"
  }
])
SELECT REDACT(sample,
    {"pattern": "ad.*", "regex": true},
    {"pattern": "email|passport", "regex": true, "mask": "#"}
) AS redacted_data;
Results
[{
    "redacted_data": [{
        "address": "xxx xxxxx xxxxxx, xxx xxxx, xx",
        "email": "####.#######@#######.###",
        "flight": "AA123",
        "passenger": "Alex Johnson",
        "passport": "#########"
    }]
}]
Redact both field names and values
Query
WITH sample AS ([
  {
    "flight": "AA123",
    "passenger": "Alex Johnson",
    "email": "alex.johnson@example.com",
    "passport": "X12345678",
    "address": "123 Maple Street, New York, NY"
  }
])
SELECT REDACT(sample,
    {"pattern": "passport", "name":true},
    {"pattern": "email", "omit": true}
) AS redacted_data;
Results
[{
    "redacted_data": [{
        "address": "123 Maple Street, New York, NY",
        "f0004": "xxxxxxxxx",
        "flight": "AA123",
        "passenger": "Alex Johnson"
    }]
}]
Redact when there are multiple filters for the same field
Query
WITH sample AS ([
  {
    "flight": "AA123",
    "passenger": "Alex Johnson",
    "email": "alex.johnson@example.com",
    "passport": "X12345678",
    "address": "123 Maple Street, New York, NY"
  }
])
SELECT REDACT(sample,
    {"pattern": "passenger|passport", "regex": true},
    {"pattern": "passenger", "omit": true}
) AS redacted_data;
Results
[{
    "redacted_data": [{
        "address": "123 Maple Street, New York, NY",
        "email": "alex.johnson@example.com",
        "flight": "AA123",
        "passenger": "xxxx xxxxxxx",
        "passport": "xxxxxxxxx"
    }]
}]

In this example, the second filter is not applied to passenger because the first filter already matched it.

Redact all fields except those matching a pattern
Query
WITH sample AS ([
  {
    "flight": "AA123",
    "passenger": "Alex Johnson",
    "email": "alex.johnson@example.com",
    "passport": "X12345678",
    "address": "123 Maple Street, New York, NY"
  }
])
SELECT REDACT(sample,
    {"pattern": "passenger|passport", "regex": true, "exclude": true}
) AS redacted_data;
Results
[{
    "redacted_data": [{
        "address": "xxx xxxxx xxxxxx, xxx xxxx, xx",
        "email": "xxxx.xxxxxxx@xxxxxxx.xxx",
        "flight": "xxxxx",
        "passenger": "Alex Johnson",
        "passport": "X12345678"
    }]
}]
Redact all values including spaces and special characters
Query
WITH sample AS ([
  {
    "email": "alex.johnson@example.com",
    "password": "P@ssw0rd!",
    "address": "123 Maple Street, New York, NY"
  }
])
SELECT REDACT(sample,
    {"pattern": ".*", "regex": true, "strict": true}
) AS redacted_data;
Results
[{
    "redacted_data": [{
        "address": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
        "email": "xxxxxxxxxxxxxxxxxxxxxxxx",
        "password": "xxxxxxxxx"
    }]
}]

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"
  }
]