JSON in Databases: MongoDB, PostgreSQL & Performance Optimization 2026

# JSON in Databases: MongoDB, PostgreSQL & Performance Optimization 2026

Modern databases provide robust JSON support—MongoDB with native document storage, PostgreSQL with JSONB columns. This guide covers schema design, querying, indexing, and performance optimization for JSON data in production databases.

Table of Contents

---

MongoDB Document Design

Basic Document Structure

// User document
{
  _id: ObjectId("507f1f77bcf86cd799439011"),
  email: "alice@example.com",
  name: "Alice Smith",
  profile: {
    bio: "Software engineer",
    location: "San Francisco, CA",
    website: "https://alice.dev"
  },
  settings: {
    theme: "dark",
    notifications: true,
    language: "en"
  },
  metadata: {
    createdAt: ISODate("2026-01-15T10:00:00Z"),
    updatedAt: ISODate("2026-03-24T10:00:00Z"),
    lastLogin: ISODate("2026-03-24T09:30:00Z")
  }
}

Embedding vs Referencing

// Blog post with embedded comments
{
  _id: ObjectId("..."),
  title: "Hello World",
  content: "...",
  author: {
    _id: ObjectId("..."),
    name: "Alice"
  },
  comments: [
    {
      _id: ObjectId("..."),
      userId: ObjectId("..."),
      text: "Great post!",
      createdAt: ISODate("...")
    }
  ]
}

// Post document
{
  _id: ObjectId("..."),
  title: "Hello World",
  content: "...",
  authorId: ObjectId("...")
}

// Comment documents
{
  _id: ObjectId("..."),
  postId: ObjectId("..."),
  userId: ObjectId("..."),
  text: "Great post!",
  createdAt: ISODate("...")
}

Schema Patterns

// Different product types in same collection
{
  _id: ObjectId("..."),
  type: "book",
  title: "MongoDB Guide",
  isbn: "978-1234567890",
  pages: 400
}

{
  _id: ObjectId("..."),
  type: "ebook",
  title: "PostgreSQL Essentials",
  format: "PDF",
  fileSize: 5242880
}

{
  _id: ObjectId("..."),
  name: "Laptop",
  category: "electronics",
  attributes: [
    { key: "brand", value: "Apple" },
    { key: "processor", value: "M2" },
    { key: "ram", value: "16GB" }
  ]
}

---

MongoDB Queries & Aggregation

Basic Queries

const { MongoClient } = require('mongodb');

const client = new MongoClient('mongodb://localhost:27017');
await client.connect();

const db = client.db('myapp');
const users = db.collection('users');

// Find one
const user = await users.findOne({ email: 'alice@example.com' });

// Find many
const activeUsers = await users.find({ 'settings.notifications': true }).toArray();

// Nested field query
const sfUsers = await users.find({ 'profile.location': /San Francisco/ }).toArray();

// Array contains
const posts = db.collection('posts');
const taggedPosts = await posts.find({ tags: 'mongodb' }).toArray();

// Array size
const postsWithComments = await posts.find({
  comments: { $exists: true, $ne: [] }
}).toArray();

Aggregation Pipeline

// Count users by location
const locationStats = await users.aggregate([
  {
    $group: {
      _id: '$profile.location',
      count: { $sum: 1 }
    }
  },
  { $sort: { count: -1 } },
  { $limit: 10 }
]).toArray();

// User activity summary
const activitySummary = await users.aggregate([
  {
    $lookup: {
      from: 'posts',
      localField: '_id',
      foreignField: 'authorId',
      as: 'posts'
    }
  },
  {
    $project: {
      name: 1,
      email: 1,
      postCount: { $size: '$posts' },
      lastPost: { $arrayElemAt: ['$posts.createdAt', -1] }
    }
  },
  { $sort: { postCount: -1 } }
]).toArray();

// Complex aggregation with multiple stages
const topAuthors = await posts.aggregate([
  { $unwind: '$tags' },
  {
    $group: {
      _id: {
        authorId: '$authorId',
        tag: '$tags'
      },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: '$_id.authorId',
      tags: {
        $push: {
          tag: '$_id.tag',
          count: '$count'
        }
      },
      totalPosts: { $sum: '$count' }
    }
  },
  { $sort: { totalPosts: -1 } },
  { $limit: 10 },
  {
    $lookup: {
      from: 'users',
      localField: '_id',
      foreignField: '_id',
      as: 'author'
    }
  },
  { $unwind: '$author' },
  {
    $project: {
      authorName: '$author.name',
      totalPosts: 1,
      tags: 1
    }
  }
]).toArray();

