Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 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'
  )
) AS t
create.select()
      .from(jsonTable(
          json("[{\"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 |
+----+---+----+----+

Dialect support

This example using jOOQ:

selectFrom(jsonTable(json("[{\"a\":5,\"b\":{\"x\":10}}]"), "$[*]").column("id").forOrdinality().column("a", INTEGER).column("x", INTEGER).path("$.b.x").as("t"))

Translates to the following dialect specific expressions:

DB2

SELECT t.id, t.a, t.x
FROM JSON_TABLE(
  '[{"a":5,"b":{"x":10}}]',
  '$[*]'
  COLUMNS (
    id FOR ORDINALITY,
    a integer,
    x integer PATH '$.b.x'
  )
  ERROR ON ERROR
) t

Hana

SELECT t.id, t.a, t.x
FROM JSON_TABLE(
  '[{"a":5,"b":{"x":10}}]',
  '$[*]'
  COLUMNS (
    id FOR ORDINALITY,
    a integer PATH '$.a',
    x integer PATH '$.b.x'
  )
) t

MariaDB, MySQL

SELECT t.id, t.a, t.x
FROM JSON_TABLE(
  '[{"a":5,"b":{"x":10}}]',
  '$[*]'
  COLUMNS (
    id FOR ORDINALITY,
    a int PATH '$.a',
    x int PATH '$.b.x'
  )
) t

Oracle

SELECT t.id, t.a, t.x
FROM JSON_TABLE(
  '[{"a":5,"b":{"x":10}}]',
  '$[*]'
  COLUMNS (
    id FOR ORDINALITY,
    a number(10),
    x number(10) PATH '$.b.x'
  )
) t

Postgres, YugabyteDB

SELECT t.id, t.a, t.x
FROM (
  SELECT
    o id,
    cast((jsonb_path_query_first(j, cast('$.a' as jsonpath))->>0) as INT) a,
    cast((jsonb_path_query_first(j, cast('$.b.x' as jsonpath))->>0) as INT) x
  FROM jsonb_path_query(CAST('[{"a":5,"b":{"x":10}}]' AS jsonb), cast('$[*]' as jsonpath)) WITH ORDINALITY AS t(j, o)
) t

SQLServer

SELECT t.id, t.a, t.x
FROM (
  SELECT
    row_number() OVER (ORDER BY (
      SELECT 1
    )) id,
    a,
    x
  FROM openjson('[{"a":5,"b":{"x":10}}]', '$[*]') WITH (
    a int,
    x int '$.b.x'
  ) t
) t

ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, CockroachDB, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo