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.JSON
ororg.jooq.JSONB
value -
->>
or with jsonGetAttributeAsText() / jsonbGetAttributeAsText(): To produce ajava.lang.String
value
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, 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!