MaxCompute:FROM_JSON

Syntax

from_json(<jsonStr>, <schema>)

Parameters

• jsonStr: required. The JSON string that you entered.

• schema: required. The schema of the JSON string. The value of this parameter must be in the same format as that in the statement for creating a table, such as array<bigint>, map<string, array<string>>, or struct<a:int, b:double, `C`:map<string,string>>.

  Note

  Keys in a struct are case-sensitive. You can also specify a struct in the format of a BIGINT, b DOUBLE, which is equivalent to STRUCT<a:BIGINT, b:DOUBLE>.

  The following table describes the mappings between JSON data types and MaxCompute data types.

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

  Note

  The JSON string of the OBJECT and ARRAY types are parsed as much as possible. If the data type of the JSON string is not mapped to any MaxCompute data type, the JSON string is omitted. For ease of use, all JSON data types can be converted into the STRING data type supported by MaxCompute. When you convert a JSON string of the NUMBER type to a value of the FLOAT, DOUBLE, or DECIMAL type, the precision of the value cannot be ensured. We recommend you convert the JSON string to a value of the STRING type and then convert the obtained value to a value of the FLOAT, DOUBLE, or DECIMAL type.

Return value

A value of the ARRAY, MAP, or STRUCT type is returned.

FROM_JSON

• Example 1: Convert a specific JSON string into a value of a specific data type. Sample statements:

  -- The return value is {a:1,b:0.8}. 
  select from_json('{"a":1, "b":0.8}', 'a int, b double');
  
  -- The return value is {time:26/08/2015}. 
  select from_json('{"time":"26/08/2015"}', 'time string');
  
  -- The return value is {a:1, b:0.8, c:NULL}. 
  select from_json('{"a":1, "b":0.8}', 'a int, b double, c string');
  
  -- The return value is [1,2,3]. 
  select from_json('[1, 2, 3, "a"]', 'array<bigint>');
  
  -- The return value is {a:1, b:[1,2,3], c:{}, d:v}. 
  select from_json('{"a":1,"b":[1,2,3],"c":{},"d":"v"}', 'map<string, string>');

• Example 2: Use the map_keys and from_json functions to obtain all keys in a JSON string. You can also use JSON_KEYS for the same purpose. Sample statements:

  -- The return value is [a, b]. 
  select map_keys(from_json('{"a":1,"b":2}','map<string,string>'));

Related functions

FROM_JSON is a complex type function or a string function.

• For more information about the functions that are used to process data of complex data types, such as ARRAY, MAP, STRUCT, and JSON, see Complex type functions.

• For more information about functions related to string searches and conversion, see String functions.