All Products
Search

This Product

    MaxCompute:JSON functions

    Document Center

    MaxCompute:JSON functions

    Last Updated:Mar 05, 2026

    MaxCompute supports four complex data types: ARRAY, MAP, STRUCT, and JSON. This topic describes the functions and features of the JSON data type.

    Index

    Function

    Features

    FROM_JSON

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

    GET_JSON_OBJECT

    Extracts a string from a JSON string using a specified path.

    JSON_ARRAY

    Transforms a JSON object, which can be empty, into a JSON array that contains the object.

    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 data type based on a specified JSONPath.

    JSON_FORMAT

    Converts a JSON data type to a string. The output is 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 a JSON data type. An error is returned if the string is not in valid JSON format.

    JSON_PRETTY

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

    JSON_SET

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

    JSON_STRIP_NULLS

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

    JSON_TUPLE

    Extracts strings from a standard JSON string for a specified 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 specified complex data type to a JSON string.

    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.