JSON_TUPLE command format, parameters, and return value-MaxCompute(MaxCompute)-阿里云帮助中心

This function extracts strings from a standard JSON string based on a set of input keys (key1,key2,...).

Command format

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

Parameters

json: Required. A standard JSON string of the STRING type.
key: Required. A STRING that specifies the path in the JSON object. You can specify multiple keys. A key cannot start with a dollar sign ($). MaxCompute supports using a period (.) or square brackets with single quotation marks (['']) to parse JSON. If a JSON key contains a period (.), use [''] instead.

Return value

Returns a value of the STRING type.

Note

If the JSON string is empty or invalid, or if a key is empty or invalid, NULL is returned.
If the JSON string is valid and the key exists, the corresponding string is returned.
This function can parse JSON data that contains Chinese characters, multilayer nesting, and nested arrays.
The parsing behavior is the same as that of the GET_JSON_OBJECT function after you run the set odps.sql.udf.getjsonobj.new=true; command. To parse the same JSON string multiple times, JSON_TUPLE is more efficient than calling GET_JSON_OBJECT multiple times. This is because you can specify multiple keys at once, and the JSON string is parsed only once.
JSON_TUPLE is a user-defined table-valued function (UDTF). Use it with LATERAL VIEW when you need to select other columns.

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

The JSON_TUPLE function is classified as a complex type function or a string function.

For more information about functions that process complex data types, such as ARRAY, MAP, STRUCT, and JSON, see Complex type functions.
For more information about functions that find strings and transform string formats, see String functions.