JSONB & Arrays

CREATE TABLE products (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     text NOT NULL,
  metadata jsonb NOT NULL DEFAULT '{}'
);

-- Insert a product with arbitrary metadata
INSERT INTO products (name, metadata) VALUES
  ('Laptop Pro', '{"brand": "Acme", "specs": {"ram_gb": 16, "ssd_gb": 512}, "tags": ["tech", "new"]}');

-- -> returns a JSON value (keeps type as jsonb)
SELECT metadata -> 'brand' FROM products;
-- Result: "Acme"   (with quotes, it is still jsonb)

-- ->> returns a text value (casts to text)
SELECT metadata ->> 'brand' FROM products;
-- Result: Acme    (plain text, no quotes)

-- Nested access
SELECT metadata -> 'specs' ->> 'ram_gb' FROM products;

-- #> and #>> for path arrays
SELECT metadata #>> '{specs,ram_gb}' FROM products;

-- Find products where brand is Acme
SELECT name FROM products
  WHERE metadata ->> 'brand' = 'Acme';

-- Containment operator @> (does the left side contain the right?)
SELECT name FROM products
  WHERE metadata @> '{"brand": "Acme"}';

-- Check if a key exists
SELECT name FROM products
  WHERE metadata ? 'brand';

-- Check if any of multiple keys exist
SELECT name FROM products
  WHERE metadata ?| ARRAY['brand', 'sku'];

-- GIN index makes @> and ? operators fast
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- Index a specific key with a functional index
CREATE INDEX idx_products_brand ON products ((metadata ->> 'brand'));

CREATE TABLE articles (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tags text[] NOT NULL DEFAULT '{}'
);

INSERT INTO articles (tags) VALUES (ARRAY['postgres', 'databases', 'tutorial']);

-- Access element (1-indexed!)
SELECT tags[1] FROM articles;  -- 'postgres'

-- Check containment
SELECT * FROM articles WHERE tags @> ARRAY['postgres'];

-- Overlap (any element in common)
SELECT * FROM articles WHERE tags && ARRAY['postgres', 'python'];

-- Append an element
UPDATE articles SET tags = tags || ARRAY['newTag'] WHERE id = 1;

-- Array length
SELECT array_length(tags, 1) FROM articles;