Querying and Filtering JSON Data in PostgreSQL/MySQL

-- PostgreSQL Example
-- Create a sample table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    item_details JSONB
);

-- Insert sample data
INSERT INTO products (item_details) VALUES
('{"name": "Laptop Pro", "category": "Electronics", "price": 1200.00, "specs": {"cpu": "i7", "ram_gb": 16}}'),
('{"name": "Mechanical Keyboard", "category": "Accessories", "price": 150.00, "specs": {"layout": "US", "switches": "Cherry MX"}}'),
('{"name": "External Monitor", "category": "Electronics", "price": 300.00, "specs": {"resolution": "4K", "size_inch": 27}}');

-- Extract a specific value
SELECT id, item_details->>'name' AS product_name, item_details->'specs'->>'ram_gb' AS ram
FROM products
WHERE item_details->>'category' = 'Electronics';

-- Filter based on a numeric value inside JSON
SELECT id, item_details->>'name'
FROM products
WHERE (item_details->>'price')::numeric > 200.00;

-- Update a value inside JSONB (PostgreSQL)
UPDATE products
SET item_details = jsonb_set(item_details, '{price}', '1250.00'::jsonb, true)
WHERE item_details->>'name' = 'Laptop Pro';

-- MySQL Example (similar operations, different syntax)
-- Assume a table `products_mysql` with a `item_details` JSON column
-- SELECT id, JSON_UNQUOTE(JSON_EXTRACT(item_details, '$.name')) AS product_name
-- FROM products_mysql
-- WHERE JSON_EXTRACT(item_details, '$.category') = 'Electronics';

-- SELECT id, JSON_UNQUOTE(JSON_EXTRACT(item_details, '$.name'))
-- FROM products_mysql
-- WHERE JSON_EXTRACT(item_details, '$.price') > 200.00;