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 with jsonGetAttribute() / jsonbGetAttribute(): To produce aorg.jooq.JSONororg.jooq.JSONBvalue -
->>or with jsonGetAttributeAsText() / 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')
-- 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'
)
)
-- MARIADB, MYSQL
json_extract(
'{"a":1}',
concat('$.', 'a')
)
-- ORACLE
json_query(
'{"a":1}',
'$.a'
)
-- SNOWFLAKE
get(parse_json('{"a":1}'), 'a')
-- SQLITE
('{"a":1}'->'a')
-- TRINO
json_extract(
json_parse('{"a":1}'),
('$.' || 'a')
)
-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, 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!