📦 JSON & Arrays

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    category    VARCHAR(100),
    price       NUMERIC(10,2),
    attributes  JSONB        -- flexible attributes column!
);

-- A laptop has screen_size, ram, storage
INSERT INTO products (name, category, price, attributes) VALUES
('MacBook Pro 16', 'Laptops', 2499.00,
 '{"screen_size": "16 inch", "ram": "32GB", "storage": "1TB SSD", "color": "Space Gray"}');

-- A t-shirt has size, material, pattern — completely different attributes!
INSERT INTO products (name, category, price, attributes) VALUES
('Cotton T-Shirt', 'Clothing', 29.99,
 '{"size": "L", "material": "100% Cotton", "pattern": "Striped", "washable": true}');

-- A blender has wattage, speeds, capacity
INSERT INTO products (name, category, price, attributes) VALUES
('Pro Blender 3000', 'Kitchen', 149.99,
 '{"wattage": 1200, "speeds": 10, "capacity_liters": 2.5, "bpa_free": true}');

INSERT INTO products (name, category, price, attributes) VALUES
('Gaming Monitor', 'Monitors', 599.99,
 '{
    "resolution": "4K",
    "refresh_rate": "144Hz",
    "panel": "IPS",
    "ports": ["HDMI 2.1", "DisplayPort 1.4", "USB-C"],
    "dimensions": {
        "width": "27 inch",
        "height": "16 inch",
        "weight_kg": 6.5
    }
}');

