18 — JSON & Array Columns

-- jsonb deduplicates keys (last wins), json preserves duplicates
SELECT '{"a": 1, "a": 2}'::json;     -- {"a": 1, "a": 2}  (both keys preserved)
SELECT '{"a": 1, "a": 2}'::jsonb;    -- {"a": 2}           (last value wins)

-- jsonb sorts keys, json preserves input order
SELECT '{"b": 1, "a": 2}'::json;     -- {"b": 1, "a": 2}   (input order)
SELECT '{"b": 1, "a": 2}'::jsonb;    -- {"a": 2, "b": 1}   (sorted)

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

-- From a JSON string literal (PostgreSQL parses and converts to binary jsonb)
INSERT INTO events (payload) VALUES (
  '{"type": "click", "user": {"id": 42, "name": "Alice"}, "tags": ["web", "mobile"]}'
);

-- From columns via jsonb_build_object (handles quoting/escaping automatically)
INSERT INTO events (payload)
SELECT jsonb_build_object(
  'type', 'click',
  'user_id', u.id,                -- integer → JSON number
  'email', u.email,               -- text → JSON string
  'ts', now()                     -- timestamptz → JSON string (ISO format)
)
FROM users u WHERE u.email = 'a@x.com';

-- From multiple rows aggregated into a JSON array
INSERT INTO summary (daily_events)
SELECT jsonb_agg(jsonb_build_object('id', e.id, 'type', e.payload->>'type'))
FROM events e
WHERE e.created_at >= CURRENT_DATE;

