The JSON_EXISTS() function introduced 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