Available in versions: Dev (3.20) | Latest (3.19) | 3.18

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

JSON array element access with -> or ->>

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

JSON array elements can be accessed by (zero based) index as follows:

SELECT
  '[1,2,3]'::json->1
create.select(jsonGetElement(json("[1,2,3]"), 1))
      .fetch();

The result would look like this:

+------------------+
| json_get_element |
+------------------+
| 2                |
+------------------+

The API is PostgreSQL inspired, and as such, there are two ways of accessing elements:

Dialect support

This example using jOOQ:

jsonGetElement(json("[1,2,3]"), 1)

Translates to the following dialect specific expressions:

Aurora Postgres, CockroachDB, Postgres, YugabyteDB

(CAST('[1,2,3]' AS json)->1)

BigQuery

json_query(
  '[1,2,3]',
  ('$[' || CAST(1 AS string) || ']')
)

ClickHouse

JSONExtractRaw(
  '[1,2,3]',
  (1 + 1)
)

DB2

json_query(
  '[1,2,3]',
  ('$.' || CAST(1 AS varchar(3998)))
)

DuckDB, SQLite

('[1,2,3]'->1)

MariaDB, MySQL

json_extract(
  '[1,2,3]',
  concat(
    '$[',
    CAST(1 AS char),
    ']'
  )
)

Oracle

json_query('[1,2,3]', '$[1]')

Snowflake

get(parse_json('[1,2,3]'), 1)

Trino

json_extract(
  json_parse('[1,2,3]'),
  ('$[' || CAST(1 AS varchar) || ']')
)

ASE, Access, Aurora MySQL, Derby, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, Sybase, Teradata, Vertica

/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL 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