json_tuple
table-valued generator function
Applies to: Databricks SQL Databricks Runtime
Returns multiple JSON objects as a tuple.
Arguments
jsonStr
: ASTRING
expression with well-formed JSON.pathN
: ASTRING
literal with a JSON path expression.
Returns
A single row composed of the JSON objects.
If any object cannot be found, NULL
is returned for that object.
Applies to: Databricks Runtime 12.1 and earlier:
json_tuple
can only be placed in theSELECT
list as the root of an expression or following a LATERAL VIEW. When placing the function in theSELECT
list there must be no other generator function in the sameSELECT
list or UNSUPPORTED_GENERATOR.MULTI_GENERATOR is raised.Applies to: Databricks SQL Databricks Runtime 12.2 LTS and above:
Invocation from the LATERAL VIEW clause or the
SELECT
list is deprecated. Instead, invokejson_tuple
as a table_reference.
Examples
Applies to: Databricks Runtime 12.1 and earlier:
> SELECT json_tuple('{"a":1, "b":2}', 'a', 'b'), 'Spark SQL';
1 2 Spark SQL
> SELECT json_tuple('{"a":1, "b":2}', 'a', 'c'), 'Spark SQL';
1 NULL Spark SQL
> SELECT json_tuple('{"a":1, "b":2}', 'a', 'c'), json_tuple('{"c":1, "d":2}', 'c', 'd'), 'Spark SQL';
Error: UNSUPPORTED_GENERATOR.MULTI_GENERATOR
Applies to: Databricks SQL Databricks Runtime 12.2 LTS and above:
> SELECT j.*, 'Spark SQL' FROM json_tuple('{"a":1, "b":2}', 'a', 'b') AS j;
1 2 Spark SQL
> SELECT j.*, 'Spark SQL' FROM json_tuple('{"a":1, "b":2}', 'a', 'c') AS j;
1 NULL Spark SQL
> SELECT j1.*, j2.*, 'Spark SQL'
FROM json_tuple('{"a":1, "b":2}', 'a', 'c') AS j1,
json_tuple('{"c":1, "d":2}', 'c', 'd') AS j2;