PostgreSQL JSON vs JSONB — 28x Query Improvement

-- Create a table to compare JSON and JSONB storage behaviour side by side
CREATE TABLE io.thecodeforge.storage.comparison (
    id          SERIAL PRIMARY KEY,
    raw_json    JSON,    -- stored verbatim, re-parsed on every access
    binary_json JSONB    -- parsed once on insert, stored as binary tree
);

-- Insert the same payload into both columns.
-- Note the intentional duplicate key 'status' and extra whitespace.
INSERT INTO io.thecodeforge.storage.comparison (raw_json, binary_json)
VALUES (
    '{  "user_id": 42,  "status": "pending",  "status": "active"  }',
    '{  "user_id": 42,  "status": "pending",  "status": "active"  }'
);

-- Retrieve both columns and observe the differences
SELECT
    raw_json,     -- JSON: whitespace preserved, BOTH 'status' keys kept
    binary_json   -- JSONB: whitespace stripped, duplicate key deduplicated (last wins)
FROM io.thecodeforge.storage.comparison
WHERE id = 1;

-- Check the actual storage size on disk
SELECT
    pg_column_size(raw_json)    AS json_bytes,
    pg_column_size(binary_json) AS jsonb_bytes
FROM io.thecodeforge.storage.comparison
WHERE id = 1;

-- Real-world benchmark: measure parse overhead at scale
-- Run this on a table with 1M rows to see the JSON vs JSONB difference
CREATE TABLE io.thecodeforge.benchmark.json_parse_test AS
SELECT
    generate_series(1, 1000000) AS id,
    ('{"name":"user_' || generate_series(1, 1000000)
     || '","email":"user' || generate_series(1, 1000000)
     || '@example.com","age":' || (random() * 80 + 18)::INT
     || ',"city":"City_' || (random() * 100)::INT || '"}')::JSON AS data_json,
    ('{"name":"user_' || generate_series(1, 1000000)
     || '","email":"user' || generate_series(1, 1000000)
     || '@example.com","age":' || (random() * 80 + 18)::INT
     || ',"city":"City_' || (random() * 100)::INT || '"}')::JSONB AS data_jsonb;

-- Benchmark: extract 'age' field from JSON (re-parses every row)
\timing on
SELECT COUNT(*) FROM io.thecodeforge.benchmark.json_parse_test
WHERE (data_json ->> 'age')::INT > 65;
-- Typical result: ~8,200ms on 1M rows

-- Benchmark: extract 'age' field from JSONB (pre-parsed, no re-parse)
SELECT COUNT(*) FROM io.thecodeforge.benchmark.json_parse_test
WHERE (data_jsonb ->> 'age')::INT > 65;
-- Typical result: ~2,100ms on 1M rows — roughly 4x faster

-- Clean up
DROP TABLE io.thecodeforge.benchmark.json_parse_test;

-- Set up a realistic product catalogue table
CREATE TABLE io.thecodeforge.catalogue.products (
    product_id   SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    attributes   JSONB NOT NULL  -- flexible attributes vary by category
);

INSERT INTO io.thecodeforge.catalogue.products (product_name, attributes) VALUES
    ('Wireless Headphones', '{"brand":"SoundPeak","specs":{"battery_hours":30,"driver_mm":40},"tags":["audio","wireless","noise-cancelling"],"in_stock":true,"price_usd":149.99}'),
    ('Mechanical Keyboard', '{"brand":"KeyForge","specs":{"switch_type":"Cherry MX Blue","layout":"TKL"},"tags":["peripherals","mechanical"],"in_stock":false,"price_usd":89.00}'),
    ('USB-C Hub', '{"brand":"ConnectPro","specs":{"ports":7,"max_wattage":100},"tags":["accessories","usb-c"],"in_stock":true,"price_usd":39.99}');

-- (1) -> returns a JSON node (preserves type)
--     ->> returns TEXT (use for comparisons and display)
SELECT
    product_name,
    attributes -> 'brand'       AS brand_as_json,   -- returns: "SoundPeak" (JSON string)
    attributes ->> 'brand'      AS brand_as_text,   -- returns: SoundPeak (plain text)
    attributes -> 'price_usd'   AS price_as_json    -- returns: 149.99 (JSON number)
FROM io.thecodeforge.catalogue.products;

