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)

It reverses the encoding done by the BASE64() or BASE64_ENCODING() 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) return NULL
PAIRS({public_likes}) returns an array

+ 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 3b - Input value of a primitive (constant string) data type.

SELECT PAIRS("N1QL");

Result:
[
  {
    "$1": null
  }
]

+ Example 3c - Input value of a primitive (constant integer) data type.

SELECT PAIRS(4);

Result:
[
  {
    "$1": null
  }
]

+ Example 3d - Input value of a primitive (constant array of integers) data type.

SELECT PAIRS([1,2,3]);

Result:
[
  {
    "$1": []
  }
]

+ Example 3e - Input value of a primitive data type (constant integer or array of integers, wrapped in a JSON object).

SELECT PAIRS({"name" : 3});

Result:
[
  {
    "$1": [
      [
        "name",
        3
      ]
    ]
  }
]

SELECT PAIRS({"name" : [1,2,3]});

Result:
[
  {
    "$1": [
      [
        "name",
        [
          1,
          2,
          3
        ]
      ],
      [
        "name",
        1
      ],
      [
        "name",
        2
      ],
      [
        "name",
        3
      ]
    ]
  }
]

+ Example 4 - 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).