Postgres JSON_EXISTS() Function

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.

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

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
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
Path expressions:
- Use lax mode (default) for optional paths
- Leverage path variables with PASSING clause for dynamic checks