How do I use JSONB in PostgreSQL for app settings and metadata?

PostgreSQL JSONB is a great fit for app settings, user preferences, and metadata when your data is semi-structured and changes over time.

When to use JSONB

Use it when:

• Different rows can have different keys
• You need flexible schema evolution
• You want to store nested objects/arrays
• You still want querying and indexing support

Avoid it when:

• The data is highly relational and frequently joined
• You need strict schema constraints on every field
• The fields are core to your business logic and heavily updated individually at high scale

---

Basic table design

Example: storing settings and metadata for an app entity.

CREATE TABLE apps (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    settings JSONB NOT NULL DEFAULT '{}'::jsonb,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Insert data

INSERT INTO apps (name, settings, metadata)
VALUES (
  'billing-service',
  '{"theme":"dark","notifications":{"email":true,"sms":false}}',
  '{"owner":"team-payments","version":"1.2.3","tags":["internal","critical"]}'
);

---

Querying JSONB

Access a top-level key

SELECT settings->>'theme' AS theme
FROM apps;

Access nested values

SELECT settings->'notifications'->>'email' AS email_notifications
FROM apps;

Filter by a JSONB value

SELECT *
FROM apps
WHERE settings->>'theme' = 'dark';

Check if a key exists

SELECT *
FROM apps
WHERE metadata ? 'owner';

Check if a JSON object contains another object

SELECT *
FROM apps
WHERE metadata @> '{"version":"1.2.3"}';

---

Updating JSONB

Update one key

Use jsonb_set:

UPDATE apps
SET settings = jsonb_set(settings, '{notifications,email}', 'false'::jsonb, true)
WHERE id = 1;

Add a new key

UPDATE apps
SET settings = jsonb_set(settings, '{language}', '"en"'::jsonb, true)
WHERE id = 1;

Merge objects

UPDATE apps
SET metadata = metadata || '{"environment":"prod"}'::jsonb
WHERE id = 1;

---

Indexing JSONB

JSONB can be slow without indexes if you query it often.

GIN index for containment queries

Good for @> and existence queries:

CREATE INDEX idx_apps_metadata_gin
ON apps
USING GIN (metadata);

Expression index for a specific field

If you frequently filter on one key:

CREATE INDEX idx_apps_theme
ON apps ((settings->>'theme'));

Partial index

Useful if only some rows use a field:

CREATE INDEX idx_apps_owner
ON apps ((metadata->>'owner'))
WHERE metadata ? 'owner';

---

Good practices

1. Keep frequently queried fields separate if needed

If you often query name, status, or tenant_id, store them in normal columns, not only in JSONB.

2. Use JSONB for flexible, optional, or evolving fields

Great for:

• feature flags
• UI preferences
• integration metadata
• external API payloads
• audit/context data

3. Validate shape in the application or with constraints

You can add simple checks:

ALTER TABLE apps
ADD CONSTRAINT settings_must_be_object
CHECK (jsonb_typeof(settings) = 'object');

For stricter validation, use application validation or database triggers.

4. Default to empty objects

This avoids null handling:

settings JSONB NOT NULL DEFAULT '{}'::jsonb

5. Avoid storing huge, frequently rewritten blobs

Updating JSONB rewrites the whole value, so very large documents that change often can be inefficient.

---

Example: storing app settings and metadata together

CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY,
    preferences JSONB NOT NULL DEFAULT '{}'::jsonb,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb
);

Example values:

preferences:
{
  "theme": "dark",
  "language": "en",
  "notifications": {
    "email": true,
    "push": false
  }
}

metadata:
{
  "signup_source": "google",
  "last_login_ip": "203.0.113.42",
  "tags": ["beta", "premium"]
}

---

Rule of thumb

• Relational columns for stable, important, heavily used fields
• JSONB for flexible, optional, or evolving settings/metadata

If you want, I can also show:

1. a real-world app settings schema,
2. SQLAlchemy / Django / Node.js examples, or
3. how to enforce JSON schema-like validation in PostgreSQL.