layout: global title: JSON_EXISTS displayTitle: JSON_EXISTS license: | Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

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');

Related Statements