JSON_TUPLE function syntax, parameters, and examples - MaxCompute

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 specifying the JSON paths to extract, separated by commas. MaxCompute parses paths by using `.` (dot notation) or `['']` (bracket notation). If a key contains a period (`.`), use bracket notation `['']`. Keys cannot start with `$`.

Return value

Returns a STRING value.

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 the key exists, the corresponding string is returned.
Supports Unicode characters (including CJK), multi-level nesting, and complex arrays.
JSON_TUPLE is a user-defined table-valued function (UDTF). To select other columns alongside JSON_TUPLE results, use the LATERAL VIEW clause.
JSON_TUPLE parses the same way as GET_JSON_OBJECT with set odps.sql.udf.getjsonobj.new=true;, but JSON_TUPLE is more efficient for extracting multiple values because it parses the JSON string once instead of once per GET_JSON_OBJECT 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);

Output:

name	age	city
Alice	30	Shanghai

Use JSON_TUPLE with LATERAL VIEW

To select other table columns alongside JSON_TUPLE results, use LATERAL VIEW:

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 nested values:

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

Handle missing keys

A missing key returns null:

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

Output:

val_a	val_c
1	null

Migrate from GET_JSON_OBJECT to JSON_TUPLE

Rewrite multiple GET_JSON_OBJECT calls as a single JSON_TUPLE call for better performance:

-- 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 both a complex type function and a string function:

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.