I Replaced MongoDB with a Single Postgres Table

CREATE TABLE MongoDB (
  _id  UUID PRIMARY KEY,
  data JSONB
);

-- The containment operator @>
-- "Does this JSON contain this structure?"
SELECT * FROM users
WHERE preferences @> '{"theme": "dark"}';

-- The existence operator ?
-- "Does this key exist?"
SELECT * FROM products
WHERE attributes ? 'wireless';

-- The arrow operators -> and ->>
-- -> returns JSON, ->> returns text
SELECT
  data->>'name' AS name,
  data->'address'->>'city' AS city
FROM users;

-- The path operator #>
-- Navigate deep into nested JSON
SELECT * FROM events
WHERE data #> '{user,settings,notifications}' = 'true';

-- Index a specific field
CREATE INDEX idx_user_email ON users ((data->>'email'));

-- Index for existence queries
CREATE INDEX idx_attributes ON products USING GIN (attributes);

-- Index for containment queries
CREATE INDEX idx_preferences ON users USING GIN (preferences);

-- Add full-text search to JSON fields
CREATE INDEX idx_content_search ON articles
USING GIN (to_tsvector('english', data->>'content'));

-- Search like a boss
SELECT * FROM articles
WHERE to_tsvector('english', data->>'content') @@ plainto_tsquery('postgres jsonb amazing');

-- The hybrid approach: structured + flexible
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  preferences JSONB DEFAULT '{}',
  metadata JSONB DEFAULT '{}'
);

-- Insert a user with preferences
INSERT INTO users (email, preferences) VALUES (
  '[email protected]',
  '{
    "theme": "dark",
    "notifications": {
      "email": true,
      "push": false,
      "frequency": "daily"
    },
    "features": {
      "beta": true,
      "advancedAnalytics": false
    }
  }'
);

-- Query users who have dark theme AND email notifications
SELECT email FROM users
WHERE preferences @> '{"theme": "dark", "notifications": {"email": true}}';

-- Update nested preferences
UPDATE users
SET preferences = jsonb_set(
  preferences,
  '{notifications,push}',
  'true'
)
WHERE email = '[email protected]';

CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_type TEXT NOT NULL,
  user_id UUID,
  occurred_at TIMESTAMPTZ DEFAULT NOW(),
  data JSONB NOT NULL
);

-- Index for fast event type + data queries
CREATE INDEX idx_events_type_data ON events (event_type)
WHERE event_type IN ('purchase', 'signup', 'feedback');
CREATE INDEX idx_events_data ON events USING GIN (data);

-- Insert different event types with different schemas
INSERT INTO events (event_type, user_id, data) VALUES
('signup', 'user-123', '{
  "source": "google",
  "campaign": "summer-2024",
  "referrer": "blog-post"
}'),
('purchase', 'user-123', '{
  "items": [
    {"sku": "PROD-1", "quantity": 2, "price": 49.99},
    {"sku": "PROD-2", "quantity": 1, "price": 19.99}
  ],
  "discount": "SUMMER20",
  "total": 99.97
}'),
('feedback', 'user-123', '{
  "type": "feature_request",
  "title": "Add dark mode",
  "priority": "high",
  "tags": ["ui", "accessibility"]
}');

-- Find all purchases with a specific discount
SELECT * FROM events
WHERE event_type = 'purchase'
AND data @> '{"discount": "SUMMER20"}';

-- Calculate total revenue from events
SELECT SUM((data->>'total')::NUMERIC) AS total_revenue
FROM events
WHERE event_type = 'purchase'
AND occurred_at >= NOW() - INTERVAL '30 days';

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  attributes JSONB DEFAULT '{}'
);

-- Insert products with completely different attributes
INSERT INTO products (name, price, attributes) VALUES
('iPhone 15', 999.00, '{
  "brand": "Apple",
  "storage": "256GB",
  "color": "Blue",
  "5g": true,
  "screen": {
    "size": "6.1 inches",
    "type": "OLED",
    "resolution": "2532x1170"
  }
}'),
('Nike Air Max', 120.00, '{
  "brand": "Nike",
  "size": "10",
  "color": "Black/White",
  "material": "Mesh",
  "style": "Running"
}'),
('The Pragmatic Programmer', 39.99, '{
  "author": "David Thomas",
  "isbn": "978-0135957059",
  "pages": 352,
  "publisher": "Addison-Wesley",
  "edition": "2nd"
}');

-- Find all products with 5G
SELECT name, price FROM products WHERE attributes @> '{"5g": true}';

-- Find products by brand
SELECT * FROM products WHERE attributes->>'brand' = 'Apple';

-- Complex query: Find all products with screens larger than 6 inches
SELECT name, attributes->'screen'->>'size' AS screen_size
FROM products
WHERE (attributes->'screen'->>'size')::FLOAT > 6.0;

CREATE TABLE feature_flags (
  key TEXT PRIMARY KEY,
  config JSONB
);

INSERT INTO feature_flags VALUES
('new_dashboard', '{
  "enabled": true,
  "rollout_percentage": 25,
  "whitelist_users": ["user-123", "user-456"],
  "blacklist_countries": ["XX"],
  "start_date": "2024-01-01",
  "end_date": null
}');

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),  -- Real FK
  total NUMERIC(10,2) NOT NULL,        -- For fast aggregations
  status TEXT NOT NULL,                -- For indexed lookups
  created_at TIMESTAMPTZ DEFAULT NOW(),
  line_items JSONB,                    -- Flexible item details
  metadata JSONB                       -- Everything else
);

# Pseudo-code for the brave
import psycopg2
from pymongo import MongoClient

# Connect to both
mongo = MongoClient('mongodb://localhost:27017/')
postgres = psycopg2.connect("postgresql://...")

# Migrate with style
for doc in mongo.mydb.mycollection.find():
    postgres.execute(
        "INSERT INTO my_table (id, data) VALUES (%s, %s)",
        (str(doc['_id']), Json(doc))
    )

-- Create a table
CREATE TABLE magic (
  id SERIAL PRIMARY KEY,
  data JSONB
);

-- Insert nested, complex data
INSERT INTO magic (data) VALUES
('{"user": {"name": "Alice", "scores": [10, 20, 30], "preferences": {"level": "expert"}}}'),
('{"user": {"name": "Bob", "scores": [5, 15, 25], "preferences": {"level": "beginner"}}}');

-- Mind-blowing query: Find users with average score > 15 AND expert level
SELECT
  data->'user'->>'name' AS name,
  (SELECT AVG(value::INT) FROM jsonb_array_elements_text(data->'user'->'scores') AS value) AS avg_score
FROM magic
WHERE data @> '{"user": {"preferences": {"level": "expert"}}}'
AND (
  SELECT AVG(value::INT)
  FROM jsonb_array_elements_text(data->'user'->'scores') AS value
) > 15;

-- Operators
@>  -- Contains
<@  -- Is contained by
?   -- Key exists
?|  -- Any key exists
?&  -- All keys exist
||  -- Concatenate
-   -- Delete key/element
#-  -- Delete at path

-- Functions
jsonb_set()           -- Update value at path
jsonb_insert()        -- Insert value at path
jsonb_strip_nulls()   -- Remove null values
jsonb_pretty()        -- Format for humans
jsonb_agg()          -- Aggregate into array
jsonb_object_agg()   -- Aggregate into object

-- Performance tips
1. Use GIN indexes for @> and ? operators
2. Use btree indexes for ->> on specific fields
3. Partial indexes for common queries
4. Don't nest more than 3-4 levels deep
5. Keep JSONB documents under 1MB