JSON & Semi-Structured Data

-- Store a whole JSON document in ONE column.
-- "Semi-structured" = each row can hold a differently-shaped object.
-- In PostgreSQL, prefer jsonb over json (binary, indexable, dedupes keys).

CREATE TABLE events (
    id      SERIAL PRIMARY KEY,
    payload JSONB NOT NULL          -- the flexible bit lives here
);

INSERT INTO events (payload) VALUES
('{"type":"purchase","customer":{"id":42,"tier":"gold"},"total":999.99,"items":["laptop","mouse"]}'),
('{"type":"signup","customer":{"id":17,"tier
...

-- json  : kept as raw TEXT. Preserves exact whitespace & key order.
--         Re-parsed on every read. Cannot be indexed with GIN.
-- jsonb : decomposed BINARY. Slightly slower to write, much faster
--         to query, removes duplicate keys, and supports indexing.

-- Same data, two columns, two behaviours:
CREATE TABLE demo (
    raw  JSON,     -- '{ "a": 1,  "a": 2 }' stays exactly as typed
    fast JSONB     -- '{"a":2}'  (whitespace gone, last key wins)
);

-- Rule of thumb: reach for js
...

-- The four extraction operators (PostgreSQL):
--   ->   get a field, KEEP it as json/jsonb
--   ->>  get a field, return it as TEXT
--   #>   follow a PATH, KEEP it as json/jsonb
--   #>>  follow a PATH, return it as TEXT

SELECT
    payload -> 'customer'            AS customer_json,   -- {"id":42,"tier":"gold"}
    payload ->> 'type'               AS type_text,       -- purchase
    payload -> 'customer' ->> 'tier' AS tier,            -- gold
    payload #>> '{customer,id}'      AS cust_id,   
...

-- Filtering rows by a value INSIDE the JSON.
-- Because ->> returns text, compare against a quoted string,
-- or cast both sides to the type you actually mean.

-- Every event whose nested customer tier is "gold":
SELECT id, payload ->> 'type' AS type
FROM events
WHERE payload -> 'customer' ->> 'tier' = 'gold';   -- rows 1 and 3

-- Numeric comparison needs a cast (text '999.99' < '30' otherwise!):
SELECT id
FROM events
WHERE (payload ->> 'total')::numeric > 100;        -- row 1 only

-- @>  "containment": does the left document CONTAIN this subset?
-- It matches nested keys/values without you spelling out the path,
-- and — crucially — it can use a GIN index (see section 6).

-- Purchases made by a gold-tier customer:
SELECT id FROM events
WHERE payload @> '{"type":"purchase","customer":{"tier":"gold"}}';
-- rows 1 and 3

-- Other handy jsonb predicates:
--   payload ? 'source'                    -- does the key "source" exist?
--   payload @> '{"items":["mouse"]}'      -- a
...

-- jsonb_set(target, path, new_value) returns a MODIFIED copy.
-- The path is a text[] array: '{a,b}' means a -> b.
-- new_value must itself be jsonb — note the quotes around a string.

-- Add / overwrite a top-level field:
UPDATE events
SET payload = jsonb_set(payload, '{status}', '"shipped"')
WHERE id = 1;
-- payload now also has  "status":"shipped"

-- Update a NESTED field (customer.tier):
UPDATE events
SET payload = jsonb_set(payload, '{customer,tier}', '"platinum"')
WHERE id = 1;

-- Quick
...

-- jsonb with NO index = full table scan on every @> / ? query.
-- A GIN ("Generalized INverted") index fixes that.

-- 1) GIN over the whole column — powers @>, ?, ?|, ?&
CREATE INDEX idx_events_payload
ON events USING gin (payload);

-- This query can now use the index instead of scanning:
SELECT id FROM events
WHERE payload @> '{"customer":{"tier":"gold"}}';

-- 2) Smaller / faster index if you ONLY use @>:
CREATE INDEX idx_events_payload_path
ON events USING gin (payload jsonb_path_ops);

--
...

-- MySQL has its own JSON type (binary, validated, like jsonb).
-- Same ideas, different spelling:

-- Extract with JSON_EXTRACT or the -> shorthand (returns JSON):
SELECT JSON_EXTRACT(payload, '$.type')   AS type_json,   -- "purchase"
       payload -> '$.customer.tier'      AS tier_json,   -- "gold"  (quoted)
       payload ->> '$.customer.tier'     AS tier_text    -- gold    (unquoted)
FROM events;

-- ->>  is shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)) — your text operator.

-- Filter by a
...

-- JSON is flexible, but flexibility is not free.
-- Pull values OUT into real columns when they earn it.

-- ✅ Good fit for JSON / jsonb:
--   • shape varies row to row (event payloads, webhook bodies)
--   • third-party API responses you store verbatim
--   • sparse / rarely-queried "extra" attributes

-- ❌ Promote to a normal column when:
--   • you filter, sort, join, or GROUP BY the value often
--   • it needs a FK, UNIQUE, NOT NULL or CHECK constraint
--   • the type matters (dates, money)
...