--- title: Miscellaneous Utility Functions description: Miscellaneous utility functions enable you to perform tasks beyond the usual evaluation and transformation of data. pubDate: 2026-08-17T09:53:44.266Z antora: editUrl: https://github.com/couchbaselabs/docs-devex/edit/release/7.6/modules/n1ql/pages/n1ql-language-reference/metafun.adoc xref: xref:7.6@server:n1ql:n1ql-language-reference/metafun.adoc[] --- [Consult the llms.txt file for a full list of contents](/llms.txt) [View original HTML](/server/7.6/n1ql/n1ql-language-reference/metafun.html) # Miscellaneous Utility Functions 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)ABORT(`expression`) ### [](#description)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](createfunction.md). You can use [conditional operators](conditionalops.md) to check the parameters, and use this function to generate an error if something is wrong. ### [](#arguments)Arguments expression An expression resolving to a string. ### [](#return-value)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)Examples Abort a query Query ```sqlpp SELECT ABORT("Something went wrong"); ``` Result ```json [ { "code": 5011, "msg": "Abort: \"Something went wrong\". - cause: \"Something went wrong\"" } ] ``` User-defined function with error checking Function ```sqlpp 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 ```sqlpp EXECUTE FUNCTION rstr(100, 4); ``` Result ```json [ { "code": 10109, "msg": "Error executing function rstr : \"Search string is not a string\" - cause: \"Search string is not a string\"" } ] ``` Test invalid number argument ```sqlpp EXECUTE FUNCTION rstr("Couchbase", "foo"); ``` Result ```json [ { "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 ```sqlpp EXECUTE FUNCTION rstr("Couchbase", 10); ``` Result ```json [ { "code": 10109, "msg": "Error executing function rstr : \"Substring longer than search string\" - cause: \"Substring longer than search string\"" } ] ``` Test with valid arguments ```sqlpp EXECUTE FUNCTION rstr("Couchbase", 4); ``` Result ```json [ "base" ] ``` ## [](#base64)BASE64(`expression`) _Alias_: [BASE64\_ENCODE()](#base64-encode) ### [](#description-2)Description Returns the [base64](https://en.wikipedia.org/wiki/Base64) encoding of the given `expression`. ### [](#arguments-2)Arguments expression An expression representing any supported SQL++ datatype. ### [](#return-value-2)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)Example Query ```sqlpp 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 ```json [ { "array": "WzEsMiwzLDRd", "boolean": "ZmFsc2U=", "null": "bnVsbA==", "number": "MTIzNA==", "object": "eyJhIjoxLCJiIjoyLCJjIjpbMSwyLDNdfQ==", "string": "IkNvdWNoYmFzZSI=" } ] ``` ## [](#base64-encode)BASE64\_ENCODE(`expression`) Alias of [BASE64()](#base64). ## [](#base64-decode)BASE64\_DECODE(`expression`) ### [](#description-3)Description Reverses the encoding done by the [BASE64()](#base64) or [BASE64\_ENCODE()](#base64-encode) functions. ### [](#arguments-3)Arguments expression An expression representing a valid base64-encoded string. ### [](#return-value-3)Return Value The decoded value of the input expression. If the input expression is `missing`, the return value is also `missing`. ### [](#example-2)Example Query ```sqlpp 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 ```json [ { "array": [ 1, 2, 3, 4 ], "boolean": false, "null": null, "number": 1234, "object": { "a": 1, "b": 2, "c": [ 1, 2, 3 ] }, "string": "Couchbase" } ] ``` ## [](#current-users)CURRENT\_USERS() ### [](#description-4)Description Returns the authenticated users for the current statement. ### [](#arguments-4)Arguments None. ### [](#return-value-4)Return Value An array of strings, each representing a user name. ### [](#example-3)Example Query ```sqlpp SELECT CURRENT_USERS() as current_users; ``` Results ```json [ { "current_users": [ "builtin:Administrator" ] } ] ``` ## [](#ds-version)DS\_VERSION() ### [](#description-5)Description Returns the Couchbase Server version. ### [](#arguments-5)Arguments None. ### [](#return-value-5)Return Value Returns string containing the Couchbase Server version. ### [](#example-4)Example Query ```sqlpp SELECT DS_VERSION() as server_version; ``` Results ```json [ { "server_version": "7.6.0-1886-enterprise" } ] ``` ## [](#finderr)FINDERR(`expression`) Couchbase Server 7.6.4 ### [](#description-6)Description Returns the full details of any Query service or cbq shell error. ### [](#arguments-6)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-6)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) | > [!NOTE] > 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-2)Examples Find error details by code number Query ```sqlpp SELECT FINDERR(5011); ``` Results ```json [ { "$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 ```sqlpp SELECT FINDERR("A semantic error is present in the statement."); ``` Results ```json [ { "$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 ```sqlpp SELECT FINDERR("semantic"); ``` Results ```json [ { "$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 ```sqlpp SELECT FINDERR("[IU][NP]SERT"); ``` Results ```json [ { "$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)See Also * The [finderr](../../cli/finderr.md) command line tool * [SQL++ Error Codes](n1ql-error-codes.md) ## [](#flatten%5Fkeys)FLATTEN\_KEYS(`expr1` \[ `modifiers` \], `expr2` \[ `modifiers` \], …​) ### [](#description-7)Description This function can only be used when defining an index key for an [array index](indexing-arrays.md). 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](selectintro.md#index-selection) and will generate spans. ### [](#arguments-7)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](createindex.md#index-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-7)Return Value The return value is a flattened list of array elements for use in an array index key. ### [](#examples-3)Examples For examples, refer to [Array Indexing Examples](indexing-arrays.md#examples). ## [](#formalize)FORMALIZE(`statement` \[ `,query_context` \]) _(Introduced in Couchbase Server 7.6)_ ### [](#description-8)Description Fully expands all references within a query, using the specified query context. This function has a synonym FORMALISE(). ### [](#arguments-8)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-8)Return Value Returns a query with all references fully specified. ### [](#examples-4)Examples Formalize a query Query ```sqlpp SELECT formalize("SELECT * FROM landmark WHERE country = 'United Kingdom'","default:`travel-sample`.inventory") ``` Results ```json [ { "$1": "select self.* from `default`:`travel-sample`.`inventory`.`landmark` where ((`landmark`.`country`) = \"United Kingdom\")" } ] ``` Formalize recently completed requests Query ```sqlpp SELECT statement, NVL(queryContext,"") AS queryContext, formalize(statement, queryContext) AS formalized FROM system:completed_requests; ``` Results ```json [ { "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)HASHBYTES(`input`, \[ `options` \]) ### [](#description-9)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-9)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)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-9)Return Value A binary hash value. The size or length of the value depends on the algorithm you choose. ### [](#examples-5)Examples Find the hash value using the `sha256` algorithm Query ```sqlpp SELECT HASHBYTES('Hello World', {"algorithm":"sha256"}); ``` Results ```json [ { "$1": "a591a6d40bf420404a011733cfb7b190d62c65bf0bcda32b57b277d9ad9f146e" } ] ``` Find the hash value using the `crc32` algorithm Query ```sqlpp SELECT HASHBYTES("hello world", {"algorithm":"crc32", "polynomial":"koopman"}); ``` Results ```json [ { "$1": "df373d3c" } ] ``` ## [](#len)LEN(`expression`) ### [](#description-10)Description A general function to return the length of an item. ### [](#arguments-10)Arguments expression An expression representing any supported SQL++ datatype. ### [](#return-value-10)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()](stringfun.md#fn-str-length). | | Object | The field count — equivalent to [OBJECT\_LENGTH()](objectfun.md#fn-obj-length). | | Array | The number of elements — equivalent to [ARRAY\_LENGTH()](arrayfun.md#fn-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-5)Example Query ```sqlpp 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 ```json [ { "array": 4, "boolean": 1, "null": null, "number": 4, "object": 3, "string": 9 } ] ``` ## [](#meta)META( \[ `keyspace_expr` \] ) \[ .`property` \] ### [](#description-11)Description This function returns the [metadata](../../learn/data/data.md#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](nestedops.md#field-selection) 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](indexing-meta-info.md). 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](join.md#section%5Fek1%5Fjnx%5F1db). If your database is running Couchbase Server version 7.6.2 and later, use the `META()` function with the [SEARCH() function](#searchfun.adoc) when you want to return XATTRs data through the Search Service and do not have a suitable Search index for your query. ### [](#arguments-11)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](../../../../java-sdk/current/howtos/concurrent-document-mutations.md). 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](../../../../java-sdk/current/howtos/kv-operations.md#document-expiration). This property is indexable. flags Value set by the SDKs for non-JSON documents. For details, refer to [Non-JSON Documents](../../../../java-sdk/current/howtos/transcoders-nonjson.md). 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-11)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-6)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](../n1ql-intro/queriesandresults.md#query-context). Return all metadata Query ```sqlpp SELECT META() AS metadata FROM airline LIMIT 3; ``` Results ```json [ { "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 ```sqlpp SELECT META().id AS id FROM airline LIMIT 3; ``` Results ```json [ { "id": "airline_10" }, { "id": "airline_10123" }, { "id": "airline_10226" } ] ``` Return a single metadata property for a specified keyspace Query ```sqlpp 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 ```json [ { "id": "route_10136" }, { "id": "route_10137" }, { "id": "route_10138" } ] ``` For examples showing how to index metadata information, refer to [Indexing Meta Info](indexing-meta-info.md). For examples showing how to use metadata information in the predicate of an ANSI JOIN clause, refer to [JOIN Clause](join.md). ## [](#node-name)NODE\_NAME() ### [](#description-12)Description Returns the name of the node on which the query is running. ### [](#arguments-12)Arguments None. ### [](#return-value-12)Return Value A string representing a node name. ### [](#example-6)Example Query ```sqlpp SELECT NODE_NAME() AS node_name; ``` Results ```json [ { "node_name": "127.0.0.1:8091" } ] ``` ## [](#node-uuid)NODE\_UUID(`expression`) ### [](#description-13)Description Returns the UUID of a node. ### [](#arguments-13)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-13)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-7)Example Query ```sqlpp 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 ```json [ { "from_empty_string": "", "from_node_name": "", "from_node_name_function": "" } ] ``` ## [](#pairs)PAIRS(`obj`) ### [](#description-14)Description This function generates an array of arrays of \[`field_name`, `value`\] pairs of all possible fields in the given JSON object `obj`. > [!NOTE] > Nested sub-object fields are explored recursively. ### [](#arguments-14)Arguments obj An expression resolving to an object. ### [](#return-value-14)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. > [!TIP] > If you wrap an array of primitive data types in an [object constructor](constructionops.md#object-construction), 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-example2): > > * `PAIRS(public_likes)` returns `[]` > * `PAIRS({public_likes})` returns an array ### [](#examples-7)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](../n1ql-intro/queriesandresults.md#query-context). Input value of a nested object Query ```sqlpp SELECT t AS orig_t, PAIRS(t) AS pairs_t FROM airport t LIMIT 1; ``` Result ```json [ { "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 ```sqlpp 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 ```json [ { "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 ```sqlpp SELECT country AS orig_t, PAIRS(country) AS pairs_t FROM airport LIMIT 1; ``` Result ```json [ { "orig_t": "France", "pairs_t": null } ] ``` Input value of a primitive (constant) data type Query ```sqlpp 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 [ { "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 ```sqlpp SELECT reviews[*].ratings, PAIRS({reviews[*].ratings}) AS pairs_t FROM hotel LIMIT 1; ``` Result ```json [ { "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)UNNEST\_POS(`expr`) You can use the `UNNEST_POS()` function with the [UNNEST Clause](unnest.md) to return the position of each element in an unnested array. This function has a synonym [UNNEST\_POSITION()](#unnest-position). ### [](#description-15)Description The `UNNEST_POS` function takes an unnested array and returns the position value of each element in the array. ### [](#arguments-15)Arguments expr \[Required\] The alias of the unnested array from an [UNNEST Clause](unnest.md). ### [](#return-values)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-8)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. ```N1QL SELECT UNNEST_POS(u) AS upos, u FROM [{"a1":[10,9,4]}] AS d UNNEST d.a1 AS u; ``` Results ```json [ { "u": 10, "upos": 0 }, { "u": 9, "upos": 1 }, { "u": 4, "upos": 2 } ] ``` ### [](#related-clauses)Related Clauses * [UNNEST Clause](unnest.md) * [FROM Clause](from.md) ## [](#unnest-position)UNNEST\_POSITION(`expr`) Synonym of [UNNEST\_POS()](#unnest-pos). ## [](#uuid)UUID() ### [](#description-16)Description Generates a universally unique identifier (UUID) according to [RFC 4122](https://www.ietf.org/rfc/rfc4122.txt). ### [](#arguments-16)Arguments None. ### [](#return-value-15)Return Value A string representing a version 4 UUID. ### [](#example-9)Example This query will return a different UUID each time you run it. Query ```sqlpp SELECT UUID() AS uuid; ``` Results ```json [ { "uuid": "2ca78bd8-0a28-4d68-995f-0da5e20e0964" } ] ``` For further examples using `UUID()`, refer to the [INSERT](insert.md) and [MERGE](merge.md) statements. ## [](#version)VERSION() ### [](#description-17)Description Returns SQL++ version. ### [](#arguments-17)Arguments None. ### [](#return-value-16)Return Value Returns string containing the SQL++ version. ### [](#example-10)Example Query ```sqlpp SELECT VERSION() as language_version; ``` Results ```json [ { "language_version": "7.6.0-N1QL" } ] ```