All Products
Search

This Product

    MaxCompute:JSON functions

    Document Center

    MaxCompute:JSON functions

    Last Updated:Dec 31, 2025

    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.

    Applicability

    • SDK version requirements

      • Java SDK V0.44.0 or later is required.

      • PyODPS V0.11.4.1 or later is required.

    • Table operation limits

      • Adding a JSON column to an existing table is not supported.

      • Clustered tables are not supported.

      • Delta tables are not supported.

    • SQL operation limits

      • Comparison operations on the JSON data type are not supported.

      • You cannot use the JSON data type in an ORDER BY or GROUP BY clause, or as a JOIN key.

    • Data precision

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

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

    • Character limit: The string used to generate JSON data cannot contain the Unicode character \u0000.

    • Engine compatibility: Other engines, such as Hologres, cannot read the JSON data type from tables.

    • Java user-defined functions (UDFs) and Python UDFs do not support the JSON data type.

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

    • Developer tools

      Supported developer tools include the odpscmd client, Studio, and DataWorks. Other tools in the ecosystem, such as Dataphin, are not supported. To use JSON data with external systems, confirm compatibility before you proceed. When you use the odpscmd client, note the following:

      • 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.