-- Get a field as text (->> is the final-step operator — returns text, can't chain)
SELECT payload->>'type' FROM events;                        -- 'click' (text)

-- Get a nested field: chain -> for traversal, ->> for the final scalar
SELECT payload->'user'->>'name' FROM events;                -- 'Alice' (text)
SELECT payload #>> '{user,name}' FROM events;               -- 'Alice' (text, path syntax)

-- Get an array element (arrays are 0-indexed in JSON path)
SELECT payload->'tags'->>0 FROM events;                     -- 'web' (first tag, as text)
SELECT payload->'tags'->>1 FROM events;                     -- 'mobile' (second tag)

-- Containment: the indexable operator (use this for filtered queries)
SELECT * FROM events WHERE payload @> '{"type": "click"}';                 -- top-level key match
SELECT * FROM events WHERE payload @> '{"user": {"id": 42}}';              -- nested object match
SELECT * FROM events WHERE payload @> '{"tags": ["web"]}';                 -- array contains element

-- Key existence
SELECT * FROM events WHERE payload ? 'user_id';                            -- key exists
SELECT * FROM events WHERE payload ?| array['user_id', 'session_id'];      -- any key exists
SELECT * FROM events WHERE payload ?& array['user_id', 'session_id'];      -- all keys exist

-- -> returns JSONB (can chain further -> operators)
SELECT payload->'user'->'id' FROM events;        -- 42 (JSONB number)
-- SELECT payload->'user'->'id'->>'x';           -- ERROR: can't chain ->> (returns text)

-- ->> returns text (final step — can't chain)
SELECT payload->'user'->>'name' FROM events;     -- 'Alice' (text)

-- ⚠️ Numeric comparison trap: ->> returns text, so comparison is string-based
SELECT * FROM events WHERE (payload->>'amount') > 100;
-- String comparison: '100' > '99' is TRUE (lexicographic), '20' > '100' is TRUE — WRONG

-- ✅ Cast to numeric for correct comparison
SELECT * FROM events WHERE (payload->>'amount')::numeric > 100;

-- Full GIN index: indexes every key and value in the JSONB column
CREATE INDEX events_payload_gin ON events USING gin(payload);

-- Now containment, key-existence, and path queries use the index
SELECT * FROM events WHERE payload @> '{"type": "click"}';    -- uses GIN
SELECT * FROM events WHERE payload ? 'user_id';               -- uses GIN
SELECT * FROM events WHERE payload ?| array['a', 'b'];        -- uses GIN

-- ⚠️ The GIN index does NOT help with ->> extraction + comparison
SELECT * FROM events WHERE payload->>'type' = 'click';
-- This needs a separate expression index (see below)

-- Expression index: indexes the extracted text value of a specific path
CREATE INDEX events_type_idx ON events ((payload->>'type'));

-- Uses this index (the expression must match exactly)
SELECT * FROM events WHERE payload->>'type' = 'click';

-- Also works for the -> operator returning JSONB (for numeric/scalar comparisons)
CREATE INDEX events_user_id_idx ON events (((payload->'user')->>'id')::int);
SELECT * FROM events WHERE (payload->'user')->>'id'::int = 42;

-- Index only the 'click' events — much smaller, used when predicate matches
CREATE INDEX events_clicks_gin ON events USING gin(payload)
  WHERE payload @> '{"type": "click"}';

-- This query uses the partial index
SELECT * FROM events WHERE payload @> '{"type": "click"}' AND payload @> '{"source": "homepage"}';

-- jsonb_path_ops: smaller index, but only supports @> (no ?/ ?| / ?&)
CREATE INDEX events_payload_path_gin ON events USING gin(payload jsonb_path_ops);

-- @> uses the index (faster lookup, smaller index)
SELECT * FROM events WHERE payload @> '{"type": "click"}';

-- ? does NOT use this index type
SELECT * FROM events WHERE payload ? 'user_id';   -- seq scan (not supported by jsonb_path_ops)

-- Extract all user IDs from an array of events
SELECT jsonb_path_query(payload, '$.user.id') FROM events;

-- Filter array elements with a condition
SELECT jsonb_path_query(payload, '$.tags[*] ? (@ == "web")') FROM events;

-- Existence check (returns boolean)
SELECT jsonb_path_exists(payload, '$.user.id ? (@ > 10)') FROM events;

-- Query with variables ($1 references the first variable)
SELECT * FROM events
WHERE jsonb_path_exists(payload, '$.user.id ? (@ == $id)', '{"id": 42}');

-- Extract nested arrays and iterate
SELECT jsonb_path_query(payload, '$.items[*].name') FROM orders_json;

-- Add/set a key at a path (jsonb_set: target, path, new_value, create_if_missing)
UPDATE events SET payload = jsonb_set(payload, '{user,name}', '"Bob"'::jsonb);
--                                                                    ^^^^^^^^^^^^
-- new_value must be jsonb — strings need double quotes and cast

-- Add a key only if it doesn't exist (create_if_missing = true is the default)
UPDATE events SET payload = jsonb_set(payload, '{processed}', 'true'::jsonb, true);

-- Set only if the key already exists (create_if_missing = false)
UPDATE events SET payload = jsonb_set(payload, '{existing_key}', '"new"'::jsonb, false);

-- Merge at the top level (|| concatenates — does NOT deep-merge)
UPDATE events SET payload = payload || '{"processed": true, "version": 2}'::jsonb;
-- ⚠️ || replaces entire objects: payload || '{"user": {"name": "Bob"}}'
--    replaces the ENTIRE user object, not just user.name

-- Remove a key (- operator)
UPDATE events SET payload = payload - 'tags';

-- Remove a nested key (#- with path)
UPDATE events SET payload = payload #- '{user,id}';

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT[] NOT NULL DEFAULT '{}'   -- array of text, default empty array
);

-- Insert with array literal syntax
INSERT INTO posts (title, tags) VALUES ('Hello', '{"intro", "welcome"}');

-- Insert with ARRAY constructor
INSERT INTO posts (title, tags) VALUES ('World', ARRAY['news', 'tech']);

-- Insert with array_agg from a subquery
INSERT INTO archive (id, all_tags)
SELECT post_id, array_agg(tag) FROM post_tags GROUP BY post_id;

-- Posts with the 'intro' tag (two equivalent forms)
SELECT * FROM posts WHERE 'intro' = ANY(tags);           -- works, no GIN
SELECT * FROM posts WHERE tags @> array['intro'];        -- uses GIN index

-- Posts with ALL of these tags (contains both)
SELECT * FROM posts WHERE tags @> array['intro', 'welcome'];

-- Posts with ANY of these tags (overlap)
SELECT * FROM posts WHERE tags && array['intro', 'news'];

-- GIN index for array containment queries
CREATE INDEX posts_tags_gin ON posts USING gin(tags);

-- Element access (1-indexed!)
SELECT tags[1] FROM posts;        -- first element (tags[0] is NULL, not an error)
SELECT tags[2] FROM posts;        -- second element

-- Length (⚠️ array_length returns NULL for empty arrays!)
SELECT array_length(tags, 1) FROM posts WHERE tags = '{}';   -- NULL, not 0
SELECT cardinality(tags) FROM posts WHERE tags = '{}';        -- 0 (use cardinality)
SELECT coalesce(array_length(tags, 1), 0) FROM posts;         -- 0 (coalesce fallback)

-- Expand each post's tags into separate rows (one row per tag)
SELECT id, unnest(tags) AS tag FROM posts;
--  id | tag
--  1  | intro
--  1  | welcome
--  2  | news

-- unnest + array_agg are inverses: expand, filter, re-aggregate
SELECT id, array_agg(tag) AS filtered_tags
FROM (SELECT id, unnest(tags) AS tag FROM posts) t
WHERE tag NOT LIKE 'test_%'   -- filter out test tags
GROUP BY id;

-- Turn a comma-separated string into an array
SELECT string_to_array('a,b,c', ',');              -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ',');   -- a,b,c

-- Multi-column unnest (parallel expansion)
SELECT unnest(ARRAY[1,2,3]), unnest(ARRAY['a','b','c']);
--  1 | a
--  2 | b
--  3 | c

-- ============================================================================
-- Schema: event tracking platform with variable-structure JSONB payloads
-- ============================================================================
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT,
  event_type TEXT NOT NULL,                    -- denormalized for fast filtering
  payload JSONB NOT NULL,                      -- variable-structure event data
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- GIN index on the full payload: supports @>, ?, ?|, ?& for any path
CREATE INDEX events_payload_gin ON events USING gin(payload);

-- B-tree index on event_type for equality filtering
CREATE INDEX events_type_idx ON events(event_type);

-- B-tree index on created_at for time-range queries
CREATE INDEX events_created_at_idx ON events(created_at);

-- Partial GIN for the hot 'click' event subset (much smaller than full GIN)
CREATE INDEX events_clicks_gin ON events USING gin(payload)
  WHERE event_type = 'click';

-- ============================================================================
-- Insert: events with different payload structures (schema flexibility)
-- ============================================================================
INSERT INTO events (user_id, event_type, payload) VALUES
  (42, 'click', '{
    "target": "button.submit",
    "page": "/checkout",
    "metadata": {"session_id": "abc123", "ab_variant": "B"}
  }'),
  (42, 'purchase', '{
    "order_id": 9821,
    "items": [
      {"sku": "WIDGET-001", "qty": 2, "price": 19.99},
      {"sku": "GADGET-002", "qty": 1, "price": 49.99}
    ],
    "total": 89.97
  }'),
  (99, 'click', '{
    "target": "link.nav",
    "page": "/home",
    "metadata": {"session_id": "def456", "ab_variant": "A"}
  }');

-- ============================================================================
-- Query 1: containment — uses the full GIN index (fast)
-- ============================================================================
-- All click events on the checkout page
SELECT id, user_id, payload->>'target' AS target
FROM events
WHERE event_type = 'click' AND payload @> '{"page": "/checkout"}';
-- Uses the event_type B-tree index AND the payload GIN index

-- ============================================================================
-- Query 2: nested object containment — GIN index handles nested paths
-- ============================================================================
-- All events from a specific A/B test variant
SELECT id, event_type, payload->>'page' AS page
FROM events
WHERE payload @> '{"metadata": {"ab_variant": "B"}}';
-- GIN index scans the nested path — no full-table scan

-- ============================================================================
-- Query 3: JSON path query for deep extraction
-- ============================================================================
-- Extract all item SKUs from purchase events
SELECT
  e.id,
  jsonb_path_query(e.payload, '$.items[*].sku')::text AS sku
FROM events e
WHERE e.event_type = 'purchase';

-- Extract items with quantity > 1
SELECT
  e.id,
  jsonb_path_query(e.payload, '$.items[*] ? (@.qty > 1)') AS high_qty_item
FROM events e
WHERE e.event_type = 'purchase';

-- ============================================================================
-- Query 4: aggregate JSON from rows (build a summary JSON object)
-- ============================================================================
-- Build a per-user event summary as a JSON array
SELECT
  user_id,
  jsonb_agg(
    jsonb_build_object(
      'type', event_type,
      'page', payload->>'page',
      'ts', created_at
    )
  ) AS event_history
FROM events
WHERE created_at >= now() - INTERVAL '24 hours'
GROUP BY user_id;

-- ============================================================================
-- Query 5: update a nested field with jsonb_set
-- ============================================================================
-- Add a 'processed' flag to an event's metadata
UPDATE events
SET payload = jsonb_set(payload, '{metadata,processed}', 'true'::jsonb, true)
WHERE id = 1;
-- Path '{metadata,processed}' navigates to payload.metadata.processed
-- create_if_missing=true creates the key if it doesn't exist

-- ============================================================================
-- Query 6: array column with GIN for tag-based filtering
-- ============================================================================
CREATE TABLE user_tags (
  user_id BIGINT PRIMARY KEY,
  tags TEXT[] NOT NULL DEFAULT '{}'
);
CREATE INDEX user_tags_gin ON user_tags USING gin(tags);

-- Find users who have all of these tags (containment, uses GIN)
SELECT user_id FROM user_tags WHERE tags @> array['premium', 'newsletter'];

-- Find users who have any of these tags (overlap, uses GIN)
SELECT user_id FROM user_tags WHERE tags && array['premium', 'trial'];

-- Expand tags for a report (one row per user-tag pair)
SELECT user_id, tag FROM user_tags, unnest(tags) AS tag ORDER BY user_id;

-- ❌ WRONG: stuffing structured data into JSONB when a relational schema is better
CREATE TABLE bad_orders (
  id BIGSERIAL PRIMARY KEY,
  data JSONB NOT NULL   -- {"customer_id": 42, "amount": 99.95, "status": "shipped", ...}
);

-- Now every query extracts fields: slow, no type safety, no foreign keys
SELECT * FROM bad_orders WHERE (data->>'customer_id')::int = 42;
SELECT * FROM bad_orders WHERE (data->>'amount')::numeric > 100;
-- No FK to customers, no CHECK constraints, no type safety, slow queries

-- ✅ RIGHT: relational schema for structured data, JSONB only for variable parts
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),  -- FK enforced
  amount NUMERIC(12,2) NOT NULL CHECK (amount >= 0),     -- type + constraint
  status TEXT NOT NULL DEFAULT 'pending',
  metadata JSONB                                          -- variable extra data only
);

CREATE INDEX orders_customer_idx ON orders(customer_id);
CREATE INDEX orders_status_idx ON orders(status);

-- ❌ WRONG: containment query without a GIN index → full table scan
SELECT * FROM events WHERE payload @> '{"type": "click"}';
-- Without a GIN index, PostgreSQL must parse every row's JSONB to check containment
-- O(n) over all rows — slow on large tables

-- ✅ RIGHT: create a GIN index, then containment queries use it
CREATE INDEX events_payload_gin ON events USING gin(payload);
SELECT * FROM events WHERE payload @> '{"type": "click"}';
-- GIN index: O(matches) — fast even on millions of rows

-- ⚠️ BUT: ->> extraction + comparison does NOT use the full GIN index
SELECT * FROM events WHERE payload->>'type' = 'click';
-- Needs a separate expression index: CREATE INDEX ON events ((payload->>'type'));

CREATE INDEX events_payload_gin ON events USING gin(payload);

SELECT * FROM events WHERE payload->>'type' = 'click';
-- EXPLAIN shows Seq Scan, not Bitmap Index Scan