JSON Functions

Functions for manipulating data in JSON.

DECODE_JSON(expression)

Description

Unmarshals the JSON-encoded string into a SQL++ value. The empty string is MISSING.

Example

Example 1. Encode a returned result set as a JSON string.

Query

select ENCODE_JSON(airline) as airline
from `travel-sample`.`inventory`.`airline` airline
where `id` = 10

Result

[
  {
    "airline": "{\"callsign\":\"MILE-AIR\",\"country\":\"United States\",\"iata\":\"Q5\",\"icao\":\"MLA\",\"id\":10,\"name\":\"40-Mile Air\",\"type\":\"airline\"}"
  }
]

ENCODE_JSON(expression)

Description

Marshals the SQL++ value into a JSON-encoded string. MISSING becomes the empty string.

Example

Example 2. Unmarshal a JSON string into an SQL++ value.

Query

select DECODE_JSON("{\"airline\":{\"callsign\": \"Mile-Air\", \"country\": \"United States\", \"iata\": \"Q5\", \"id\": 10, \"name\": \"40-mile Air\", \"type\": \"airline\"}}") as jsonObj

Result

[
  {
    "jsonObj": {
      "airline": {
        "callsign": "Mile-Air",
        "country": "United States",
        "iata": "Q5",
        "id": 10,
        "name": "40-mile Air",
        "type": "airline"
      }
    }
  }
]

ENCODED_SIZE(expression)

Description

Number of bytes in an uncompressed JSON encoding of the value. The exact size is implementation-dependent. Always returns an integer, and never MISSING or NULL. Returns 0 for MISSING.

Example

Example 3. Return the size of the returned object.

Query

select ENCODED_SIZE(airline) as airline_size
from `travel-sample`.`inventory`.`airline` airline
where `id` = 10

Result

[
  {
    "airline_size": 120
  }
]

POLY_LENGTH(expression)

Description

Returns length of the value after evaluating the expression. The exact meaning of length depends on the type of the value:

Example

Example 4. Return the length of the retrieved object.

Query

select POLY_LENGTH(airline) as airline_length
from `travel-sample`.`inventory`.`airline` airline
where `id` = 10

Result

[
  {
    "airline_length": 7
  }
]

MISSING: MISSING
NULL: NULL
String: The length of the string.
Array: The number of elements in the array.
Object: The number of name/value pairs in the object
Any other value: NULL