A newer version of this documentation is available.

View Latest

Miscellaneous Utility Functions

Meta functions retrieve information about the document or item as well as perform base64 encoding.

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(expression)

For details, see Indexing Meta Info.

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 1. Input value of a nested object
SELECT t        AS orig_t,
       PAIRS(t) AS pairs_t
FROM   `travel-sample` t
WHERE  type = "airport"
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"
      ]
    ]
  }
]
Example 2. Input value of an array
SELECT public_likes          AS orig_t,
       PAIRS(public_likes)   AS pairs_array_t,
       PAIRS({public_likes}) AS pairs_obj_t
FROM   `travel-sample`
WHERE  type = "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"
      ]
    ]
  }
]
Example 3. Input value of a primitive (field document string) data type
SELECT country        AS orig_t,
       PAIRS(country) AS pairs_t
FROM   `travel-sample`
WHERE  type = "airport"
LIMIT  1;
Result
[
  {
    "orig_t": "France",
    "pairs_t": null
  }
]
Example 4. Input value of a primitive (constant) data type
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
      ]
    ]
  }
]
Example 5. Input value of an array of objects
SELECT reviews[*].ratings,
       PAIRS({reviews[*].ratings}) AS pairs_t
FROM   `travel-sample`
WHERE  type = "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
      }
    ]
  }
]

UUID()

Returns a version 4 universally unique identifier (UUID).