Miscellaneous Utility Functions

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)
Returns metadata for the document expression.
You can use the meta() function when creating an index. In this context, the meta() function does not require a parameter. It implicitly uses the keyspace being indexed.
CREATE INDEX id_idx on `travel-sample`(meta().id);
The meta() function in a query is given an expression; if this resolves to a keyspace alias, the requested field (id or CAS) is returned.
SELECT b.name, meta(b).id 
FROM `travel-sample` b 
WHERE meta(b).id > "g" 
LIMIT 1;

Results:
[
  {
    "id": "hotel_10025",
    "name": "Medway Youth Hostel"
  }
]
PAIRS( obj)
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
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.
Note: 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).