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)

    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).