JSON with jsonb

Storing Documents in PostgreSQL

CREATE TABLE events (
  id      SERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

Querying JSON

SELECT payload ->> 'user' AS username,
       payload -> 'metadata' AS meta
FROM events;

SELECT * FROM events WHERE payload ->> 'action' = 'login';

• -> returns a JSON value.
• ->> returns text.
• #> and #>> navigate a path such as payload #>> '{'metadata', 'ip'}'.

Indexing JSONB

CREATE INDEX idx_events_payload ON events USING GIN (payload);

A GIN index makes queries against the JSON document fast.

Key Points

• jsonb stores JSON in binary form with indexing support.
• Use -> for JSON, ->> for text extraction.
• GIN indexes accelerate jsonb lookups.
• Combine relational tables and JSON documents freely.