Query and Filter Data within JSON Columns

-- PostgreSQL JSON Query (using JSONB type for performance)
SELECT
    id,
    data->>'product_name' AS product_name,
    data->'details'->>'category' AS product_category
FROM
    products
WHERE
    data->>'status' = 'available'
    AND (data->'price')::numeric > 100;

-- MySQL JSON Query (using JSON type)
SELECT
    id,
    JSON_UNQUOTE(JSON_EXTRACT(data, '$.product_name')) AS product_name,
    JSON_UNQUOTE(JSON_EXTRACT(data, '$.details.category')) AS product_category
FROM
    products
WHERE
    JSON_EXTRACT(data, '$.status') = '"available"'
    AND JSON_EXTRACT(data, '$.price') > 100;

How it works: This snippet demonstrates how to query and filter data stored within JSON (or JSONB in PostgreSQL) columns. In PostgreSQL, `->>` extracts a JSON object field as text, while `->` extracts it as JSON; you can chain these operators for nested paths and cast types. MySQL uses `JSON_EXTRACT()` to retrieve values and `JSON_UNQUOTE()` to remove surrounding quotes from string results. Filtering is done directly in the `WHERE` clause using these JSON functions, with careful consideration for how string values are represented in JSON (e.g., `"available"` in MySQL for comparison).