Advanced & vendor frontiers

-- MySQL path extraction
SELECT payload->'$.user.name' AS quoted,      -- "ada"
       payload->>'$.user.name' AS unquoted     -- ada
FROM events;
--   comparing a -> result to a plain string silently fails to match.
-- Postgres
SELECT payload -> 'user' ->> 'name' FROM events;
SELECT payload #>> '{user,name}' FROM events;

-- containment — the Postgres query that GIN accelerates
SELECT * FROM events WHERE payload @> '{"type":"signup"}';
CREATE INDEX idx_payload ON events USING GIN (payload);

-- JSON_TABLE: expand an array into rows (SQL:2016, both engines)
SELECT e.id, t.sku, t.qty
  FROM events e,
  JSON_TABLE(e.payload, '$.items[*]' COLUMNS (
    sku VARCHAR(32) PATH '$.sku',
    qty INT PATH '$.qty'
  )) t;

-- Postgres
CREATE TABLE posts (
  id   SERIAL PRIMARY KEY,
  tags TEXT[] NOT NULL DEFAULT '{}'
);

SELECT * FROM posts WHERE 'sql' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['sql','db'];   -- contains both
CREATE INDEX ON posts USING GIN (tags);

-- unnest to rows, and aggregate back
SELECT id, tag FROM posts, unnest(tags) AS tag;
SELECT array_agg(tag ORDER BY tag) FROM tags;

-- MySQL
CREATE TABLE docs (id INT PRIMARY KEY, body TEXT,
  FULLTEXT KEY ft_body (body));

SELECT id, MATCH(body) AGAINST ('innodb btree' IN NATURAL LANGUAGE MODE) AS score
  FROM docs
 WHERE MATCH(body) AGAINST ('innodb btree' IN NATURAL LANGUAGE MODE)
 ORDER BY score DESC;

-- Postgres: tsvector + GIN, with stemming and weighting
ALTER TABLE docs ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;
CREATE INDEX ON docs USING GIN (tsv);

SELECT id, ts_rank(tsv, q) AS rank
  FROM docs, to_tsquery('english', 'innodb & btree') q
 WHERE tsv @@ q ORDER BY rank DESC;

-- the common pattern: a history table plus a trigger
CREATE TABLE prices_history (
  LIKE prices,
  valid_from DATETIME(3) NOT NULL,
  valid_to   DATETIME(3) NOT NULL,
  PRIMARY KEY (id, valid_from)
);

-- "what was the price when this order was placed?"
-- a non-equi join, exactly as in Part 2 ch 21
SELECT o.id, p.amount
  FROM orders o
  JOIN prices_history p
    ON p.sku = o.sku
   AND o.created_at >= p.valid_from
   AND o.created_at <  p.valid_to;

-- Postgres: sample ~1% of BLOCKS, not rows. very fast.
SELECT * FROM events TABLESAMPLE SYSTEM (1);

-- BERNOULLI samples rows independently: more uniform, slower
SELECT * FROM events TABLESAMPLE BERNOULLI (1);

-- MySQL has neither. the usual approximation:
SELECT * FROM events WHERE RAND() < 0.01;   -- full scan!

-- the rules that trip people up
--   1. both sides need the same column COUNT and compatible types
--   2. column NAMES come from the FIRST query
--   3. ORDER BY applies to the WHOLE result and goes at the end

(SELECT id, name FROM a ORDER BY name LIMIT 5)    -- per-branch: parenthesise
UNION ALL
(SELECT id, name FROM b ORDER BY name LIMIT 5)
ORDER BY name;                                    -- overall

-- Postgres
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT DATE(created_at) AS d, SUM(total) AS revenue
    FROM orders GROUP BY 1;

CREATE UNIQUE INDEX ON daily_revenue (d);      -- required for:
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;
--   without CONCURRENTLY, the refresh takes an exclusive lock
--   and the view is unreadable while it runs.