SQL

Querying and Updating JSONB Data in PostgreSQL

Master how to query, extract, and update nested data within JSONB columns in PostgreSQL, offering immense flexibility for semi-structured data in modern web applications and APIs.

-- Select data from JSONB
SELECT
    id,
    data->>'productName' AS product_name,
    data->'details'->>'color' AS product_color
FROM
    products_jsonb
WHERE
    data->'details'->>'size' = 'Large'
    AND (data->'tags' ? 'electronics'); -- Check if array contains element

-- Update data in JSONB (add/modify a key)
UPDATE products_jsonb
SET
    data = jsonb_set(data, '{details,material}', '"Leather"', true)
WHERE
    id = 1;

-- Update data in JSONB (remove a key)
UPDATE products_jsonb
SET
    data = data - 'tags'
WHERE
    id = 2;

How it works: This snippet illustrates common operations with PostgreSQL's `JSONB` data type, which is highly efficient for storing and querying semi-structured JSON data. It shows how to extract values using `->` (for JSON object) and `->>` (for text result), filter records based on JSON values, and check for array containment with `?`. Additionally, it demonstrates updating `JSONB` data by adding or modifying nested keys using `jsonb_set` and removing keys using the `data - 'key'` operator. This is crucial for applications that leverage flexible schema designs, common in microservices and API development.

Querying and Updating JSONB Data in PostgreSQL

Need help integrating this into your project?