New versions: Dev (3.14)

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'
  )
)
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"))
      .fetch();

The result would look like this:

+----+---+----+----+
| ID | A |  X |  Y |
+----+---+----+----+
|  1 | 5 | 10 |    |
|  2 | 7 |    | 20 |
+----+---+----+----+
The jOOQ Logo