JSONB Query and Indexing Patterns

-- Create table with JSONB column
CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  TEXT NOT NULL,
  attrs JSONB NOT NULL DEFAULT '{}'
);

-- GIN index for containment queries
CREATE INDEX idx_product_attrs ON products USING GIN (attrs);

-- Query: find products with specific attributes
SELECT * FROM products
WHERE attrs @> '{"color": "red", "size": "L"}';

-- Query nested values
SELECT
  name,
  attrs->>'color' AS color,
  (attrs->'dimensions'->>'weight')::numeric AS weight
FROM products
WHERE attrs ? 'color'
  AND (attrs->'dimensions'->>'weight')::numeric < 5.0;

-- Update nested JSONB
UPDATE products
SET attrs = jsonb_set(attrs, '{dimensions,weight}', '3.5')
WHERE id = 1;