PostgreSQL JSONB Patterns: Operators, Indexing, and Schema-on-Read Design

PostgreSQL's JSONB column type is one of its most misused features. Teams reach for it when they want flexibility without relational constraints, then discover their queries are slow, their indexes bloated, and their data inconsistent.

Used correctly, JSONB is a precision tool for specific problems: storing heterogeneous metadata, building EAV-style flexible attributes, and handling evolving schemas without migrations. This post covers the patterns and pitfalls.

---

JSONB vs JSON

Always use JSONB, not JSON:

The only reason to use JSON is if you need to preserve key order or duplicate keys — which is almost never a valid production requirement.

---

Operators and Query Patterns

-- Sample table
CREATE TABLE products (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL,
  name        TEXT NOT NULL,
  attributes  JSONB NOT NULL DEFAULT '{}',
  metadata    JSONB NOT NULL DEFAULT '{}'
);

-- Insert example
INSERT INTO products (tenant_id, name, attributes) VALUES (
  'tenant-1',
  'Pro Plan',
  '{
    "price": 99,
    "currency": "USD",
    "features": ["api_access", "custom_domain", "sso"],
    "limits": {
      "users": 25,
      "projects": 20,
      "storage_gb": 100
    },
    "billing_cycle": "monthly",
    "trial_eligible": true
  }'
);

-- Arrow operators: extract value
SELECT attributes -> 'price'                    -- Returns JSONB: 99
SELECT attributes ->> 'currency'               -- Returns TEXT: 'USD'
SELECT attributes -> 'limits' -> 'users'       -- Nested: JSONB 25
SELECT attributes -> 'limits' ->> 'users'      -- Nested: TEXT '25'

-- Path operators
SELECT attributes #> '{limits, users}'         -- Returns JSONB: 25
SELECT attributes #>> '{limits, users}'        -- Returns TEXT: '25'

-- Array operations
SELECT attributes -> 'features' ->> 0          -- First array element: 'api_access'
SELECT jsonb_array_length(attributes -> 'features')  -- Array length: 3

-- Containment: does JSONB contain this subset?
SELECT * FROM products
WHERE attributes @> '{"billing_cycle": "monthly", "trial_eligible": true}';

-- Key existence
SELECT * FROM products WHERE attributes ? 'trial_eligible';       -- Has key?
SELECT * FROM products WHERE attributes ?| ARRAY['sso', 'mfa'];   -- Has any key?
SELECT * FROM products WHERE attributes ?& ARRAY['price', 'currency']; -- Has all keys?

-- Path existence
SELECT * FROM products WHERE attributes @? '$.features[*] ? (@ == "sso")';

-- Comparison operators (cast to typed value for correct comparison)
SELECT * FROM products WHERE (attributes ->> 'price')::numeric > 50;
SELECT * FROM products WHERE (attributes -> 'limits' ->> 'users')::int >= 25;

-- Update specific key without rewriting entire document
UPDATE products
SET attributes = jsonb_set(attributes, '{limits, users}', '50')
WHERE id = $1;

-- Remove a key
UPDATE products
SET attributes = attributes - 'trial_eligible'
WHERE id = $1;

-- Merge/extend JSONB (concatenation operator)
UPDATE products
SET attributes = attributes || '{"new_feature": true, "version": 2}'
WHERE id = $1;

---

GIN Index Strategies

-- Default GIN index: supports @>, ?, ?|, ?& operators
-- Best for containment queries
CREATE INDEX idx_products_attributes_gin
  ON products USING GIN (attributes);

-- GIN with jsonb_path_ops: SMALLER index, only supports @> operator
-- 2-3x smaller than default, slightly faster for containment
CREATE INDEX idx_products_attributes_gin_path
  ON products USING GIN (attributes jsonb_path_ops);

-- Which to choose:
-- • Default GIN: if you use ?, ?|, ?& (key existence)
-- • jsonb_path_ops: if you ONLY use @> (containment)

-- Expression index: when you always query a specific path
-- Much smaller and faster than full GIN for single-key queries
CREATE INDEX idx_products_price
  ON products ((attributes ->> 'price'));

CREATE INDEX idx_products_billing_cycle
  ON products ((attributes ->> 'billing_cycle'));