---

PostgreSQL JSONB

Creating Tables with JSONB

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  profile JSONB DEFAULT '{}',
  settings JSONB DEFAULT '{}',
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Insert data
INSERT INTO users (email, name, profile, settings)
VALUES (
  'alice@example.com',
  'Alice Smith',
  '{"bio": "Software engineer", "location": "San Francisco"}',
  '{"theme": "dark", "notifications": true}'
);

Querying JSONB

-- Extract field
SELECT 
  name,
  profile->>'bio' AS bio,
  profile->>'location' AS location
FROM users;

-- Filter by JSONB field
SELECT  FROM users

WHERE settings->>'theme' = 'dark';

-- Nested field access
SELECT  FROM users
WHERE profile->'address'->>'city' = 'San Francisco';

-- Check if key exists
SELECT  FROM users

WHERE profile ? 'bio';

-- Contains (@>)
SELECT  FROM users
WHERE settings @> '{"notifications": true}';

-- JSON array contains
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT,
  tags JSONB DEFAULT '[]'
);

INSERT INTO posts (title, tags)
VALUES ('Hello World', '["mongodb", "postgresql"]');

-- Find posts with specific tag
SELECT  FROM posts

WHERE tags @> '["mongodb"]';

-- Array element access
SELECT 
  title,
  tags->0 AS first_tag,
  jsonb_array_length(tags) AS tag_count
FROM posts;

-- Set entire JSONB column
UPDATE users
SET settings = '{"theme": "light", "notifications": false}'
WHERE id = 1;

-- Update specific field (preserves other fields)
UPDATE users
SET settings = jsonb_set(
  settings,
  '{theme}',
  '"light"'
)
WHERE id = 1;

-- Add new field
UPDATE users
SET profile = profile || '{"twitter": "@alice"}'
WHERE id = 1;

-- Remove field
UPDATE users
SET profile = profile - 'twitter'
WHERE id = 1;

-- Nested update
UPDATE users
SET profile = jsonb_set(
  profile,
  '{address, city}',
  '"New York"'
)
WHERE id = 1;

-- Extract keys
SELECT jsonb_object_keys(settings) AS setting_key
FROM users;

-- Build JSON object from columns
SELECT jsonb_build_object(
  'name', name,
  'email', email,
  'location', profile->>'location'
) AS user_summary
FROM users;

-- Aggregate to JSON array
SELECT jsonb_agg(
  jsonb_build_object(
    'id', id,
    'name', name,
    'email', email
  )
) AS users_json
FROM users;

// Single field index
await users.createIndex({ email: 1 });

// Compound index
await posts.createIndex({ authorId: 1, createdAt: -1 });

// Nested field index
await users.createIndex({ 'profile.location': 1 });

// Array field index (multikey index)
await posts.createIndex({ tags: 1 });

// Text index for search
await posts.createIndex({ 
  title: 'text',
  content: 'text'
});

// Text search
const results = await posts.find({
  $text: { $search: 'mongodb performance' }
}).toArray();

// Partial index (only index subset)
await users.createIndex(
  { 'settings.notifications': 1 },
  { 
    partialFilterExpression: {
      'settings.notifications': true
    }
  }
);

// TTL index (auto-delete documents)
await sessions.createIndex(
  { expiresAt: 1 },
  { expireAfterSeconds: 0 }
);

-- GIN index (generalized inverted index)
CREATE INDEX idx_users_settings ON users USING GIN (settings);

-- Enables efficient queries like:
SELECT  FROM users
WHERE settings @> '{"notifications": true}';

-- Path-specific index
CREATE INDEX idx_users_theme ON users ((settings->>'theme'));

-- Enables:
SELECT  FROM users

WHERE settings->>'theme' = 'dark';

-- Expression index
CREATE INDEX idx_users_location_lower 
ON users (LOWER(profile->>'location'));

-- Multicolumn index with JSONB
CREATE INDEX idx_users_email_theme
ON users (email, (settings->>'theme'));

