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.

JSON Functions

  • reference
    +
    Functions for encoding, decoding, and evaluating JSON values.

    DECODE_JSON(expression)

    Converts a JSON-encoded string into a SQL++ value.

    Arguments

    expression

    [Required] An expression that represents a JSON-encoded string.

    Return Value

    The function returns 1 of the following:

    • A SQL++ value.

    • NULL if the input is NULL or not a valid JSON value.

    • MISSING if the input is empty.

    Example

    Example 1. Decode a JSON string into a SQL++ value
    Query
    SELECT DECODE_JSON(
    "{\"airline\":
        { \"callsign\": \"Mile-Air\",
          \"country\": \"United States\",
          \"iata\": \"Q5\",
          \"id\": 10,
          \"name\": \"40-mile Air\",
          \"type\": \"airline\"
        }
    }"
) as decoded_value;
    Result
    [
  {
    "decoded_value": {
      "airline": {
        "callsign": "Mile-Air",
        "country": "United States",
        "iata": "Q5",
        "id": 10,
        "name": "40-mile Air",
        "type": "airline"
      }
    }
  }
]

    ENCODE_JSON(expression)

    Converts a SQL++ value into a JSON-encoded string.

    Arguments

    expression

    [Required] A SQL++ expression to be encoded.

    Return Value

    The function returns 1 of the following:

    • A JSON encoded string.

    • NULL if the input is NULL.

    • MISSING if the input is MISSING.

    Example

    Example 2. Encode a SQL++ value into a JSON string
    Query
    SELECT ENCODE_JSON(
    {
        "airline": {
            "callsign": "Mile-Air",
            "country": "United States",
            "iata": "Q5",
            "id": 10,
            "name": "40-mile Air",
            "type": "airline"
        }
    }
) as encoded_value;
    Result
    [
  {
    "encoded_value":
        "{ \"airline\":
             { \"callsign\":\"Mile-Air\",
               \"country\":\"United States\",
               \"iata\":\"Q5\",
               \"id\":10,
               \"name\":\"40-mile Air\",
               \"type\":\"airline\"
            }
        }"
  }
]

    ENCODED_SIZE(expression)

    Returns the number of bytes in an uncompressed JSON encoding of a value. The exact size depends on the implementation and may vary.

    Arguments

    expression

    [Required] An expression to evaluate.

    Return Value

    An integer representing the size in bytes.

    The function never returns NULL or MISSING. If the input value is MISSING, the function returns 0.

    Example

    Example 3. Calculate the size of a JSON-encoded value
    Query
    SELECT ENCODED_SIZE(
    {
        "airline": {
            "callsign": "Mile-Air",
            "country": "United States",
            "iata": "Q5",
            "id": 10,
            "name": "40-mile Air",
            "type": "airline"
        }
    }
) as encoded_size;
    Result
    [
  {
    "encoded_size": 119
  }
]

    POLY_LENGTH(expression)

    Evaluates an expression and returns the length of the resulting value. The definition of length depends on the type of the evaluated value. For more information, see the Return Value section.

    Arguments

    expression

    [Required] An expression to evaluate.

    Return Value

    The function returns a value based on the data type of the result:

    • String: Returns the number of characters in the string.

    • Array: Returns the number of elements in the array.

    • Object: Returns the number of name/value pairs in the object.

    • MISSING: Returns MISSING.

    • NULL: Returns NULL.

    • Any other value: Returns NULL.

    Example

    Example 4. Find the length of a string, array, and object
    Query
    SELECT
    POLY_LENGTH("Flight 101") as string_length,
    POLY_LENGTH(["Flight 101", "Flight 202", "Flight 303"]) as array_length,
    POLY_LENGTH({
        "flight": 101,
        "airline": "Mile-Air",
        "destination": "United States"
    }) as object_length;
    Result
    [
  {
    "string_length": 10,
    "array_length": 3,
    "object_length": 3
  }
]