Available in versions: Dev (3.20)
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_KEY_EXISTS
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The JSON_KEY_EXISTS function is a non-standard JSON function inspired by PostgreSQL's JSONB_EXISTS function or ? operator, which can be used to check for the existence of a key in a JSON object.
SELECT json_key_exists(json_object( KEY 'a' VALUE 1 ), 'a')
create.select(jsonKeyExists(jsonObject(
key("a").value(1)), "a"))
.fetch();
The result would look like this:
+-----------------+ | json_key_exists | +-----------------+ | true | +-----------------+
This is simply a less powerful version of the standard SQL JSON_EXISTS predicate, which can work with JSON paths, not just key names.
Dialect support
This example using jOOQ:
jsonKeyExists(jsonObject(key("a").value(1)), "a")
Translates to the following dialect specific expressions:
Aurora Postgres, Postgres, YugabyteDB
(CAST(json_build_object('a', CAST(1 AS int)) AS jsonb) ?? 'a')
BigQuery
json_query(
json_object('a', 1),
('$.' || 'a')
) IS NOT NULL
ClickHouse
JSONExtractRaw(
toJSONString(map('a', 1)),
'a'
) IS NOT NULL
CockroachDB
(json_build_object('a', CAST(1 AS int4)) ?? 'a')
DB2
coalesce(
json_query(
json_object(KEY 'a' VALUE 1),
('$.' || CAST('a' AS varchar(3998)))
),
nvl2(
json_query(
json_object(KEY 'a' VALUE 1),
('$.' || CAST('a' AS varchar(3998))) EMPTY ARRAY ON EMPTY
),
NULL,
'null'
)
) IS NOT NULL
DuckDB, SQLite
(json_object('a', 1)->'a') IS NOT NULL
MariaDB, MySQL
json_extract(
json_object('a', 1),
concat('$.', 'a')
) IS NOT NULL
Oracle
json_query( json_object(KEY 'a' VALUE 1), '$.a' ) IS NOT NULL
Snowflake
get(
object_construct_keep_null('a', 1),
'a'
) IS NOT NULL
Trino
json_extract(
CAST(map_from_entries(ARRAY[row(
'a',
CAST(1 AS json)
)]) AS json),
('$.' || 'a')
) IS NOT NULL
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!