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, Databricks, Derby, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, Sybase, Teradata, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!