SQL

Extract and Filter Data from JSON Columns (PostgreSQL)

Master how to query and extract specific values from JSON data stored directly in database columns using PostgreSQL's native JSON operators, enabling flexible data retrieval and filtering.

SELECT
    id,
    data->>'userName' AS user_name,
    (data->'address'->>'city') AS user_city,
    (data->'settings'->>'emailNotifications')::boolean AS email_notifications_enabled
FROM
    user_preferences
WHERE
    data->>'status' = 'active'
    AND (data->'address'->>'country') = 'USA';

How it works: This SQL query demonstrates how to extract and filter data stored within a JSON column named `data` in a PostgreSQL database. It uses the `->>` operator to extract text values from JSON keys and `->` for nested JSON objects, allowing you to access properties like `userName`, `city`, `emailNotifications`, `status`, and `country` directly. The `::boolean` cast converts a text value to a boolean for type-safe comparisons.

Extract and Filter Data from JSON Columns (PostgreSQL)

Need help integrating this into your project?