JSON Support

PostgreSQL provides first-class support for JSON data through two types: json and jsonb. This makes it possible to store semi-structured or document-style data alongside relational data in the same database, without sacrificing queryability.

json vs jsonb

Feature	json	jsonb
Storage	Stores the raw text exactly	Stores a parsed binary format
Duplicate keys	Preserved	Last value wins
Key order	Preserved	Not preserved
Indexing	Not indexable	Fully indexable with GIN
Query speed	Slower (reparsed each time)	Faster

Always prefer jsonb unless you need to preserve key order or duplicate keys exactly.

Storing and Querying JSON

CREATE TABLE events (
  id         serial  PRIMARY KEY,
  name       text    NOT NULL,
  payload    jsonb
);

INSERT INTO events (name, payload)
VALUES ('user_signup', '{"user_id": 42, "plan": "pro", "country": "US"}');

-- Extract a field as jsonb
SELECT payload -> 'plan' FROM events;

-- Extract a field as text
SELECT payload ->> 'plan' FROM events;

-- Filter on a JSON field
SELECT * FROM events WHERE payload ->> 'country' = 'US';

-- Nested access
SELECT payload -> 'address' ->> 'city' FROM events;

Containment and Existence Operators

-- Does the jsonb value contain this object?
SELECT * FROM events WHERE payload @> '{"plan": "pro"}';

-- Does the key exist?
SELECT * FROM events WHERE payload ? 'country';

Indexing JSONB

A GIN index on a jsonb column enables fast containment (@>) and existence (?) queries:

CREATE INDEX idx_events_payload ON events USING GIN (payload);

Updating JSON Fields

-- Replace a top-level key
UPDATE events
SET payload = payload || '{"plan": "enterprise"}'
WHERE id = 1;

-- Remove a key
UPDATE events
SET payload = payload - 'country'
WHERE id = 1;

JSON Aggregation

PostgreSQL can build JSON objects and arrays from query results:

SELECT json_agg(name) FROM events;

SELECT json_build_object('id', id, 'name', name) FROM events;

JSON support in PostgreSQL gives you the flexibility of a document store with the power of a relational database — no need to maintain a separate MongoDB instance for semi-structured data.