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.

A newer version of this documentation is available.

View Latest

Miscellaneous Utility Functions

  • concept
March 23, 2025
+ 12

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)

(Introduced in Couchbase Server 7.0)

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

Example 1. Abort a query
Query
n1ql
Copy
SELECT ABORT("Something went wrong");
Result
json
Copy
[
  {
    "code": 5011,
    "msg": "Abort: \"Something went wrong\". - cause: \"Something went wrong\""
  }
]
Example 2. User-defined function with error checking
Function
n1ql
Copy
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
n1ql
Copy
EXECUTE FUNCTION rstr(100, 4);
Result
json
Copy
[
  {
    "code": 10109,
    "msg": "Error executing function rstr : \"Search string is not a string\" - cause: \"Search string is not a string\""
  }
]
Test invalid number argument
n1ql
Copy
EXECUTE FUNCTION rstr("Couchbase", "foo");
Result
json
Copy
[
  {
    "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
n1ql
Copy
EXECUTE FUNCTION rstr("Couchbase", 10);
Result
json
Copy
[
  {
    "code": 10109,
    "msg": "Error executing function rstr : \"Substring longer than search string\" - cause: \"Substring longer than search string\""
  }
]
Test with valid arguments
n1ql
Copy
EXECUTE FUNCTION rstr("Couchbase", 4);
Result
json
Copy
[
  "base"
]

BASE64(expression)

Returns the base64 encoding of the given expression.

BASE64_ENCODE(expression)

Alias of BASE64().

BASE64_DECODE(expression)

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

META( [ keyspace_expr ] ) [ .property ]

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.

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

Example 3. Return all metadata
n1ql
Copy
SELECT META() AS metadata
FROM `travel-sample`.inventory.airline
LIMIT 3;
Results
json
Copy
[
  {
      "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"
      }
  }
]
Example 4. Return a single metadata property
n1ql
Copy
SELECT META().id AS id
FROM `travel-sample`.inventory.airline
LIMIT 3;
Results
json
Copy
[
  {
    "id": "airline_10"
  },
  {
    "id": "airline_10123"
  },
  {
    "id": "airline_10226"
  }
]
Example 5. Return a single metadata property for a specified keyspace
n1ql
Copy
SELECT META(route).id AS id (1)
FROM `travel-sample`.inventory.route
JOIN `travel-sample`.inventory.airport
ON route.sourceairport = airport.faa
WHERE airport.city = "Paris"
LIMIT 3;
Results
json
Copy
[
  {
    "id": "route_10136"
  },
  {
    "id": "route_10137"
  },
  {
    "id": "route_10138"
  }
]
1 You must specify a keyspace for the META() function because there is more than one FROM term.

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.

PAIRS(obj)

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

a valid JSON 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.

When the field_name is in curly brackets, it’s treated as an array and returns an array; but without curly bracket, it’s treated as a primitive data type and returns NULL. For example,

  • PAIRS(public_likes) returns NULL

  • PAIRS({public_likes}) returns an array

Examples

Example 6. Input value of a nested object
n1ql
Copy
SELECT t        AS orig_t,
       PAIRS(t) AS pairs_t
FROM   `travel-sample`.inventory.airport t
LIMIT  1;
Result
json
Copy
[
  {
    "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"
      ]
    ]
  }
]
Example 7. Input value of an array
n1ql
Copy
SELECT public_likes          AS orig_t,
       PAIRS(public_likes)   AS pairs_array_t,
       PAIRS({public_likes}) AS pairs_obj_t
FROM   `travel-sample`.inventory.hotel
LIMIT  1;
Result
json
Copy
[
  {
    "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"
      ]
    ]
  }
]
Example 8. Input value of a primitive (field document string) data type
n1ql
Copy
SELECT country        AS orig_t,
       PAIRS(country) AS pairs_t
FROM   `travel-sample`.inventory.airport
LIMIT  1;
Result
json
Copy
[
  {
    "orig_t": "France",
    "pairs_t": null
  }
]
Example 9. Input value of a primitive (constant) data type
n1ql
Copy
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
json
Copy
[
  {
    "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
      ]
    ]
  }
]
Example 10. Input value of an array of objects
n1ql
Copy
SELECT reviews[*].ratings,
       PAIRS({reviews[*].ratings}) AS pairs_t
FROM   `travel-sample`.inventory.hotel
LIMIT  1;
Result
json
Copy
[
  {
    "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
      }
    ]
  }
]

UUID()

Returns a version 4 universally unique identifier (UUID).