json_tuple table-valued generator function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns multiple JSON objects as a tuple.

Syntax 

json_tuple(jsonStr, path1 [, ...] )

Arguments

  • jsonStr: A STRING expression with well-formed JSON.

  • pathN: A STRING literal with a JSON path.

Returns

A single row composed of the JSON objects.

If any object cannot be found, NULL is returned for that object.

  • Applies to: check marked yes Databricks Runtime 12.1 and earlier:

    json_tuple can only be placed in the SELECT list as the root of an expression or following a LATERAL VIEW. When placing the function in the SELECT list there must be no other generator function in the same SELECT list or UNSUPPORTED_GENERATOR.MULTI_GENERATOR is raised.

  • Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above:

    Invocation from the LATERAL VIEW clause or the SELECT list is deprecated. Instead, invoke json_tuple as a table_reference.

Examples

Applies to: check marked yes 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: check marked yes Databricks SQL check marked yes 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;