MaxCompute:JSON data type

Background information

Semi-structured data falls between structured and unstructured data. This type of data has a schema, but the schema is flexible and does not have strong constraints. The schema is usually self-describing. JSON data is a typical example. MaxCompute already enhances SQL support for semi-structured data with features such as schema evolution, JSON strings, built-in functions for complex types, and Lambda expressions. In this model, you must process semi-structured data in a standardized way before you can import it into structured tables with a defined schema. If your business data changes, you must explicitly run schema evolution Data Definition Language (DDL) statements to modify the table schema.

This model has strong schema constraints. You cannot quickly import semi-structured data into the system. During data import, data that does not conform to the table schema is discarded and cannot be fully saved. To solve these problems, MaxCompute provides a new data type: JSON. The JSON type supports semi-structured data without strong schema constraints. It also takes full advantage of column-oriented storage optimizations to meet the requirements for high flexibility and high performance.

Basic principles

The JSON data type is a new data type that you can use just like other types. You do not need to manage schema information. After you insert JSON data, MaxCompute automatically extracts a public schema and performs optimizations. MaxCompute stores the data in a column-oriented format whenever possible to improve performance. The following example uses test data:

CREATE TABLE json_table
(
    json_val  json
);

CREATE TABLE string_table
(
    string_val  STRING
);

INSERT INTO string_table VALUES
        ('{"a":1, "b":2}')
        ,('{"a":"key", "b":2}')
        ,('{"c":3}');

INSERT INTO json_table
SELECT  json_parse(string_val)
FROM    string_table;

When you write data, MaxCompute automatically extracts the public schema <"a":binary, "b":bigint, "c":bigint>. When you read data, MaxCompute can perform column pruning based on the schema. This reduces the amount of data that needs to be read and improves efficiency. For example:

SELECT  json_val["b"]
        ,json_val["c"]
FROM    json_table
;  
-- When reading the table, column pruning is performed to keep only the b and c variables.
+-----+-----+
| _c0 | _c1 |
+-----+-----+
| 2   | NULL |
| 2   | NULL |
| NULL | 3   |
+-----+-----+

For the non-public part of the schema, MaxCompute uses the BINARY type for storage. This reduces storage space compared to the STRING type. The new JSON data type also greatly improves the conversion efficiency between STRING and JSON compared to user-defined functions (UDFs).

Scope

• SDK version requirements

  • Only Java SDK V0.44.0 and later versions are supported.

  • Only PyODPS V0.11.4.1 and later versions are supported.

• Table operation limits

  • You cannot add a JSON column to a table.

  • Clustered tables are not supported.

  • Tables of the Delta Table type are not supported.

• SQL operation limits

  • Comparison operations on the JSON type are not supported.

  • You cannot use ORDER BY or GROUP BY clauses on the JSON type. You also cannot use a JSON type column as a JOIN key.

• Data precision

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

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

• Character limits: The Unicode character \u0000 is not supported in strings that are used to generate JSON data.

• Engine compatibility: If you use another engine, such as Hologres, to read data from a table, the JSON data type cannot be read.

• Java UDFs and Python UDFs do not support the JSON type.

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

• Development tools

  Supported development tools include the MaxCompute client (odpscmd), MaxCompute Studio, and DataWorks. External ecosystems such as Dataphin are not supported. If you want to use the JSON data type with an external system, you must confirm its compatibility before you start. When you use the odpscmd client, note the following:

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

Literal constants

The JSON type is strictly defined according to JSON standards. It supports BOOLEAN, NUMBER, STRING, NULL, ARRAY, and OBJECT. The NUMBER type uses BIGINT and DOUBLE for storage. Values that exceed the limits may experience precision loss. Note that json 'null' is different from sql null.

JSON 'null'
JSON '123'
JSON '123.34'
JSON 'true'
JSON '{"id":123,"name":"MaxCompute"}'
JSON '[12, 34]'

Constants must conform to JSON standards. For example, JSON '{id:123,"name":"MaxCompute"}' is an invalid JSON string because the key id must be enclosed in double quotation marks ("").

JSON type definition

You do not need to specify a schema. You can create a JSON type just as you would a basic data type.

CREATE TABLE mf_json_table (json_val JSON);

Generate JSON data

You can generate JSON data in several ways:

• JSON literal

  INSERT INTO mf_json_table VALUES (json '123');

• JSON functions

  -- JSON_OBJECT and JSON_ARRAY are built-in functions of MaxCompute.
  INSERT INTO mf_json_table SELECT JSON_OBJECT("key",123, "value", "abc");
  
  SELECT * FROM mf_json_table;
  
  -- The following result is returned:
  +----------+
  | json_val |
  +----------+
  | 123      |
  | {"key":123,"value":"abc"} |
  +----------+
  
  
  INSERT INTO mf_json_table SELECT JSON_ARRAY("key",234, "value", "abc");
  
  SELECT * FROM mf_json_table;
  
  -- The following result is returned:
  +----------+
  | json_val |
  +----------+
  | 123      |
  | ["key",234,"value","abc"] |
  | {"key":123,"value":"abc"} |
  +----------+

• Type conversion

  Note the differences between converting data using CAST and parsing data using json_parse. For more information, see JSON functions.

  INSERT INTO mf_json_table SELECT CAST("abc" AS json);
  SELECT * FROM mf_json_table;
  -- The following result is returned:
  +----------+
  | json_val |
  +----------+
  | 123      |
  | "abc"    |
  | ["key",234,"value","abc"] |
  | {"key":123,"value":"abc"} |
  +----------+

Access JSON data

You can access JSON data using indexing, or using the json_extract and get_json_object functions. These functions return a JSON type.

-- Returns 123
SELECT v['id'] 
  FROM VALUES (JSON '{"id":123}') AS t(v);
  
-- Returns 12
SELECT v[0] 
  FROM VALUES (JSON '[12, 34]') AS t(v);
  
-- Returns 1
SELECT v['x']['a']  FROM VALUES (json '{"x": {"a": 1, "b": 2}}') AS t(v);

-- Returns NULL
SELECT v[0] 
FROM VALUES (JSON '{"id":123}') AS t(v);

-- Returns NULL
SELECT v['not_exists'] 
FROM VALUES (JSON '{"id":123}') AS t(v);

-- Access data using the GET_JSON_OBJECT function. A STRING value 'MaxCompute' is returned.
SELECT GET_JSON_OBJECT(v, '$.x.name')
  FROM VALUES (JSON '{"x": {"id": 1001, "name": "MaxCompute"}}') AS t(v);
  
-- The following result is returned:
+-----+
| _c0 |
+-----+
| MaxCompute |
+-----+

-- Access data using the JSON_EXTRACT function. A JSON value 'MaxCompute' is returned.
SELECT JSON_EXTRACT(v, '$.x.name') 
  FROM VALUES (JSON '{"x": {"id": 1001, "name": "MaxCompute"}}') AS t(v);
  
-- The following result is returned:
+-----+
| _c0 |
+-----+
| "MaxCompute" |
+-----+