MaxCompute:JSON data type

When to use the JSON type

How it works

When you insert JSON data, MaxCompute automatically extracts a public schema from the data and performs optimizations. Fields in the public schema are stored in columnar format. Fields that are not in the public schema are stored as BINARY.

For example, given three rows with fields a, b, and c:

INSERT INTO json_table
SELECT json_parse(string_val)
FROM   string_table;

MaxCompute extracts the public schema <"a":binary, "b":bigint, "c":bigint>. A subsequent query that reads only b and c scans just those columns:

SELECT json_val["b"], json_val["c"]
FROM   json_table;
-- Column pruning keeps only b and c.
+------+------+
| _c0  | _c1  |
+------+------+
| 2    | NULL |
| 2    | NULL |
| NULL | 3    |
+------+------+

JSON values are stored using MaxCompute internal types. Because of this mapping, values outside the BIGINT or DOUBLE range will overflow or lose precision.

Prerequisites

Before you begin, make sure you have:

• A MaxCompute project with the JSON type enabled (see Enable the JSON type)

• Java SDK V0.44.0 or later, or PyODPS V0.11.4.1 or later

• If using odpscmd: version V0.46.5 or later, with use_instance_tunnel=false in conf\odps_config.ini

Enable the JSON type

The odps.sql.type.json.enable flag controls JSON type availability:

To enable the JSON type in an existing project:

SET odps.sql.type.json.enable=true;

To check the current value:

setproject;

Limitations

Work with the JSON type

All examples below use a shared order record to show create, insert, and query patterns end-to-end:

{"id": 1001, "customer": "Molly", "amount": 299.50}

CREATE TABLE orders (record JSON);

INSERT INTO orders VALUES (JSON '{"id": 1001, "customer": "Molly", "amount": 299.50}');

-- JSON_OBJECT builds a JSON object from key-value pairs.
INSERT INTO orders SELECT JSON_OBJECT("id", 1002, "customer", "Frank", "amount", 150.00);

-- JSON_ARRAY builds a JSON array.
SELECT JSON_ARRAY("tag1", "tag2", "promo");
-- Returns: ["tag1","tag2","promo"]

INSERT INTO orders
SELECT json_parse(raw_json)
FROM   staging_table
WHERE  json_valid(raw_json);

-- Returns 1001
SELECT record['id']
FROM   orders
WHERE  record['id'] IS NOT NULL;

-- Returns "Molly"
SELECT record['customer']
FROM   orders;

-- Returns NULL (field does not exist)
SELECT record['email']
FROM   orders;

-- These two expressions return the same result:
SELECT record['id']                          FROM orders;
SELECT JSON_EXTRACT(record, 'strict $.id')   FROM orders;
-- Both return: 1001

-- JSON_EXTRACT returns a JSON value (with quotes).
SELECT JSON_EXTRACT(record, '$.customer')
FROM   orders;
-- Returns: "Molly"

-- GET_JSON_OBJECT returns a STRING value (no quotes).
SELECT GET_JSON_OBJECT(record, '$.customer')
FROM   orders;
-- Returns: Molly

{
  "name": "Molly",
  "phones": [
    { "phonetype": "work",  "phone#": "650-506-7000" },
    { "phonetype": "cell",  "phone#": "650-555-5555" }
  ]
}

Design considerations

• Use strict mode for column pruning. Lax mode does not trigger column pruning optimization, so queries in lax mode scan more data.

• Keep JSON documents small. Each JSON column is stored as a single column value. Large documents increase the memory and I/O cost per row.

• Validate before ingesting. Use json_valid() to filter malformed rows before calling json_parse().

• Check precision before inserting large numbers. JSON numbers are stored as BIGINT (integer part) and DOUBLE (decimal part). Numbers outside those ranges overflow.

• Plan your schema before creating tables. You cannot add a JSON column to an existing table. Design the table with the JSON column from the start.

End-to-end example

-- Enable the JSON type if your project was created before the feature was enabled.
SET odps.sql.type.json.enable=true;

-- Create a JSON table.
CREATE TABLE orders (record JSON);

-- Ingest from a staging STRING table, skipping malformed rows.
CREATE TABLE staging (raw_json STRING);
INSERT INTO staging VALUES ('{"id": 1001, "customer": "Molly", "amount": 299.50}');

INSERT INTO orders
SELECT json_parse(raw_json)
FROM   staging
WHERE  json_valid(raw_json);

-- Query all non-null records.
SELECT * FROM orders WHERE record IS NOT NULL;
-- Returns:
-- +--------------------------------------------------+
-- | record                                           |
-- +--------------------------------------------------+
-- | {"id":1001,"customer":"Molly","amount":299.5}    |
-- +--------------------------------------------------+

-- Access a specific field.
SELECT record['customer'] FROM orders WHERE record IS NOT NULL;
-- Returns:
-- +-----------+
-- | _c0       |
-- +-----------+
-- | "Molly"   |
-- +-----------+

What's next

• JSON functions — full reference for JSON_EXTRACT, GET_JSON_OBJECT, JSON_OBJECT, JSON_ARRAY, json_parse, json_valid, and related functions

• CAST expressions — type conversion behavior for JSON