Parse JSON string into ARRAY, MAP, or STRUCT using FROM_JSON - MaxCompute

Parses a JSON string and returns a value of the ARRAY, MAP, or STRUCT type based on the specified schema.

Syntax

from_json(<jsonStr>, <schema>)

Parameters

Parameter	Required	Description
`jsonStr`	Yes	The JSON string to parse.
`schema`	Yes	The output schema. Use the same format as a `CREATE TABLE` column definition: for example, `array<bigint>`, `map<string, array<string>>`, or struct<a:int, b:double, `C`:map<string,string>>. You can also write a struct schema in the shorthand form `a BIGINT, b DOUBLE`, which is equivalent to `STRUCT<a:BIGINT, b:DOUBLE>`.

Return value

Returns a value of the ARRAY, MAP, or STRUCT type.

Usage notes

Precision loss for floating-point types

Converting a JSON number to FLOAT, DOUBLE, or DECIMAL cannot guarantee decimal precision. To preserve precision, parse the value as STRING first, then cast it to the target numeric type. Alternatively, use GET_JSON_OBJECT to extract the value directly.

Partial parsing for OBJECT and ARRAY

JSON strings of the OBJECT and ARRAY types are parsed as far as possible. If a JSON value cannot be mapped to any MaxCompute type, that value is omitted from the result.

Case sensitivity in struct keys

Keys in a struct schema are case-sensitive.

Type mappings

JSON type	MaxCompute type
OBJECT	STRUCT, MAP, STRING
ARRAY	ARRAY, STRING
NUMBER	TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, STRING
BOOLEAN	BOOLEAN, STRING
STRING	STRING, CHAR, VARCHAR, BINARY, DATE, DATETIME
NULL	All types

All JSON types can be mapped to STRING.

Examples

Parse a JSON object into a struct

SELECT from_json('{"a":1, "b":0.8}', 'a int, b double');

{a:1, b:0.8}

SELECT from_json('{"time":"26/08/2015"}', 'time string');

{time:26/08/2015}

Missing fields in jsonStr are filled with NULL:

SELECT from_json('{"a":1, "b":0.8}', 'a int, b double, c string');

{a:1, b:0.8, c:NULL}

Parse a JSON array

Non-mappable elements (for example, the string "a") are omitted when the target type is BIGINT:

SELECT from_json('[1, 2, 3, "a"]', 'array<bigint>');

[1, 2, 3]

Parse a JSON object into a map

All values are cast to STRING:

SELECT from_json('{"a":1,"b":[1,2,3],"c":{},"d":"v"}', 'map<string, string>');

{a:1, b:[1,2,3], c:{}, d:v}

Extract all keys from a JSON object

Use map_keys with from_json to get all keys. JSON_KEYS returns the same result:

SELECT map_keys(from_json('{"a":1,"b":2}', 'map<string,string>'));

[a, b]

Related functions

GET_JSON_OBJECT — extract a value from a JSON string by path
JSON_KEYS — return all keys of a JSON object
map_keys — return all keys of a MAP value
Complex type functions — functions for ARRAY, MAP, STRUCT, and JSON data
String functions — functions for string manipulation and conversion