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:
-
->or with jsonGetElement() / jsonbGetElement(): To produce aorg.jooq.JSONororg.jooq.JSONBvalue -
->>or with jsonGetElementAsText() / 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)
-- 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)) || ']')
)
-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, DUCKDB, EXASOL, FIREBIRD, H2, HANA, HSQLDB, INFORMIX, MEMSQL, REDSHIFT,
-- SQLDATAWAREHOUSE, SQLSERVER, SYBASE, TERADATA, VERTICA
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!