PostgreSQL JSON Columns: When to Use JSONB vs Relational Tables

#database #postgres #backend #webdev

PostgreSQL JSON Columns: When to Use JSONB vs Relational Tables

JSONB gives you document flexibility inside a relational database. Here's when it's the right tool.

JSONB vs JSON

Always use JSONB, not JSON:

JSONB is stored as binary — faster for queries
JSONB deduplicates keys, normalizes whitespace
JSON is stored as-is — only faster for writes you never query

When JSONB Makes Sense

Use JSONB for:

Schema-flexible data (user settings, feature configs, plugin metadata)
Event payloads where the shape varies by type
Third-party API responses you need to store but not query deeply
Polymorphic attributes

Use relational tables when:

You query individual fields frequently
You need foreign keys to this data
You need row-level permissions

Basic JSONB Operations

-- Create table with JSONB column
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL,
  settings JSONB DEFAULT '{}'
);

-- Insert
INSERT INTO users (email, settings)
VALUES ('alice@example.com', '{"theme": "dark", "notifications": {"email": true}}');

-- Query a nested field
SELECT email, settings->>'theme' AS theme FROM users;

-- Filter by nested value
SELECT * FROM users WHERE settings->>'theme' = 'dark';

-- Update a single key without replacing the whole object
UPDATE users SET settings = settings || '{"theme": "light"}' WHERE id = $1;

Indexing JSONB

-- GIN index for @> (contains) queries
CREATE INDEX idx_users_settings ON users USING gin(settings);

-- Expression index for frequent key access
CREATE INDEX idx_users_theme ON users ((settings->>'theme'));

-- Query that uses the GIN index
SELECT * FROM users WHERE settings @> '{"notifications": {"email": true}}';

Prisma with JSONB

// schema.prisma
model User {
  id       String @id @default(uuid())
  email    String @unique
  settings Json   @default("{}")
}

// Update nested settings safely
await prisma.user.update({
  where: { id: userId },
  data: {
    settings: {
      ...currentSettings,
      theme: 'dark',
    },
  },
});

// Query users with specific setting
const darkModeUsers = await prisma.user.findMany({
  where: {
    settings: { path: ['theme'], equals: 'dark' },
  },
});