JSON_TABLE
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some dialects ship with a built-in standard SQL table-valued function called JSON_TABLE, which can be used to unnest a JSON data structure into a SQL table.
SELECT *
FROM json_table(
'[{"a":5,"b":{"x":10}},{"a":7,"b":{"y":20}}]',
'$[*]'
COLUMNS (
id FOR ORDINALITY,
a INT,
x INT PATH '$.b.x',
y INT PATH '$.b.y'
)
) AS t
create.select()
.from(jsonTable(
JSON.valueOf("[{\"a\":5,\"b\":{\"x\":10}},"
+ "{\"a\":7,\"b\":{\"y\":20}}]"),
"$[*]")
.column("id").forOrdinality()
.column("a", INTEGER)
.column("x", INTEGER).path("$.b.x")
.column("y", INTEGER).path("$.b.y")
.as("t"))
.fetch();
The result would look like this:
+----+---+----+----+ | ID | A | X | Y | +----+---+----+----+ | 1 | 5 | 10 | | | 2 | 7 | | 20 | +----+---+----+----+
Feedback
Do you have any feedback about this page? We'd love to hear it!