Use JSON_TUPLE to extract multiple values from a JSON string in a single pass - MaxCompute

Extracts multiple values from a JSON string in a single pass, based on a set of specified keys.

Syntax

string json_tuple(string <json>, string <key1>, string <key2>, ...)

Parameters

Parameter	Required	Type	Description
json	Yes	STRING	A valid JSON string.
key1, key2, ...	Yes	STRING	One or more keys that specify the paths of JSON objects to extract. You can specify multiple keys, separated by commas. MaxCompute parses JSON objects by using `.` (dot notation) or `['']` (bracket notation). If a key contains a period (`.`), use bracket notation `['']`. Keys cannot start with a dollar sign (`$`).

Return value

A value of the STRING type is returned.

Usage notes

If the JSON string is empty or invalid, null is returned.
If a key is empty or invalid, null is returned for that key.
If the JSON string is valid and a key exists, the corresponding string value is returned.
This function can parse JSON strings that contain Unicode characters (including CJK characters), multiple levels of nesting, and arrays with complex nesting.
JSON_TUPLE is a user-defined table-valued function (UDTF). To select other columns alongside JSON_TUPLE results, you must use the LATERAL VIEW clause.
The parsing action of JSON_TUPLE is equivalent to GET_JSON_OBJECT with set odps.sql.udf.getjsonobj.new=true;. When you need to extract multiple values from the same JSON string, JSON_TUPLE is more efficient because it parses the JSON string only once, whereas multiple GET_JSON_OBJECT calls parse the JSON string once per call.

Examples

Extract multiple keys from a JSON string

SELECT json_tuple('{"name":"Alice","age":"30","city":"Shanghai"}', 'name', 'age', 'city')
AS (name, age, city);

The following result is returned:

name	age	city
Alice	30	Shanghai

Use JSON_TUPLE with LATERAL VIEW

When you need to select other columns from a table alongside JSON_TUPLE results, use the LATERAL VIEW clause:

SELECT t.id, jt.name, jt.age
FROM my_table t
LATERAL VIEW json_tuple(t.json_col, 'name', 'age') jt AS name, age;

Extract nested JSON values

Use dot notation to access values in nested JSON objects:

SELECT json_tuple('{"user":{"name":"Alice","role":"admin"}}', 'user.name', 'user.role')
AS (user_name, user_role);

Handle missing keys

If a key does not exist in the JSON string, null is returned for that key:

SELECT json_tuple('{"a":"1","b":"2"}', 'a', 'c') AS (val_a, val_c);

The following result is returned:

val_a	val_c
1	null

Migrate from GET_JSON_OBJECT to JSON_TUPLE

If you use multiple GET_JSON_OBJECT calls on the same JSON string, you can improve performance by rewriting the query to use JSON_TUPLE:

-- Before: parses the JSON string three times
SELECT
  get_json_object(t.json_col, '$.name') AS name,
  get_json_object(t.json_col, '$.age') AS age,
  get_json_object(t.json_col, '$.city') AS city
FROM my_table t;

-- After: parses the JSON string once
SELECT t.*, jt.name, jt.age, jt.city
FROM my_table t
LATERAL VIEW json_tuple(t.json_col, 'name', 'age', 'city') jt AS name, age, city;

Related functions

JSON_TUPLE is a complex type function and a string function. For more information, see:

Complex type functions: Functions that process data of complex data types such as ARRAY, MAP, STRUCT, and JSON.
String functions: Functions related to string searches and conversion.