-- Partial index: filter + expression — smallest possible index
CREATE INDEX idx_products_active_price
  ON products ((attributes ->> 'price'))
  WHERE (attributes ->> 'billing_cycle') = 'monthly';

-- Composite: multiple extracted fields (B-tree)
CREATE INDEX idx_products_plan_price
  ON products (
    (attributes ->> 'billing_cycle'),
    ((attributes ->> 'price')::numeric)
  );

Choosing the Right Index

-- EXPLAIN ANALYZE to verify index usage

-- Query: find all monthly products over $50
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM products
WHERE attributes @> '{"billing_cycle": "monthly"}'
  AND (attributes ->> 'price')::numeric > 50;

-- If plan shows Seq Scan instead of Index Scan:
-- 1. Run ANALYZE products; to update statistics
-- 2. Check if the index exists: \d products
-- 3. Check selectivity: high-cardinality queries use indexes better
-- 4. For small tables (< 10k rows), Postgres may prefer Seq Scan

-- Force index for testing:
SET enable_seqscan = OFF;
EXPLAIN SELECT * FROM products WHERE attributes @> '{"billing_cycle": "monthly"}';
SET enable_seqscan = ON;

---

Schema-on-Read vs Schema-on-Write

This is the core architectural decision when considering JSONB:

Schema-on-write (relational columns): Schema defined at table creation. Invalid data rejected by database. Queries are simple and fast. Requires migration for schema changes.

Schema-on-read (JSONB): Schema defined at query time. Any data accepted by database. Application validates. No migration for new fields.

// Schema-on-read: validate when reading, not when writing
import { z } from "zod";

const ProductAttributesV1 = z.object({
  price: z.number().positive(),
  currency: z.string().length(3),
  features: z.array(z.string()),
  billing_cycle: z.enum(["monthly", "annual"]),
  trial_eligible: z.boolean().optional(),
});

const ProductAttributesV2 = ProductAttributesV1.extend({
  // New field added without migration
  max_api_calls_per_day: z.number().int().optional(),
  support_tier: z.enum(["standard", "priority"]).optional(),
});

// Read with version detection
async function getProductAttributes(id: string) {
  const { rows } = await db.query(
    "SELECT attributes FROM products WHERE id = $1",
    [id]
  );

  const raw = rows[0]?.attributes;
  if (!raw) return null;

  // Try V2 first, fall back to V1
  const v2Result = ProductAttributesV2.safeParse(raw);
  if (v2Result.success) return { version: 2, data: v2Result.data };

  const v1Result = ProductAttributesV1.safeParse(raw);
  if (v1Result.success) return { version: 1, data: v1Result.data };

  throw new Error(`Unknown product attributes format: ${JSON.stringify(raw)}`);
}

When to Use Each Approach

Use relational columns when:
✅ Data is queried with range comparisons (dates, numbers, enums)
✅ You need foreign key constraints
✅ You need uniqueness constraints
✅ The schema is stable and known upfront
✅ You need to aggregate/report across the column

Use JSONB when:
✅ Attributes vary significantly between rows (EAV/flexible attributes)
✅ Schema evolves frequently and migrations are costly
✅ You only query by equality/containment (not range)
✅ The data is truly heterogeneous (audit logs, API responses, metadata)
✅ You're storing opaque blobs that you need to pass through unchanged

---

EAV Alternative: JSONB for Flexible Attributes

Entity-Attribute-Value (EAV) tables are the traditional approach for flexible attributes, but JSONB is almost always better:

-- ❌ Traditional EAV: complex queries, poor performance
CREATE TABLE product_attributes (
  product_id UUID REFERENCES products(id),
  key        TEXT NOT NULL,
  value      TEXT,
  PRIMARY KEY (product_id, key)
);

-- Query: products where price > 50 AND features includes sso
-- Requires multiple self-joins — slow and complex
SELECT p.id FROM products p
JOIN product_attributes pa1 ON pa1.product_id = p.id AND pa1.key = 'price'
JOIN product_attributes pa2 ON pa2.product_id = p.id AND pa2.key = 'billing_cycle'
WHERE pa1.value::numeric > 50
  AND pa2.value = 'monthly';

-- ✅ JSONB: single table, fast query
SELECT id FROM products
WHERE (attributes ->> 'price')::numeric > 50
  AND attributes @> '{"billing_cycle": "monthly"}';

---

JSONB Aggregation and Reporting