-- -> returns the value as JSONB (still wrapped)
SELECT name, attributes->'ram' AS ram_jsonb
FROM products
WHERE category = 'Laptops';
-- Result: "32GB"  (with quotes — it's still JSONB type)

-- ->> returns the value as plain TEXT (unwrapped)
SELECT name, attributes->>'ram' AS ram_text
FROM products
WHERE category = 'Laptops';
-- Result: 32GB  (no quotes — plain text)

-- Reach into nested JSON using a path
-- Get the "width" from the nested "dimensions" object
SELECT name,
       attributes#>'{dimensions, width}' AS width_jsonb,
       attributes#>>'{dimensions, width}' AS width_text
FROM products
WHERE name = 'Gaming Monitor';
-- width_jsonb: "27 inch"   (JSONB)
-- width_text:  27 inch     (TEXT)

-- Get the second port from the "ports" array (0-indexed!)
SELECT attributes#>>'{ports, 1}' AS second_port
FROM products
WHERE name = 'Gaming Monitor';
-- Result: DisplayPort 1.4

-- Find all products where attributes contain {"bpa_free": true}
SELECT name, price
FROM products
WHERE attributes @> '{"bpa_free": true}';
-- Finds the blender! This operator is VERY fast with a GIN index.

-- Find laptops with specific RAM
SELECT name, price
FROM products
WHERE attributes @> '{"ram": "32GB"}';

-- Find all products that HAVE a "screen_size" attribute
SELECT name, category
FROM products
WHERE attributes ? 'screen_size';
-- Returns laptops and monitors (not t-shirts or blenders)

-- ? also has variants:
-- ?|  (ANY of these keys exist)
SELECT name FROM products
WHERE attributes ?| ARRAY['wattage', 'screen_size'];

-- ?&  (ALL of these keys exist)
SELECT name FROM products
WHERE attributes ?& ARRAY['ram', 'storage'];

-- Find products with wattage greater than 1000
SELECT name, price,
       (attributes->>'wattage')::INTEGER AS watts
FROM products
WHERE (attributes->>'wattage')::INTEGER > 1000;

-- Find products that are washable
SELECT name, price
FROM products
WHERE (attributes->>'washable')::BOOLEAN = TRUE;

-- Partial text match inside JSON
SELECT name, attributes->>'material' AS material
FROM products
WHERE attributes->>'material' LIKE '%Cotton%';

-- Create a GIN index on the entire JSONB column
CREATE INDEX idx_products_attributes
ON products USING GIN (attributes);

-- Now these queries are FAST (use the GIN index):
SELECT * FROM products WHERE attributes @> '{"ram": "32GB"}';
SELECT * FROM products WHERE attributes ? 'wattage';

-- For targeting a SPECIFIC path (more efficient if you only query one key):
CREATE INDEX idx_products_category_attr
ON products USING GIN ((attributes->'ports'));

-- Turn each JSON key-value pair into a row
SELECT p.name,
       kv.key   AS attribute_name,
       kv.value AS attribute_value
FROM products p,
     jsonb_each(p.attributes) AS kv(key, value)
WHERE p.name = 'MacBook Pro 16';

-- Result:
-- name           | attribute_name | attribute_value
-- MacBook Pro 16 | color          | "Space Gray"
-- MacBook Pro 16 | ram            | "32GB"
-- MacBook Pro 16 | screen_size    | "16 inch"
-- MacBook Pro 16 | storage        | "1TB SSD"

-- Expand the "ports" array into individual rows
SELECT p.name,
       port.value AS port_name
FROM products p,
     jsonb_array_elements(p.attributes->'ports') AS port(value)
WHERE p.name = 'Gaming Monitor';

-- Result:
-- name           | port_name
-- Gaming Monitor | "HDMI 2.1"
-- Gaming Monitor | "DisplayPort 1.4"
-- Gaming Monitor | "USB-C"

-- Use jsonb_array_elements_text to get TEXT instead of JSONB:
SELECT p.name,
       port AS port_name
FROM products p,
     jsonb_array_elements_text(p.attributes->'ports') AS port
WHERE p.name = 'Gaming Monitor';

-- Convert JSON attributes to proper typed columns
SELECT p.name, p.price, attr.*
FROM products p,
     jsonb_to_record(p.attributes) AS attr(
         ram TEXT,
         storage TEXT,
         screen_size TEXT
     )
WHERE p.category = 'Laptops';

-- Result:
-- name           | price   | ram  | storage | screen_size
-- MacBook Pro 16 | 2499.00 | 32GB | 1TB SSD | 16 inch

-- Change the RAM of a laptop from 32GB to 64GB
UPDATE products
SET attributes = jsonb_set(attributes, '{ram}', '"64GB"')
WHERE name = 'MacBook Pro 16';

-- Update a nested value (change the weight)
UPDATE products
SET attributes = jsonb_set(attributes, '{dimensions, weight_kg}', '5.8')
WHERE name = 'Gaming Monitor';

-- Add a NEW key (create_missing = true by default)
UPDATE products
SET attributes = jsonb_set(attributes, '{warranty_years}', '3')
WHERE name = 'MacBook Pro 16';

-- Add multiple new keys at once by merging
UPDATE products
SET attributes = attributes || '{"on_sale": true, "discount_pct": 15}'::jsonb
WHERE name = 'Cotton T-Shirt';

-- If a key already exists, || overwrites it with the new value
UPDATE products
SET attributes = attributes || '{"price_note": "Holiday sale!"}'::jsonb
WHERE category = 'Clothing';

-- Remove a single key
UPDATE products
SET attributes = attributes - 'on_sale'
WHERE name = 'Cotton T-Shirt';

-- Remove multiple keys at once
UPDATE products
SET attributes = attributes - 'discount_pct' - 'price_note'
WHERE name = 'Cotton T-Shirt';

-- Remove a nested path
UPDATE products
SET attributes = attributes #- '{dimensions, weight_kg}'
WHERE name = 'Gaming Monitor';

-- Remove all keys that have null values
SELECT jsonb_strip_nulls(
    '{"name": "Widget", "color": null, "size": "M", "note": null}'::jsonb
);
-- Result: {"name": "Widget", "size": "M"}

CREATE TABLE articles (
    id       SERIAL PRIMARY KEY,
    title    VARCHAR(300) NOT NULL,
    tags     TEXT[],           -- array of text strings
    ratings  INTEGER[],        -- array of integers
    scores   NUMERIC(5,2)[]   -- array of decimals
);

-- Method 1: ARRAY constructor
INSERT INTO articles (title, tags, ratings) VALUES
('PostgreSQL JSON Guide',
 ARRAY['postgresql', 'json', 'database', 'tutorial'],
 ARRAY[5, 4, 5, 3, 5]);

-- Method 2: Curly-brace syntax
INSERT INTO articles (title, tags, ratings) VALUES
('Learning SQL Joins',
 '{sql, joins, beginner}',
 '{4, 5, 4, 4}');

-- Method 3: Cast a string
INSERT INTO articles (title, tags, ratings) VALUES
('Advanced Indexing',
 '{indexing, performance, b-tree, gin}'::TEXT[],
 '{5, 5, 4, 5, 5}'::INTEGER[]);

-- Find articles tagged with 'postgresql'
SELECT title, tags
FROM articles
WHERE 'postgresql' = ANY(tags);

-- Find articles tagged with BOTH 'sql' AND 'beginner'
SELECT title, tags
FROM articles
WHERE tags @> ARRAY['sql', 'beginner'];

-- Find articles with ANY overlap with these tags
SELECT title, tags
FROM articles
WHERE tags && ARRAY['json', 'performance'];

-- Access specific array element (1-indexed!)
SELECT title, tags[1] AS first_tag
FROM articles;

-- Get array length
SELECT title, array_length(tags, 1) AS num_tags
FROM articles;

-- Turn each tag into its own row
SELECT a.title, t.tag
FROM articles a,
     unnest(a.tags) AS t(tag);

-- Count how many articles use each tag
SELECT tag, COUNT(*) AS article_count
FROM articles, unnest(tags) AS tag
GROUP BY tag
ORDER BY article_count DESC;

-- Combine all employee names in each department into one array
SELECT department,
       array_agg(name ORDER BY name) AS employees
FROM employees
GROUP BY department;

-- Result:
-- department  | employees
-- Engineering | {Alice, Bob, Carol}
-- Marketing   | {Dave, Eve}

CREATE TABLE blog_posts (
    id        SERIAL PRIMARY KEY,
    title     TEXT NOT NULL,
    content   TEXT,
    tags      TEXT[] DEFAULT '{}'
);

-- GIN index for fast tag queries
CREATE INDEX idx_posts_tags ON blog_posts USING GIN (tags);

-- Find posts with a specific tag
SELECT title FROM blog_posts WHERE 'python' = ANY(tags);

-- Add a tag to an existing post
UPDATE blog_posts
SET tags = array_append(tags, 'featured')
WHERE id = 1;

-- Remove a tag
UPDATE blog_posts
SET tags = array_remove(tags, 'draft')
WHERE id = 1;

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       VARCHAR(255) UNIQUE NOT NULL,
    preferences JSONB DEFAULT '{
        "theme": "light",
        "notifications": true,
        "language": "en",
        "timezone": "UTC"
    }'::jsonb
);

