Query and Manipulate JSON Data in SQL Databases (PostgreSQL)

-- Example: Querying and extracting values from a JSONB column
SELECT
    id,
    data ->> 'name' AS customer_name,
    data -> 'address' ->> 'city' AS customer_city,
    data -> 'items' -> 0 ->> 'product_id' AS first_item_product_id
FROM
    orders_with_json
WHERE
    data ->> 'status' = 'pending'
    AND (data -> 'address' ->> 'zip_code')::int > 10000;

-- Example: Updating a value within a JSONB column
UPDATE orders_with_json
SET
    data = jsonb_set(data, '{status}', '"completed"', false)
WHERE
    id = 101;

How it works: Modern SQL databases like PostgreSQL provide robust functions and operators for storing, querying, and manipulating JSON (or JSONB) data directly within columns. This allows for greater schema flexibility, enabling you to store semi-structured data alongside traditional relational data. Operators like `->` (extract JSON object field or array element) and `->>` (extract JSON object field or array element as text) are commonly used for retrieval, while functions like `jsonb_set()` are used for updates.