JSON_TUPLE

Syntax

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

Parameters

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

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

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

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

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

-- 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;

Examples

-- Create a table named school.
create table school (id string, json string);
-- Insert data into the table.
insert into school (id, json) values ("1", "{
\"school\": \"Hupan University\", \"Address\":\"Hangzhou\", \"SchoolRank\": \"00\",
\"Class1\":{\"Student\":[{\"studentId\":1,\"scoreRankIn3Year\":[1,2,[3,2,6]]},
{\"studentId\":2,\"scoreRankIn3Year\":[2,3,[4,3,1]]}]}}");

• Example 1: Fetch JSON object information.

  select json_tuple(school.json,"SchoolRank","Class1") as (item0, item1) from school;
  -- This statement is equivalent to the following statement.
  select get_json_object(school.json,"$.SchoolRank") item0,get_json_object(school.json,"$.Class1") item1 from school;
  
  -- The following result is returned.
  +-------+--------------------------------------------------------------------------------------------------------------+
  | item0 | item1                                                                                                        |
  +-------+--------------------------------------------------------------------------------------------------------------+
  | 00    | {"Student":[{"studentId":1,"scoreRankIn3Year":[1,2,[3,2,6]]},{"studentId":2,"scoreRankIn3Year":[2,3,[4,3,1]]}]} |
  +-------+--------------------------------------------------------------------------------------------------------------+

• Example 2: Use [''] to fetch JSON object information.

  select json_tuple(school.json,"school","['Class1'].Student") as (item0, item2) from school where id=1;
  -- The following result is returned.
  +------------------+--------------------------------------------------------------------------------------------------------------+
  | item0            | item2                                                                                                        |
  +------------------+--------------------------------------------------------------------------------------------------------------+
  | Hupan University | [{"studentId":1,"scoreRankIn3Year":[1,2,[3,2,6]]},{"studentId":2,"scoreRankIn3Year":[2,3,[4,3,1]]}]             |
  +------------------+--------------------------------------------------------------------------------------------------------------+

• Example 3: Parse JSON data that contains Chinese characters.

  select json_tuple(school.json,"school","Address") as (item0,item1) from school;
  -- The following result is returned.
  +------------------+----------+
  | item0            | item1    |
  +------------------+----------+
  | Hupan University | Hangzhou |
  +------------------+----------+

• Example 4: Parse JSON data with multilayer nesting.

  select sc.Id, q.item0, q.item1
  from school sc lateral view json_tuple(sc.json,"Class1.Student[*].studentId","Class1.Student[0].scoreRankIn3Year") q as item0,item1;
  
  -- The following result is returned.
  +------------+-------+---------------+
  | id         | item0 | item1         |
  +------------+-------+---------------+
  | 1          | [1,2] | [1,2,[3,2,6]] |
  +------------+-------+---------------+

• Example 5: Parse JSON data that contains nested arrays.

  select sc.Id, q.item0, q.item1
  from school sc lateral view json_tuple(sc.json,"Class1.Student[0].scoreRankIn3Year[2]","Class1.Student[0].scoreRankIn3Year[2][1]") q as item0,item1;
  -- The following result is returned.
  +------------+---------+-------+
  | id         | item0   | item1 |
  +------------+---------+-------+
  | 1          | [3,2,6] | 2     |
  +------------+---------+-------+

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.