-- (2) Nested access with chained -> and path operator #>>
SELECT
    product_name,
    attributes -> 'specs' -> 'battery_hours'  AS battery_json,  -- NULL for non-headphones
    attributes #>> '{specs, battery_hours}'    AS battery_text   -- same result, cleaner syntax
FROM io.thecodeforge.catalogue.products;

-- (3) @> containment: find all in-stock products with 'wireless' tag
-- This is the operator GIN indexes are built for — use it!
SELECT product_name, attributes ->> 'price_usd' AS price
FROM io.thecodeforge.catalogue.products
WHERE attributes @> '{"in_stock": true}'          -- containment check
  AND attributes @> '{"tags": ["wireless"]}';      -- array containment works too

-- (4) ? existence: find products that HAVE an 'in_stock' key
-- Note: ? only checks TOP-LEVEL keys
SELECT product_name
FROM io.thecodeforge.catalogue.products
WHERE attributes ? 'in_stock';  -- all rows have this key, returns all 3

-- (5) ?| any-existence: find products with ANY of these keys
SELECT product_name
FROM io.thecodeforge.catalogue.products
WHERE attributes ?| array['warranty', 'return_policy', 'in_stock'];
-- Returns all 3 because all have 'in_stock'

-- (6) ?& all-existence: find products that have ALL of these keys
SELECT product_name
FROM io.thecodeforge.catalogue.products
WHERE attributes ?& array['brand', 'price_usd', 'in_stock'];
-- Returns all 3

-- (7) Casting ->> result for numeric comparison (common pattern)
SELECT product_name
FROM io.thecodeforge.catalogue.products
WHERE (attributes ->> 'price_usd')::NUMERIC > 100.00;  -- cast TEXT to NUMERIC

-- (8) jsonb_typeof: inspect the type of a JSONB value
-- Useful for debugging — 'string', 'number', 'boolean', 'object', 'array', 'null'
SELECT
    product_name,
    jsonb_typeof(attributes -> 'price_usd')   AS price_type,     -- 'number'
    jsonb_typeof(attributes -> 'brand')        AS brand_type,     -- 'string'
    jsonb_typeof(attributes -> 'tags')         AS tags_type,      -- 'array'
    jsonb_typeof(attributes -> 'in_stock')     AS stock_type      -- 'boolean'
FROM io.thecodeforge.catalogue.products;

-- (9) jsonb_each: explode a JSONB object into key-value rows
-- Useful for dynamic pivoting or inspecting unknown structures
SELECT
    product_name,
    kv.key,
    kv.value
FROM io.thecodeforge.catalogue.products,
     jsonb_each(attributes) AS kv(key, value)
WHERE product_name = 'Wireless Headphones';

-- (10) jsonb_each_text: same but returns values as TEXT (no JSONB type wrapper)
SELECT
    product_name,
    kv.key,
    kv.value
FROM io.thecodeforge.catalogue.products,
     jsonb_each_text(attributes) AS kv(key, value)
WHERE product_name = 'Wireless Headphones';

-- (11) jsonb_array_elements_text: unnest a JSON array into rows
SELECT
    product_name,
    tag_text
FROM io.thecodeforge.catalogue.products,
     jsonb_array_elements_text(attributes -> 'tags') AS tag_text
ORDER BY product_name, tag_text;

