JSON_EXISTS

Description

The JSON_EXISTS predicate tests whether a SQL/JSON path matches at least one item in a JSON document, returning a BOOLEAN. This is the SQL-standard (SQL:2016) way to test for the presence of a JSON value, and is commonly used to migrate queries from other systems such as Oracle, DB2, and PostgreSQL.

Unlike get_json_object(json_expr, path) IS NOT NULL, JSON_EXISTS distinguishes a path that is present but whose value is JSON null (which is true) from a path that is absent (which is false).

Syntax

JSON_EXISTS ( json_expr, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ] )

Parameters

json_expr

An expression that evaluates to a STRING containing the JSON document. A NULL input yields NULL (SQL Unknown), regardless of the ON ERROR clause.
path

A constant SQL/JSON path literal (for example '$.a.b', '$.tags[0]', or '$.a[*].b'). Paths are evaluated in lax mode, matching Oracle and PostgreSQL: array wildcards ([*]) and member wildcards (.* / ['*']) are supported, and arrays are auto-wrapped/unwrapped (a member, index, or wildcard step applied to an array is applied to each element, and a non-array value is treated as a single-element array). A structural mismatch is a non-match, not an error. A syntactically invalid path is rejected during analysis.
**{ TRUE FALSE UNKNOWN ERROR } ON ERROR**

Controls the result when json_expr is not a single well-formed JSON value (malformed input, or a valid value followed by trailing content). TRUE, FALSE, and UNKNOWN produce that value (UNKNOWN is a BOOLEAN NULL); ERROR raises an error. The default is FALSE ON ERROR.

Result

The path matches at least one item (including a match whose value is JSON null) → true.
The path matches nothing → false.
json_expr is SQL NULL → NULL.
json_expr is not a single well-formed JSON value → the ON ERROR behavior.

A structural mismatch is treated as “no match” (false), not an error – for example reading an absent key, reading a key from a scalar, an out-of-range array index, or [*] over an empty array.

Examples

SELECT json_exists('{"a":{"b":1}}', '$.a.b') AS matched;
+-------+
|matched|
+-------+
|   true|
+-------+

-- Present but JSON null -> true; absent -> false
SELECT json_exists('{"a":null}', '$.a') AS present_null,
       json_exists('{"a":1}', '$.b')    AS absent;
+------------+------+
|present_null|absent|
+------------+------+
|        true| false|
+------------+------+

-- NULL input -> NULL (Unknown), regardless of the ON ERROR clause
SELECT json_exists(CAST(NULL AS STRING), '$.a' TRUE ON ERROR) AS r;
+----+
|   r|
+----+
|NULL|
+----+

-- Malformed input follows the ON ERROR clause (default FALSE)
SELECT json_exists('not json', '$.a')                 AS default_false,
       json_exists('not json', '$.a' TRUE ON ERROR)    AS true_on_error,
       json_exists('not json', '$.a' UNKNOWN ON ERROR) AS unknown_on_error;
+-------------+-------------+----------------+
|default_false|true_on_error|unknown_on_error|
+-------------+-------------+----------------+
|        false|         true|            NULL|
+-------------+-------------+----------------+

-- Lax wildcards: [*] is true iff the array has elements; auto-unwrap applies a step to each element
SELECT json_exists('{"a":[1,2]}', '$.a[*]')             AS has_elems,
       json_exists('{"a":[]}', '$.a[*]')                AS empty_array,
       json_exists('{"a":[{"b":1},{"c":2}]}', '$.a[*].b') AS any_elem_has_b;
+---------+-----------+--------------+
|has_elems|empty_array|any_elem_has_b|
+---------+-----------+--------------+
|     true|      false|          true|
+---------+-----------+--------------+

-- Use as a predicate in WHERE
SELECT id FROM docs WHERE json_exists(doc, '$.address.city');