-- Update just the theme preference
UPDATE users
SET preferences = jsonb_set(preferences, '{theme}', '"dark"')
WHERE id = 1;

-- Toggle notifications off
UPDATE users
SET preferences = jsonb_set(preferences, '{notifications}', 'false')
WHERE id = 1;

-- Find users who prefer dark theme
SELECT email FROM users
WHERE preferences @> '{"theme": "dark"}';

CREATE TABLE api_logs (
    id           BIGSERIAL PRIMARY KEY,
    endpoint     VARCHAR(500),
    method       VARCHAR(10),
    status_code  INTEGER,
    request_body JSONB,
    response_body JSONB,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

-- Find all failed API calls that had a specific error code in the response
SELECT endpoint, status_code,
       response_body->>'error_message' AS error
FROM api_logs
WHERE status_code >= 400
  AND response_body ? 'error_message'
ORDER BY created_at DESC
LIMIT 20;

-- The "hybrid" approach: fixed columns for common fields,
-- JSONB for variable attributes
CREATE TABLE catalog (
    id          SERIAL PRIMARY KEY,
    sku         VARCHAR(50) UNIQUE NOT NULL,
    name        VARCHAR(300) NOT NULL,
    category    VARCHAR(100) NOT NULL,
    price       NUMERIC(12,2) NOT NULL,
    in_stock    BOOLEAN DEFAULT TRUE,
    specs       JSONB DEFAULT '{}',   -- variable attributes
    images      TEXT[] DEFAULT '{}',   -- multiple image URLs
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_catalog_specs ON catalog USING GIN (specs);

-- Search for laptops with at least 16GB RAM under $2000
SELECT name, price, specs->>'ram' AS ram
FROM catalog
WHERE category = 'Laptops'
  AND specs @> '{"ram": "16GB"}'
  AND price < 2000;