JSON_EXISTS predicate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The JSON_EXISTS predicate can be used to check whether a JSON path expression produces a value within a JSON document (see also the JSON_VALUE function)
SELECT 1
FROM dual
WHERE json_exists('{"a":1}', '$.a')
create.selectOne()
.where(jsonExists(val(JSON.valueOf("{\"a\":1}")), "$.a"))
.fetch();
Dialect support
This example using jOOQ:
jsonExists(val(json("{\"a\":1}")), "$.a")
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB
JSON_EXISTS(CAST('{"a":1}' AS json), '$.a')
BigQuery
json_query('{"a":1}', '$.a') IS NOT NULL
ClickHouse, DB2, MariaDB, Oracle
JSON_EXISTS('{"a":1}', '$.a')
DuckDB
('{"a":1}'->'$.a') IS NOT NULL
MySQL
json_contains_path('{"a":1}', 'one', '$.a')
Postgres
jsonb_path_exists(CAST('{"a":1}' AS jsonb), CAST('$.a' AS jsonpath))
SQLite
json_type('{"a":1}', '$.a') IS NOT NULL
ASE, Access, Aurora MySQL, Derby, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
/* 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!