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:
-
->
or with jsonGetElement() / jsonbGetElement(): To produce aorg.jooq.JSON
ororg.jooq.JSONB
value -
->>
or with jsonGetElementAsText() / jsonbGetElementAsText(): To produce ajava.lang.String
value
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)
ClickHouse
JSONExtractRaw( '[1,2,3]', (1 - 1) )
DB2
json_query( '[1,2,3]', ('$.' || CAST(1 AS varchar(3998))) )
MariaDB, MySQL
json_extract( '[1,2,3]', concat( concat( '$[', CAST(1 AS char) ), ']' ) )
Oracle
json_query('[1,2,3]', '$[1]')
Snowflake
get(parse_json('[1,2,3]'), 1)
SQLite
('[1,2,3]'->1)
Trino
json_extract( json_parse('[1,2,3]'), (('$[' || CAST(1 AS varchar)) || ']') )
ASE, Access, Aurora MySQL, BigQuery, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, Sybase, Teradata, Vertica
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!