The JSON_EXISTS() function in PostgreSQL 17 provides a powerful way to check for the existence of values within JSON data using SQL/JSON path expressions. This function is particularly useful for validating JSON structure and implementing conditional logic based on the presence of specific JSON elements.

Use JSON_EXISTS() when you need to:

  • Validate the presence of specific JSON paths
  • Implement conditional logic based on JSON content
  • Filter JSON data based on complex conditions
  • Verify JSON structure before processing

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Sign Up

Function signature

The JSON_EXISTS() function uses the following syntax:

JSON_EXISTS(  context_item, -- JSON/JSONB input  path_expression -- SQL/JSON path expression  [ PASSING { value AS varname } [, ...] ]  [{ TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ] ) → boolean

Parameters:

  • context_item: JSON or JSONB input to evaluate
  • path_expression: SQL/JSON path expression to check
  • PASSING: Optional clause to pass variables for use in the path expression
  • ON ERROR: Controls behavior when path evaluation fails (defaults to FALSE)

Example usage

Let's explore various ways to use the JSON_EXISTS() function with different scenarios and options.

Basic existence checks

-- Check if a simple key exists SELECT JSON_EXISTS('{"name": "Alice", "age": 30}', '$.name');
# | json_exists -------------- 1 | t
-- Check for a nested key SELECT JSON_EXISTS(  '{"user": {"details": {"email": "alice@example.com"}}}',  '$.user.details.email' );
# | json_exists -------------- 1 | t

Array operations

-- Check if array contains any elements SELECT JSON_EXISTS('{"numbers": [1,2,3,4,5]}', '$.numbers[*]');
# | json_exists -------------- 1 | t
-- Check for specific array element SELECT JSON_EXISTS('{"tags": ["postgres", "json", "database"]}', '$.tags[3]');
# | json_exists -------------- 1 | f

Conditional checks

-- Check for values meeting a condition SELECT JSON_EXISTS(  '{"scores": [85, 92, 78, 95]}',  '$.scores[*] ? (@ > 90)' );
# | json_exists -------------- 1 | t

Using PASSING clause

-- Check using a variable SELECT JSON_EXISTS(  '{"temperature": 25}',  'strict $.temperature ? (@ > $threshold)'  PASSING 30 AS threshold );
# | json_exists -------------- 1 | f

Error handling

-- Default behavior (returns FALSE) SELECT JSON_EXISTS(  '{"data": [1,2,3]}',  'strict $.data[5]' );
# | json_exists -------------- 1 | f
-- Using ERROR ON ERROR SELECT JSON_EXISTS(  '{"data": [1,2,3]}',  'strict $.data[5]'  ERROR ON ERROR );
ERROR: jsonpath array subscript is out of bounds (SQLSTATE 22033)
-- Using UNKNOWN ON ERROR SELECT JSON_EXISTS(  '{"data": [1,2,3]}',  'strict $.data[5]'  UNKNOWN ON ERROR );
# | json_exists -------------- 1 |

Practical applications

Data validation

-- Validate required fields before insertion CREATE TABLE user_profiles (  id SERIAL PRIMARY KEY,  data JSONB NOT NULL,  CONSTRAINT valid_profile CHECK (  JSON_EXISTS(data, '$.email') AND  JSON_EXISTS(data, '$.username')  ) );  -- This insert will succeed INSERT INTO user_profiles (data) VALUES (  '{"email": "user@example.com", "username": "user123"}'::jsonb );  -- This insert will fail INSERT INTO user_profiles (data) VALUES (  '{"username": "user123"}'::jsonb );
ERROR: new row for relation "user_profiles" violates check constraint "valid_profile" (SQLSTATE 23514)

Conditional queries

-- Filter records based on JSON content SELECT * FROM user_profiles WHERE JSON_EXISTS(  data,  '$.preferences.notifications ? (@ == true)' );

Best practices

  1. Error handling:

    • Use appropriate ON ERROR clauses based on your requirements
    • Consider UNKNOWN ON ERROR for nullable conditions
    • Use ERROR ON ERROR when validation is critical

  2. Performance optimization:

    • Create GIN indexes on JSONB columns for better performance
    • Use strict mode when path is guaranteed to exist
    • Combine with other JSON functions for complex operations

  3. Path expressions:

    • Use lax mode (default) for optional paths
    • Leverage path variables with PASSING clause for dynamic checks

Learn more