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 withjsonGetElement()/jsonbGetElement(): To produce aorg.jooq.JSONororg.jooq.JSONBvalue -
->>or withjsonGetElementAsText()/jsonbGetElementAsText(): To produce ajava.lang.Stringvalue
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
Feedback
Do you have any feedback about this page? We'd love to hear it!