JSON_TUPLE

Description

The JSON_TUPLE function is used to parse key-value pairs in a JSON string and extract the values corresponding to specified keys. This function takes a JSON string as input and extracts the corresponding values based on the provided list of keys (c1, c2, ... cN). If the JSON string fails to parse, the function will return a result with all columns as null; if a key does not exist in the JSON object, the corresponding column will be filled with null. It is important to note that the JSON_TUPLE function can only parse top-level keys of the JSON object.

This function can be used directly or in conjunction with the LATERAL VIEW clause to extract JSON data in more complex queries.

Parameters

• str (string): The JSON string to be parsed, which should be a JSON object.
• c1, c2, ... cN (string): The keys in the JSON object to be extracted.

Return Type

• Returns a string array containing the extracted values, with the number of elements matching the length of the provided key list.

Usage Example

Example 1: Basic Usage

SELECT json_tuple('{"x" : "1", "y" : 2}', 'x', 'z');
+------+------+
| col0 | col1 |
+------+------+
| 1    |      |
+------+------+

The above query attempts to extract the values of keys 'x' and 'z'. Since the 'z' key does not exist, the position corresponding to 'z' in the returned result is empty.

Example 2: Using with LATERAL VIEW

SELECT a, b, c FROM VALUES ('{"a" : 1.0, "b" : 2}') as t(word) LATERAL VIEW json_tuple(word, 'a', 'b', 'c') lv as a, b, c;
+-----+---+---+
|  a  | b | c |
+-----+---+---+
| 1.0 | 2 |   |
+-----+---+---+

In this example, we first create a table containing JSON strings, and then use LATERAL VIEW and the json_tuple function to extract the values of the 'a', 'b', and 'c' keys. Since the 'c' key does not exist, its corresponding value is empty.