JSONB Storage and Operators

PostgreSQL's JSONB type stores JSON data in a decomposed binary format that allows fast key lookups, containment tests, and indexing. Unlike the JSON type (which preserves whitespace and key order), JSONB normalises the data on insert: it removes duplicate keys (keeping the last value) and does not preserve key order. The main query operators are: -> (get JSON value by key or array index), ->> (get text value), #> (get value at JSON path as JSONB), #>> (get value at JSON path as text), @> (left contains right), <@ (left is contained by right), ? (key exists), ?| (any of the keys exist), and ?& (all of the keys exist). JSONB supports GIN indexes for fast containment and existence queries.

Example

-- Create a table with JSONB
CREATE TABLE events (
  id         BIGSERIAL PRIMARY KEY,
  event_type TEXT NOT NULL,
  payload    JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert JSONB data
INSERT INTO events (event_type, payload) VALUES
  ('purchase', '{"userId":1,"items":[{"sku":"KB1","qty":2},{"sku":"M2","qty":1}],"total":159.97}'),
  ('signup',   '{"userId":2,"email":"bob@example.com","referral":"google"}');

-- -> and ->> operators
SELECT
  payload -> 'userId'            AS user_id_json,   -- 1 (JSONB number)
  payload ->> 'userId'           AS user_id_text,   -- '1' (text)
  payload -> 'items' -> 0 ->> 'sku' AS first_sku    -- 'KB1'
FROM events WHERE event_type = 'purchase';

-- @> containment — find purchases by userId 1
SELECT * FROM events
WHERE event_type = 'purchase'
  AND payload @> '{"userId": 1}';

-- ? key existence
SELECT * FROM events WHERE payload ? 'referral';

-- GIN index for fast JSONB queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- jsonb_each — expand JSONB object to key-value rows
SELECT key, value FROM jsonb_each('{"a":1,"b":2,"c":3}');

-- jsonb_array_elements — expand JSONB array to rows
SELECT elem FROM jsonb_array_elements('[1,2,3,4,5]'::jsonb) AS t(elem);

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned