PostgreSQL JSON vs JSONB: Deep Dive into Operators, Indexing & Performance

-- Create a table to compare JSON and JSONB storage behaviour side by side
CREATE TABLE 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 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 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 storage_comparison
WHERE id = 1;

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

INSERT INTO product_catalogue (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 product_catalogue;

-- (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 product_catalogue;

-- (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 product_catalogue
WHERE attributes @> '{"in_stock": true}'          -- containment check
  AND attributes @> '{"tags": ["wireless"]}';      -- array containment works too

-- (4) ? existence: find products that HAVE a 'battery_hours' spec key at top-level
-- Note: ? only checks TOP-LEVEL keys
SELECT product_name
FROM product_catalogue
WHERE attributes ? 'in_stock';  -- all rows have this key, returns all 3

-- (5) Casting ->> result for numeric comparison (common pattern)
SELECT product_name
FROM product_catalogue
WHERE (attributes ->> 'price_usd')::NUMERIC > 100.00;  -- cast TEXT to NUMERIC

-- (6) jsonb_array_elements: unnest a JSON array into rows for per-tag analysis
SELECT
    product_name,
    tag.value ->> 0 AS tag_value   -- each array element becomes a row
FROM product_catalogue,
     jsonb_array_elements(attributes -> 'tags') AS tag(value);

-- Cleaner version using jsonb_array_elements_text for string arrays
SELECT
    product_name,
    tag_text
FROM product_catalogue,
     jsonb_array_elements_text(attributes -> 'tags') AS tag_text
ORDER BY product_name, tag_text;

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

-- Create the table (same structure as before, scaled up)
CREATE TABLE large_product_catalogue (
    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 large_product_catalogue
    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 large_product_catalogue
    USING GIN (attributes jsonb_path_ops);

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

CREATE INDEX idx_products_brand_btree
    ON large_product_catalogue (brand);  -- standard B-tree, fast for equality and range

-- ── 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 large_product_catalogue
WHERE attributes @> '{"tags": ["wireless"], "in_stock": true}';

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

-- ── 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_product_catalogue'
ORDER BY pg_relation_size(indexrelid) DESC;

-- ── A production-grade hybrid schema for a SaaS user profile system ──

CREATE TABLE user_profiles (
    -- Anchor fields: relational columns for everything queryable/joinable
    user_id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    email           TEXT         NOT NULL UNIQUE,
    account_tier    TEXT         NOT NULL DEFAULT 'free'
                                 CHECK (account_tier IN ('free', 'pro', 'enterprise')),
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    last_active_at  TIMESTAMPTZ,

    -- JSONB for flexible, schema-light data that varies per user
    preferences     JSONB        NOT NULL DEFAULT '{}',  -- UI prefs, notification settings
    integrations    JSONB        NOT NULL DEFAULT '{}'   -- third-party OAuth tokens, webhooks
);

-- Index anchor fields the relational way
CREATE INDEX idx_user_profiles_account_tier ON user_profiles (account_tier);
CREATE INDEX idx_user_profiles_last_active  ON user_profiles (last_active_at DESC NULLS LAST);

-- GIN index only on the JSONB we'll actually query
-- We query preferences.theme and preferences.notifications frequently
CREATE INDEX idx_user_preferences_gin
    ON user_profiles USING GIN (preferences jsonb_path_ops);

-- Insert realistic user data
INSERT INTO user_profiles (email, account_tier, preferences, integrations) VALUES
    ('alice@example.com', 'pro',
     '{"theme": "dark", "language": "en", "notifications": {"email": true, "sms": false}}',
     '{"slack": {"workspace": "acme-corp", "channel": "#alerts"}, "github": {"username": "alicecodes"}}'
    ),
    ('bob@example.com', 'free',
     '{"theme": "light", "language": "fr", "notifications": {"email": true, "sms": true}}',
     '{}'
    ),
    ('carol@example.com', 'enterprise',
     '{"theme": "dark", "language": "de", "notifications": {"email": false, "sms": false}, "custom_domain": "carol.io"}',
     '{"slack": {"workspace": "bigcorp", "channel": "#ops"}}'
    );

-- ── UPDATING JSONB SAFELY: use jsonb_set, not full-document replace ──
-- BAD: rewrites the ENTIRE preferences blob even if 99% is unchanged
-- UPDATE user_profiles SET preferences = '{"theme":"system", ...all other keys...}' WHERE email = 'alice@example.com';

-- GOOD: surgical update using jsonb_set(target, path, new_value, create_missing)
UPDATE user_profiles
SET preferences = jsonb_set(
    preferences,                       -- the existing JSONB column
    '{notifications, sms}',            -- path as text array
    'true'::jsonb,                     -- new value (must be valid JSONB)
    false                              -- don't create if path doesn't exist
)
WHERE email = 'alice@example.com';

-- ── QUERYING: mix relational and JSONB filters naturally ──
-- Find pro/enterprise users who prefer dark mode and have Slack connected
SELECT
    email,
    account_tier,
    preferences ->> 'theme'      AS theme,
    preferences ->> 'language'   AS language,
    integrations -> 'slack' ->> 'workspace' AS slack_workspace
FROM user_profiles
WHERE account_tier IN ('pro', 'enterprise')   -- hits B-tree index (relational column)
  AND preferences @> '{"theme": "dark"}'       -- hits GIN index (JSONB)
  AND integrations ? 'slack'                   -- existence check (GIN jsonb_ops needed here!)
ORDER BY last_active_at DESC NULLS LAST;

-- ── BUILDING ANALYTICS: aggregate JSONB data across rows ──
-- Count users by preferred language
SELECT
    preferences ->> 'language'   AS language,
    COUNT(*)                      AS user_count,
    COUNT(*) FILTER (WHERE account_tier = 'pro') AS pro_count
FROM user_profiles
GROUP BY preferences ->> 'language'
ORDER BY user_count DESC;