-- Expand JSONB array to rows
SELECT
  p.id,
  p.name,
  feature
FROM products p,
  jsonb_array_elements_text(p.attributes -> 'features') AS feature;
-- Returns one row per feature per product

-- Count products by feature
SELECT
  feature,
  COUNT(*) AS product_count
FROM products p,
  jsonb_array_elements_text(p.attributes -> 'features') AS feature
GROUP BY feature
ORDER BY product_count DESC;

-- Aggregate JSONB into report
SELECT
  attributes ->> 'billing_cycle' AS cycle,
  COUNT(*) AS count,
  AVG((attributes ->> 'price')::numeric) AS avg_price,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY (attributes ->> 'price')::numeric
  ) AS median_price
FROM products
WHERE attributes ? 'price'
GROUP BY 1
ORDER BY count DESC;

-- Build JSONB from query results
SELECT jsonb_agg(
  jsonb_build_object(
    'id', id,
    'name', name,
    'price', attributes ->> 'price',
    'features', attributes -> 'features'
  )
) AS products_json
FROM products
WHERE attributes @> '{"billing_cycle": "monthly"}';

---

TypeScript Query Builder for JSONB

// src/db/jsonb-query-builder.ts
// Type-safe JSONB query construction

type JsonPath = string | string[];

interface JsonbCondition {
  path?: JsonPath;
  operator:
    | "contains"      // @>
    | "contained_by"  // <@
    | "has_key"       // ?
    | "has_any_keys"  // ?|
    | "has_all_keys"  // ?&
    | "path_exists"   // @?
    | "equals"        // ->> = $value
    | "gt" | "gte" | "lt" | "lte" // numeric comparison on extracted text
    | "matches";      // ILIKE on extracted text
  value: unknown;
  castAs?: "numeric" | "integer" | "boolean" | "date";
}

export function buildJsonbWhere(
  column: string,
  conditions: JsonbCondition[]
): { sql: string; params: unknown[] } {
  const clauses: string[] = [];
  const params: unknown[] = [];
  let paramIndex = 1;

  for (const cond of conditions) {
    const pathExpr = Array.isArray(cond.path)
      ? `${column} #>> '{${cond.path.join(",")}}'`
      : cond.path
      ? `${column} ->> '${cond.path}'`
      : column;

    switch (cond.operator) {
      case "contains":
        clauses.push(`${column} @> $${paramIndex++}::jsonb`);
        params.push(JSON.stringify(cond.value));
        break;
      case "has_key":
        clauses.push(`${column} ? $${paramIndex++}`);
        params.push(cond.value);
        break;
      case "equals":
        clauses.push(`${pathExpr} = $${paramIndex++}`);
        params.push(cond.value);
        break;
      case "gt":
      case "gte":
      case "lt":
      case "lte": {
        const ops = { gt: ">", gte: ">=", lt: "<", lte: "<=" };
        const cast = cond.castAs ?? "numeric";
        clauses.push(`(${pathExpr})::${cast} ${ops[cond.operator]} $${paramIndex++}`);
        params.push(cond.value);
        break;
      }
      case "matches":
        clauses.push(`${pathExpr} ILIKE $${paramIndex++}`);
        params.push(`%${cond.value}%`);
        break;
    }
  }

  return {
    sql: clauses.join(" AND "),
    params,
  };
}

// Usage
const { sql, params } = buildJsonbWhere("attributes", [
  { operator: "contains", value: { billing_cycle: "monthly" } },
  { path: "price", operator: "gt", value: 50, castAs: "numeric" },
  { path: ["limits", "users"], operator: "gte", value: 10, castAs: "integer" },
]);

const { rows } = await db.query(
  `SELECT * FROM products WHERE ${sql}`,
  params
);

---

Performance Reference

---

See Also

• PostgreSQL Performance Tuning — query optimization
• PostgreSQL Extensions for Production — advanced PG features
• PostgreSQL Full-Text Search — text search with JSONB
• Multi-Tenant SaaS Architecture — JSONB for tenant attributes

---

Working With Viprasol

JSONB is powerful but requires careful index design to stay performant at scale. Our database engineers design JSONB schemas with appropriate GIN indexes, expression indexes, and query patterns that maintain sub-10ms response times at millions of rows.

Database architecture services → | Talk to our engineers →