Use TO_JSON to convert complex types to JSON strings - MaxCompute

Converts a complex type expression to a JSON string.

Syntax

STRING TO_JSON(<expr>)

Parameters

Parameter	Required	Type	Description
`expr`	Yes	ARRAY, MAP, or STRUCT	The complex type expression to convert.

Return value

Returns a STRING in JSON format.

Usage notes

The following behaviors apply to STRUCT expressions:

Key case: All keys are converted to lowercase.
NULL values (default): If a value is NULL, the entire key-value pair is excluded from the output.
NULL values (BigQuery-compatible mode): Run SET odps.sql.bigquery.compatible=true; before the query. NULL values are retained as null in the output instead of being excluded.

Examples

Convert complex types to a JSON string

SELECT TO_JSON(NAMED_STRUCT('a', 1, 'b', 2));
-- {"a":1,"b":2}

SELECT TO_JSON(NAMED_STRUCT('time', "26/08/2015"));
-- {"time":"26/08/2015"}

SELECT TO_JSON(ARRAY(NAMED_STRUCT('a', 1, 'b', 2)));
-- [{"a":1,"b":2}]

SELECT TO_JSON(MAP('a', NAMED_STRUCT('b', 1)));
-- {"a":{"b":1}}

SELECT TO_JSON(MAP('a', 1));
-- {"a":1}

SELECT TO_JSON(ARRAY((MAP('a', 1))));
-- [{"a":1}]

Handle NULL values in a STRUCT expression

In default mode, NULL values are excluded from the output and STRUCT keys are lowercased.

-- Key "A" is lowercased to "a" in the output.
SELECT TO_JSON(NAMED_STRUCT("A", "B"));
-- {"a":"B"}

-- The key-value pair for "k1" is excluded because its value is NULL.
SELECT TO_JSON(NAMED_STRUCT("k1", CAST(NULL AS STRING), "k2", "v2"));
-- {"k2":"v2"}

Retain NULL values with BigQuery-compatible mode

In BigQuery-compatible mode, NULL values are included in the output as null.

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

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`
  );
-- {"id":null,"tag":null,"group":null,"success":true}

What's next

GET_JSON_OBJECT: Extract a value from a JSON string
JSON_TUPLE: Extract multiple values from a JSON string in a single pass