-- Set up an event log table with deeply nested JSONB payloads
CREATE TABLE io.thecodeforge.events.log (
    event_id    BIGSERIAL PRIMARY KEY,
    event_type  TEXT      NOT NULL,
    payload     JSONB     NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO io.thecodeforge.events.log (event_type, payload) VALUES
    ('purchase', '{"customer":{"id":1001,"tier":"gold"},"items":[{"sku":"A1","name":"Widget","qty":2,"unit_price":29.99},{"sku":"B3","name":"Gadget","qty":1,"unit_price":29.99}],"total":89.99,"payment":{"method":"card","last4":"4242"}}'),
    ('purchase', '{"customer":{"id":1002,"tier":"silver"},"items":[{"sku":"C7","name":"Cable","qty":5,"unit_price":9.99}],"total":49.95,"payment":{"method":"paypal","email":"user@example.com"}}'),
    ('refund', '{"customer":{"id":1001,"tier":"gold"},"original_purchase_id":42,"items":[{"sku":"A1","name":"Widget","qty":1,"refund_amount":29.99}],"reason":"defective"}');

-- (1) jsonb_path_exists / @? : boolean check
-- Find all events where the customer is gold tier
SELECT event_id, event_type
FROM io.thecodeforge.events.log
WHERE payload @? '$.customer ? (@.tier == "gold")';

-- (2) jsonb_path_query : extract matching values
-- Get all SKUs from purchase events where qty > 1
SELECT
    event_id,
    jsonb_path_query(payload, '$.items[*] ? (@.qty > 1).sku') AS high_qty_sku
FROM io.thecodeforge.events.log
WHERE event_type = 'purchase';

-- (3) Complex filter: items with unit_price > 20 in gold customer purchases
SELECT
    event_id,
    jsonb_path_query(payload, '$.items[*] ? (@.unit_price > 20)') AS expensive_item
FROM io.thecodeforge.events.log
WHERE payload @? '$.customer ? (@.tier == "gold")'
  AND event_type = 'purchase';

-- (4) Path expression with type casting
-- Extract the total as a numeric value directly
SELECT
    event_id,
    jsonb_path_query(payload, '$.total')::NUMERIC AS total_amount
FROM io.thecodeforge.events.log
WHERE event_type = 'purchase';

-- (5) @@ operator : evaluate path expression as boolean
-- Find purchases where total exceeds 50
SELECT event_id, event_type
FROM io.thecodeforge.events.log
WHERE payload @@ '$.total > 50';

-- (6) Wildcard iteration: get all item names across all events
SELECT
    event_id,
    jsonb_path_query(payload, '$.items[*].name') AS item_name
FROM io.thecodeforge.events.log;

-- (7) Array indexing: get the first item in every purchase
SELECT
    event_id,
    jsonb_path_query(payload, '$.items[0]') AS first_item
FROM io.thecodeforge.events.log
WHERE event_type = 'purchase';

-- (8) Combining with relational filters
-- Recent gold customer purchases with expensive items
SELECT
    event_id,
    payload -> 'customer' ->> 'id' AS customer_id,
    jsonb_path_query(payload, '$.items[*] ? (@.unit_price > 25)') AS premium_item
FROM io.thecodeforge.events.log
WHERE event_type = 'purchase'
  AND payload @? '$.customer ? (@.tier == "gold")'
  AND created_at > NOW() - INTERVAL '7 days';

-- Scenario: 1 million product rows, we query by tags and brand frequently

-- Create the table (same structure as before, scaled up)
CREATE TABLE io.thecodeforge.catalogue.large_products (
    product_id   BIGSERIAL PRIMARY KEY,
    product_name TEXT      NOT NULL,
    attributes   JSONB     NOT NULL
);

-- GIN INDEX OPTION 1: jsonb_ops (default)
-- Indexes ALL keys, values, and key-value pairs.
-- Supports: @>, ?, ?|, ?&
-- Use when: you query by key existence AND containment
CREATE INDEX idx_products_attrs_gin
    ON io.thecodeforge.catalogue.large_products
    USING GIN (attributes);          -- uses jsonb_ops by default

-- GIN INDEX OPTION 2: jsonb_path_ops
-- Only indexes values at the end of paths.
-- ~30% smaller than jsonb_ops, faster for @> only.
-- Does NOT support ?, ?|, ?&
-- Use when: you ONLY ever use @> containment queries
CREATE INDEX idx_products_attrs_gin_path
    ON io.thecodeforge.catalogue.large_products
    USING GIN (attributes jsonb_path_ops);

-- GIN INDEX OPTION 3: Partial GIN index
-- Only index rows that match a WHERE clause.
-- Use when: you have a mix of active/archived data and only query active rows.
-- This is what saved us in the event ingestion incident.
CREATE INDEX idx_products_active_gin
    ON io.thecodeforge.catalogue.large_products
    USING GIN (attributes jsonb_path_ops)
    WHERE attributes @> '{"in_stock": true}';  -- only index in-stock products

-- GIN INDEX OPTION 4: Generated column + B-tree
-- Best when querying a SINGLE well-known field repeatedly.
-- Much cheaper to maintain than GIN, smaller index size.
ALTER TABLE io.thecodeforge.catalogue.large_products
    ADD COLUMN brand TEXT
    GENERATED ALWAYS AS (attributes ->> 'brand') STORED;  -- materialised at write time

CREATE INDEX idx_products_brand_btree
    ON io.thecodeforge.catalogue.large_products (brand);  -- standard B-tree

-- QUERY: Verify the GIN index is actually being used
-- Run EXPLAIN ANALYZE to confirm index usage
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT product_id, product_name
FROM io.thecodeforge.catalogue.large_products
WHERE attributes @> '{"tags": ["wireless"], "in_stock": true}';

-- QUERY: Demonstrate the generated column approach
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT product_id, product_name
FROM io.thecodeforge.catalogue.large_products
WHERE brand = 'SoundPeak';   -- hits the B-tree index, not GIN

-- BENCHMARK: GIN index impact on insert performance
-- Run BEFORE adding GIN index:
\timing on
INSERT INTO io.thecodeforge.catalogue.large_products (product_name, attributes)
SELECT
    'Product ' || i,
    jsonb_build_object(
        'brand', 'Brand_' || (i % 100),
        'price_usd', (random() * 500)::NUMERIC(10,2),
        'in_stock', (random() > 0.3),
        'tags', jsonb_build_array('tag_' || (i % 20), 'tag_' || (i % 7))
    )
FROM generate_series(1, 100000) AS s(i);
-- Note the time, then DROP the GIN index and run again.
-- Typical results:
--   Without GIN: ~3.2 seconds for 100K inserts
--   With GIN (jsonb_ops):      ~14.8 seconds (4.6x slower)
--   With GIN (jsonb_path_ops): ~11.2 seconds (3.5x slower)

-- MONITORING: Check index size and usage stats
SELECT
    indexrelname                    AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan                        AS times_used,
    idx_tup_read                    AS tuples_read
FROM pg_stat_user_indexes
WHERE relname = 'large_products'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Create a user preferences table with nested JSONB
CREATE TABLE io.thecodeforge.users.preferences (
    user_id   BIGSERIAL PRIMARY KEY,
    prefs     JSONB NOT NULL
);

INSERT INTO io.thecodeforge.users.preferences (prefs) VALUES
('{
    "theme": "dark",
    "notifications": {
        "email": true,
        "sms": false,
        "push": true
    },
    "privacy": {
        "share_location": false,
        "analytics": true
    }
}'),
('{
    "theme": "light",
    "notifications": {
        "email": true,
        "sms": true,
        "push": false
    },
    "privacy": {
        "share_location": true,
        "analytics": true
    }
}');

-- (1) jsonb_set: update email notification to false for user 1
UPDATE io.thecodeforge.users.preferences
SET prefs = jsonb_set(
    prefs,
    '{notifications,email}',
    'false',      -- must be valid JSONB (boolean in this case)
    true          -- create_missing: if key doesn't exist, create it
)
WHERE user_id = 1;

-- (2) jsonb_insert: add a new notification channel 'in_app'
UPDATE io.thecodeforge.users.preferences
SET prefs = jsonb_insert(
    prefs,
    '{notifications}',
    '{"in_app": true}',
    false         -- after: FALSE inserts before the key, TRUE after (but for objects it inserts as a new key)
)
WHERE user_id = 1;

-- (3) jsonb_strip_nulls: remove null-valued keys
-- Useful after partial updates that set some fields to null
UPDATE io.thecodeforge.users.preferences
SET prefs = jsonb_strip_nulls(prefs)
WHERE user_id = 1;

-- (4) || operator (jsonb_concat): merge two JSONB objects
-- Right side wins on key conflicts
SELECT
    '{"a": 1, "b": 2}'::JSONB || '{"b": 3, "c": 4}'::JSONB AS merged;
-- Result: {"a": 1, "b": 3, "c": 4}

-- (5) jsonb_agg: aggregate rows into a JSON array
-- Build a list of all notification preferences for all users
SELECT jsonb_agg(prefs -> 'notifications') AS all_notification_settings
FROM io.thecodeforge.users.preferences;

-- (6) jsonb_object_agg: build JSON object from key-value pairs
-- Get count of users per notification channel (dynamic pivot)
WITH expanded AS (
    SELECT
        user_id,
        kv.key AS channel,
        kv.value::boolean AS enabled
    FROM io.thecodeforge.users.preferences,
         jsonb_each(prefs -> 'notifications') AS kv(key, value)
)
SELECT jsonb_object_agg(channel, cnt) AS counts_per_channel
FROM (
    SELECT channel, COUNT(*) AS cnt
    FROM expanded
    WHERE enabled = true
    GROUP BY channel
) sub;

-- (7) Performance check: how big is the TOAST tuple?
SELECT
    pg_size_pretty(pg_column_size(prefs)) AS pref_size,
    pg_column_size(prefs) > 2000 AS is_toasted
FROM io.thecodeforge.users.preferences
WHERE user_id = 1;

-- (8) Using jsonb_set_lax (PG 14+): controls behaviour on invalid values
-- Equivalent to jsonb_set but with error handling modes
-- 'raise' (default), 'use_json_null', 'delete_key', 'return_target'
UPDATE io.thecodeforge.users.preferences
SET prefs = jsonb_set_lax(
    prefs,
    '{notifications,email}',
    'invalid'::JSONB,  -- this is an object, not a boolean — will raise error by default
    true,
    'use_json_null'    -- set to null instead of erroring
)
WHERE user_id = 2;
-- Better: use 'return_target' to skip the update on invalid value

-- Step 1: Find malformed JSON rows before migration
-- Look for rows that are not valid JSON objects or arrays
SELECT id, payload AS malformed_row
FROM io.thecodeforge.events.ingest
WHERE payload !~ '^\\{' AND payload !~ '^\\[';  -- naïvely check first char

-- More robust: use a test conversion in a transaction to check each row
-- (Rollback so we don't actually change data)
BEGIN;
-- Attempt to cast each row; if any fails, the whole transaction rolls back
-- So we need to catch the exception per row, which requires PL/pgSQL
-- Better approach: create a temporary table with only valid JSON
CREATE TABLE io.thecodeforge.events.ingest_clean AS
SELECT id, payload
FROM io.thecodeforge.events.ingest
WHERE payload @? '$'  -- path expression that tests if it's valid JSONB? Actually ->> returns text, we need to check by trying cast
-- But we can't use WHERE in that way. Use a function:
;

-- Step 2: Write a function to test JSON validity
CREATE OR REPLACE FUNCTION io.thecodeforge.util.is_valid_json(p_text TEXT)
RETURNS BOOLEAN LANGUAGE plpgsql IMMUTABLE AS $func$
BEGIN
    PERFORM p_text::JSONB;
    RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
    RETURN FALSE;
END;
$func$;

-- Use it to find bad rows
SELECT id, payload
FROM io.thecodeforge.events.ingest
WHERE NOT io.thecodeforge.util.is_valid_json(payload);

-- Step 3: Fix or exclude bad rows, then perform the migration
SET maintenance_work_mem = '2GB';  -- speed up the rewrite

ALTER TABLE io.thecodeforge.events.ingest
    ALTER COLUMN payload TYPE JSONB
    USING payload::JSONB;

-- Step 4: Validate no data loss from deduplication
SELECT id, payload::TEXT AS original_json,
       payload AS jsonb_value
FROM io.thecodeforge.events.ingest
WHERE payload::TEXT IS DISTINCT FROM payload::TEXT;  -- this always false; better compare before/after
-- If you kept a backup, compare counts.

-- Step 5: Create indexes after migration
CREATE INDEX idx_ingest_payload_gin
    ON io.thecodeforge.events.ingest
    USING GIN (payload);

-- Step 6: Verify query performance improved
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM io.thecodeforge.events.ingest
WHERE payload @> '{"event_type": "purchase"}';

-- Requires pg_jsonschema extension: CREATE EXTENSION pg_jsonschema;
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL,
  CONSTRAINT valid_product_schema CHECK (
    jsonb_matches_schema(
      '{
        "type": "object",
        "required": ["name", "price", "category"],
        "properties": {
          "name": {"type": "string", "minLength": 1},
          "price": {"type": "number", "minimum": 0},
          "category": {"type": "string", "enum": ["electronics", "clothing", "food"]}
        }
      }',
      data
    )
  )
);

-- This insertion fails:
INSERT INTO products (data) VALUES ('{"name": "Widget", "price": "free", "category": "unknown"}');

-- Bad: indexes every key/value pair in every document
CREATE INDEX idx_full_gin ON events USING GIN (data);

-- Good: only index status='pending' docs on event_date
CREATE INDEX idx_pending_date ON events (
  (data->>'event_date'::date)
) WHERE data->>'status' = 'pending';

-- Query that uses the partial index:
SELECT id, data 
FROM events 
WHERE data->>'status' = 'pending'
  AND (data->>'event_date')::date >= '2025-01-01';