JSON object attribute access with -> or ->>
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
JSON object attributes can be accessed by key as follows:
SELECT
'{"a":1}'::json->'a'
create.select(jsonGetAttribute(json("{\"a\":1}"), "a"))
.fetch();
The result would look like this:
+--------------------+ | json_get_attribute | +--------------------+ | 1 | +--------------------+
The API is PostgreSQL inspired, and as such, there are two ways of accessing attributes:
-
->or withjsonGetAttribute()/jsonbGetAttribute(): To produce aorg.jooq.JSONororg.jooq.JSONBvalue -
->>or withjsonGetAttributeAsText()/jsonbGetAttributeAsText(): To produce ajava.lang.Stringvalue
Dialect support
This example using jOOQ:
jsonGetAttribute(json("{\"a\":1}"), "a")
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, Postgres, YugabyteDB
(CAST('{"a":1}' AS json)->'a')
BigQuery
json_query(
'{"a":1}',
('$.' || 'a')
)
ClickHouse
JSONExtractRaw('{"a":1}', 'a')
DB2
coalesce(
json_query(
'{"a":1}',
('$.' || CAST('a' AS varchar(3998)))
),
nvl2(
json_query(
'{"a":1}',
('$.' || CAST('a' AS varchar(3998))) EMPTY ARRAY ON EMPTY
),
NULL,
'null'
)
)
DuckDB, SQLite
('{"a":1}'->'a')
MariaDB, MySQL
json_extract(
'{"a":1}',
concat('$.', 'a')
)
Oracle
json_query(
'{"a":1}',
'$.a'
)
Snowflake
get(parse_json('{"a":1}'), 'a')
Trino
json_extract(
json_parse('{"a":1}'),
('$.' || 'a')
)
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!