Working with JSONB in PostgreSQL

JSONB vs JSON

PostgreSQL offers two JSON types. JSON stores text verbatim; JSONB parses it into a binary format that supports indexing, containment checks, and fast access. Always prefer JSONB unless you need to preserve formatting or key order.

-- JSON stores raw text (preserves whitespace, duplicate keys)
SELECT '{"a":1, "a":2}'::json;   -- keeps both keys

-- JSONB deduplicates and reorders keys
SELECT '{"b":2, "a":1}'::jsonb;  -- {"a": 1, "b": 2}

Essential JSONB Operators

PostgreSQL provides a rich set of operators for navigating and querying JSONB data:

-- -> returns JSONB, ->> returns TEXT
SELECT payload->'user'->>'name' AS user_name FROM events;

-- #> navigates a path, #>> returns text
SELECT payload #>> '{user,address,city}' AS city FROM events;

-- @> containment: does the JSONB contain this sub-object?
SELECT * FROM events
WHERE payload @> '{"type": "purchase"}';

-- ? key existence: does the top-level key exist?
SELECT * FROM events
WHERE payload ? 'coupon_code';

-- ?| any of these keys exist
SELECT * FROM events
WHERE payload ?| array['coupon_code', 'referral_code'];

Indexing JSONB for Performance

Without indexes, JSONB queries do sequential scans. GIN indexes make containment (@>) and existence (?) operators fast. For specific key lookups, use expression indexes.

-- GIN index for general containment and existence queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Expression index for a specific frequently-queried path
CREATE INDEX idx_events_type ON events ((payload->>'type'));

-- GIN with jsonb_path_ops: smaller index, only supports @>
CREATE INDEX idx_events_payload_path ON events
USING GIN (payload jsonb_path_ops);

Building & Aggregating JSONB

PostgreSQL provides functions to construct JSONB dynamically and aggregate rows into JSON structures.

-- Build JSONB objects from values
SELECT jsonb_build_object(
  'id', u.id,
  'name', u.name,
  'email', u.email
) AS user_json
FROM users u WHERE u.id = 1;

-- Aggregate rows into a JSON array
SELECT jsonb_agg(
  jsonb_build_object('id', p.id, 'title', p.title)
) AS posts
FROM posts p
WHERE p.user_id = 1;

-- Merge JSONB objects with || operator
UPDATE events
SET payload = payload || '{"processed": true}'::jsonb
WHERE id = 42;

Querying Nested Structures

-- Expand JSONB array elements into rows
SELECT e.id, item->>'product' AS product, (item->>'qty')::int AS qty
FROM events e,
     jsonb_array_elements(e.payload->'items') AS item
WHERE e.payload->>'type' = 'order';

-- jsonb_each: iterate over key-value pairs
SELECT key, value
FROM events, jsonb_each(payload->'metadata')
WHERE id = 100;