TO_JSON command format, parameters, and return value - MaxCompute

Converts the specified complex type expr to a string in JSON format.

Syntax

STRING TO_JSON(<expr>)

Parameters

expr: Required. An expression of a complex data type. The data type must be ARRAY, MAP, or STRUCT.

If the input is of the STRUCT type (struct<key1:value1, key2:value2):

When the STRUCT type is converted to a JSON string, all keys are converted to lowercase.
If a value is NULL, the data group containing the value is excluded from the output. For example, if value2 is NULL, key2:value2 is excluded from the output JSON string.
You can run the SET odps.sql.bigquery.compatible=true; command to enable the BigQuery-compatible mode. In this mode, if a value is NULL, the corresponding key-value pair is included in the output. For example, if value2 is NULL, key2:NULL is included in the output.

Return value

Returns a string in JSON format.

Examples

Example 1: Convert a complex type to a JSON string. Sample command:

-- Returns {"a":1,"b":2}.
SELECT TO_JSON(NAMED_STRUCT('a', 1, 'b', 2));
-- Returns {"time":"26/08/2015"}.
SELECT TO_JSON(NAMED_STRUCT('time', "26/08/2015"));
-- Returns [{"a":1,"b":2}].
SELECT TO_JSON(ARRAY(NAMED_STRUCT('a', 1, 'b', 2)));
-- Returns {"a":{"b":1}}.
SELECT TO_JSON(MAP('a', NAMED_STRUCT('b', 1)));
-- Returns {"a":1}.
SELECT TO_JSON(MAP('a', 1));
-- Returns [{"a":1}].
SELECT TO_JSON(ARRAY((MAP('a', 1))));

Example 2: Handle a NULL value in a STRUCT type expression. Sample command:

-- Returns {"a":"B"}. When the STRUCT type is converted to a JSON string, all keys are converted to lowercase.
SELECT TO_JSON(NAMED_STRUCT("A", "B"));
-- Returns {"k2":"v2"}. The group of data that contains a NULL value is not included in the output JSON string.
SELECT TO_JSON(NAMED_STRUCT("k1", CAST(NULL AS STRING), "k2", "v2"));

Example 3: Convert a STRUCT type expression that contains a NULL value in BigQuery-compatible mode. Sample command:

-- Returns {"k1":null,"k2":"v2"}
SET odps.sql.bigquery.compatible=true;
SELECT TO_JSON(named_struct("k1", CAST(NULL AS STRING), "k2", "v2"));

-- Returns {"id":null,"tag":null,"group":null,"success":true}
SET odps.sql.bigquery.compatible=true;
SELECT TO_JSON(struct(experiment_id AS id,tag  AS tag,`group`,`success`)) AS experiment_in_json
  FROM(
    SELECT CAST(NULL AS string) AS experiment_id,
    CAST(NULL AS string) AS tag,
    CAST(NULL AS string) AS `group`,
    TRUE AS `success`
);