// Only return needed fields
const users = await db.collection('users').find(
  { 'settings.notifications': true },
  { projection: { name: 1, email: 1, _id: 0 } }
).toArray();

const explain = await posts.find({ tags: 'mongodb' }).explain('executionStats');
console.log(explain.executionStats);
// Check: totalDocsExamined vs nReturned

const users = await User.find({ active: true }).lean();
// Returns plain JS objects instead of Mongoose documents

EXPLAIN ANALYZE
SELECT  FROM users
WHERE settings @> '{"notifications": true}';

-- Look for:
-- - Sequential Scan (bad, add index)
-- - Index Scan (good)
-- - Bitmap Index Scan (good for OR conditions)

-- Update statistics
ANALYZE users;

-- Reclaim space
VACUUM users;

-- Both at once
VACUUM ANALYZE users;

CREATE MATERIALIZED VIEW user_stats AS
SELECT 
  DATE_TRUNC('day', created_at) AS date,
  COUNT() AS user_count,

  COUNT() FILTER (WHERE settings @> '{"notifications": true}') AS notif_enabled
FROM users
GROUP BY date;

CREATE INDEX ON user_stats (date);

-- Refresh periodically
REFRESH MATERIALIZED VIEW user_stats;

---

Schema Evolution

MongoDB Schema Versioning

// Add schema version field
{
  _id: ObjectId("..."),
  schemaVersion: 2,
  email: "alice@example.com",
  name: {  // Changed from flat 'name' to object in v2
    first: "Alice",
    last: "Smith"
  }
}

// Migration script
async function migrateUsers() {
  const users = await db.collection('users').find({ schemaVersion: { $ne: 2 } });

  for await (const user of users) {
    if (!user.schemaVersion || user.schemaVersion === 1) {
      const [first, ...rest] = (user.name || '').split(' ');

      await db.collection('users').updateOne(
        { _id: user._id },
        {
          $set: {
            name: {
              first: first || '',
              last: rest.join(' ') || ''
            },
            schemaVersion: 2
          }
        }
      );
    }
  }
}

PostgreSQL Schema Changes

-- Add new JSONB field with default
ALTER TABLE users
ADD COLUMN preferences JSONB DEFAULT '{}';

-- Migrate data from settings to preferences
UPDATE users
SET preferences = settings - 'theme' - 'notifications';

-- Add constraint to validate JSONB structure
ALTER TABLE users
ADD CONSTRAINT valid_settings CHECK (
  settings ? 'theme' AND
  settings ? 'notifications'
);

---

Hybrid Approaches

PostgreSQL with JSON and Relational

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  category_id INTEGER REFERENCES categories(id),
  price DECIMAL(10, 2) NOT NULL,
  specs JSONB DEFAULT '{}',  -- Flexible attributes
  created_at TIMESTAMP DEFAULT NOW()
);

-- Query combining relational and JSON
SELECT 
  p.name,
  c.name AS category,
  p.price,
  p.specs->>'brand' AS brand,
  p.specs->>'model' AS model
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE 
  c.name = 'Laptops'
  AND (p.specs->>'ram')::INTEGER >= 16;

MongoDB with References

// Use references for frequently changing data
// Use embedding for rarely changing data

const OrderSchema = new Schema({
  userId: { type: ObjectId, ref: 'User' },  // Reference
  items: [{  // Embedded (snapshot at order time)
    productId: ObjectId,
    name: String,
    price: Number,
    quantity: Number
  }],
  total: Number,
  status: String,
  createdAt: Date
});

// Populate reference when needed
const order = await Order.findById(orderId).populate('userId');
console.log(order.userId.name);

---

Production Best Practices

MongoDB

const client = new MongoClient(uri, {
  maxPoolSize: 50,
  minPoolSize: 10,
  serverSelectionTimeoutMS: 5000
});

PostgreSQL

-- Enable pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- Find slow queries
SELECT 
  query,
  mean_exec_time,
  calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

---

Conclusion

Choose the right database for JSON data:

• Design schema based on query patterns
• Index frequently queried JSON fields
• Monitor and optimize query performance
• Plan for schema evolution from day one

Master JSON in databases for flexible, scalable applications!

Related Resources

• Large JSON Files
• JSON Schema Patterns
• JSON in Node.js