Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

A newer version of this documentation is available.

View Latest

JSON Functions

    March 23, 2025
    + 12

    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
    sql++
    Copy
    select ENCODE_JSON(airline) as airline
from `travel-sample`.`inventory`.`airline` airline
where `id` = 10
    Result
    json5
    Copy
    [
  {
    "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
    sql++
    Copy
    select DECODE_JSON("{\"airline\":{\"callsign\": \"Mile-Air\", \"country\": \"United States\", \"iata\": \"Q5\", \"id\": 10, \"name\": \"40-mile Air\", \"type\": \"airline\"}}") as jsonObj
    Result
    json5
    Copy
    [
  {
    "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
    sql++
    Copy
    select ENCODED_SIZE(airline) as airline_size
from `travel-sample`.`inventory`.`airline` airline
where `id` = 10
    Result
    sql++
    Copy
    [
  {
    "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
    sql++
    Copy
    select POLY_LENGTH(airline) as airline_length
from `travel-sample`.`inventory`.`airline` airline
where `id` = 10
    Result
    json5
    Copy
    [
  {
    "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