PostgreSQL vs MySQL: In-Depth Technical & Analytics Comparison

-- PostgreSQL: Querying JSONB with GIN Index
-- Create GIN index on entire JSONB payload
CREATE INDEX idx_user_metadata ON users USING GIN (metadata);

-- Fast sub-millisecond query using JSON containment operator (@>)
SELECT 
    id, 
    email, 
    metadata->>'plan' AS subscription_tier
FROM users
WHERE metadata @> '{"preferences": {"newsletter": true}}'
  AND (metadata->'billing'->>'credits')::int > 100;

-- MySQL: Querying JSON with Generated Column Index
-- MySQL requires generating a virtual column to index nested values
ALTER TABLE users 
ADD COLUMN newsletter_pref BOOLEAN 
GENERATED ALWAYS AS (metadata->>'$.preferences.newsletter') VIRTUAL;

CREATE INDEX idx_newsletter ON users(newsletter_pref);

-- Query using JSON_EXTRACT / ->> syntax
SELECT 
    id, 
    email, 
    metadata->>'$.plan' AS subscription_tier
FROM users
WHERE newsletter_pref = TRUE
  AND CAST(metadata->>'$.billing.credits' AS UNSIGNED) > 100;