JSONB

JSONB is PostgreSQL's binary JSON data type. It allows you to store, index, and query JSON documents directly in the database. This gives you the flexibility of a document database (like MongoDB) while keeping all the power of SQL and relational features.

Storing JSONB

Creating a table with a JSONB column:

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  event_type VARCHAR(50),
  data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert JSON data
INSERT INTO events (event_type, data)
VALUES ('user_action', '{
  "user_id": 123,
  "action": "click",
  "page": "/dashboard",
  "metadata": {
    "browser": "Chrome",
    "os": "Windows"
  }
}');

The data is stored in binary format, which is more efficient than plain text JSON for querying and indexing.

Querying JSONB

PostgreSQL provides operators for navigating and filtering JSON data:

-- Get a specific key
SELECT data->>'user_id' FROM events;

-- Get nested values
SELECT data->'metadata'->>'browser' FROM events;

-- Check if a key exists
SELECT * FROM events WHERE data ? 'user_id';

-- Check for a specific value
SELECT * FROM events WHERE data @> '{"action": "click"}';

-- Filter on nested values
SELECT * FROM events
WHERE data->'metadata'->>'browser' = 'Chrome';

The ->> operator returns text, while -> returns JSONB. Use @> for containment checks which can use GIN indexes.

Modifying JSONB

You can update specific parts of a JSONB document:

-- Add or update a key
UPDATE events
SET data = jsonb_set(data, '{page}', '"/settings"')
WHERE id = 1;

-- Remove a key
UPDATE events
SET data = data - 'metadata'
WHERE id = 1;

-- Merge two JSONB objects
UPDATE events
SET data = data || '{"source": "web"}'
WHERE id = 1;

JSONB Functions

PostgreSQL provides functions for working with JSON:

-- Convert text to JSONB
SELECT '{"name": "Alice"}'::jsonb;

-- Get all keys
SELECT jsonb_object_keys(data) FROM events;

-- Get the type of a value
SELECT jsonb_typeof(data->'user_id') FROM events;

-- Pretty-print JSON
SELECT jsonb_pretty(data) FROM events WHERE id = 1;

Indexing JSONB

For fast queries on JSONB, create a GIN index:

CREATE INDEX idx_events_data ON events USING gin (data);

-- Now containment queries are fast
SELECT * FROM events WHERE data @> '{"action": "click"}';

-- For specific key queries, create a btree index on the expression
CREATE INDEX idx_events_user_id ON events ((data->>'user_id'));

SELECT * FROM events WHERE data->>'user_id' = '123';

When to Use JSONB

JSONB is great when you:

• Have variable or evolving data structures
• Store configuration, metadata, or semi-structured data
• Need to query inside JSON documents efficiently
• Want document-database flexibility with SQL power

Avoid JSONB when you have a fixed, well-defined schema. Relational columns are faster and more efficient for structured data.