All Products
Search

This Product

    MaxCompute:JSON functions

    Document Center

    MaxCompute:JSON functions

    Last Updated:Jun 02, 2026

    MaxCompute supports four complex data types: ARRAY, MAP, STRUCT, and JSON. This page lists the built-in JSON functions and their usage notes.

    Index

    Function

    Features

    FROM_JSON

    Returns an ARRAY, MAP, or STRUCT type from a JSON string and the output format.

    GET_JSON_OBJECT

    Extracts a string from a JSON string by path.

    JSON_ARRAY

    Wraps a JSON object (can be empty) into a JSON array.

    JSON_CONTAINS

    Checks whether a JSON document contains a specified JSON element.

    JSON_EXISTS

    Checks whether a specified JSONPath exists in a JSON document.

    JSON_EXPLODE

    Expands each element in a JSON array or JSON object into multiple rows.

    JSON_EXTRACT

    Extracts data from a JSON string or JSON type by JSONPath.

    JSON_FORMAT

    Converts a JSON value to a string. Not prettified by default.

    JSON_INSERT

    Inserts a JSON value at a specified position in a JSON document.

    JSON_LENGTH

    Returns the length of the JSON data at a specified path.

    JSON_OBJECT

    Generates a JSON object from key-value pairs.

    JSON_PARSE

    Converts a string to JSON. Returns an error if the string is not valid JSON.

    JSON_PRETTY

    Formats a JSON string for readability by adding line breaks and spaces.

    JSON_SET

    Replaces or adds a value at a specified path in a JSON document.

    JSON_STRIP_NULLS

    Removes all null fields or elements from a JSON object or array.

    JSON_TUPLE

    Extracts strings from a JSON string for a group of keys.

    JSON_TYPE

    Returns the type name of a JSON value.

    JSON_UNQUOTE

    Removes quotes from a JSON string.

    JSON_VALID

    Checks whether a string is valid JSON.

    TO_JSON

    Converts a complex data type to a JSON string.

    Usage notes

    Usage notes

    • SDK version requirements

      • Only Java SDK V0.44.0 and later versions are supported.

      • Only PyODPS V0.11.4.1 and later versions are supported.

    • Table operation limits

      • You cannot add a JSON column to a table.

      • Clustered tables are not supported.

      • Tables of the Delta Table type are not supported.

    • SQL operation limits

      • Comparison operations on the JSON type are not supported.

      • You cannot use ORDER BY or GROUP BY clauses on the JSON type. You also cannot use a JSON type column as a JOIN key.

    • Data precision

      • The integer part of a JSON NUMBER is stored as a BIGINT type. An overflow occurs if the integer is outside the BIGINT range.

      • The decimal part of a JSON NUMBER is stored as a DOUBLE type. Precision loss may occur when the decimal part is converted to the DOUBLE type.

    • Character limits: The Unicode character \u0000 is not supported in strings that are used to generate JSON data.

    • Engine compatibility: If you use another engine, such as Hologres, to read data from a table, the JSON data type cannot be read.

    • Java UDFs and Python UDFs do not support the JSON type.

    • The JSON data type can be nested up to 20 levels deep.

    • Development tools

      Supported development tools include the MaxCompute client (odpscmd), MaxCompute Studio, and DataWorks. External ecosystems such as Dataphin are not supported. If you want to use the JSON data type with an external system, you must confirm its compatibility before you start. When you use the odpscmd client, note the following:

      • You must upgrade the client to V0.46.5 or later. Otherwise, you cannot run the DESC json_table command or download JSON data using Tunnel.

      • In the conf\odps_config.ini file in the client installation path, set the use_instance_tunnel parameter to false. Otherwise, queries will fail.

    References

    For more information about the JSON data type, see JSON data type.