JSONB & Semi-Structured Data

JSONB stores JSON data in a decomposed binary format that PostgreSQL can query, index, and filter directly — a genuine document store living inside a regular relational column.

JSON vs JSONB

PostgreSQL actually has two JSON types: plain JSON stores an exact text copy of what you inserted, preserving whitespace and key order; JSONB parses it into a binary structure, which is slightly slower to insert but much faster to query — and it's what almost everyone should reach for by default.

Storing and inserting JSONB

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

INSERT INTO events (payload) VALUES
  ('{"type": "signup", "user": {"name": "Priya", "plan": "pro"}}'),
  ('{"type": "signup", "user": {"name": "Sam", "plan": "free"}}');
SELECT * FROM events;

 id |                              payload                               
----+---------------------------------------------------------------------
  1 | {"type": "signup", "user": {"name": "Priya", "plan": "pro"}}
  2 | {"type": "signup", "user": {"name": "Sam", "plan": "free"}}
(2 rows)

The -> and ->> operators

SELECT payload->'user'->>'name' AS user_name, payload->>'type' AS event_type
FROM events;

 user_name | event_type 
-----------+------------
 Priya     | signup
 Sam       | signup
(2 rows)

-> reaches into a JSON object or array and returns another JSONB value, so it can be chained (payload->'user' gives you the nested user object). ->> does the same thing but returns plain text instead — you generally want ->> at the very end of a chain, once you've drilled down to the actual value you want to compare or display.

Filtering with @>

SELECT id FROM events WHERE payload @> '{"user": {"plan": "pro"}}';

 id 
----
  1
(1 row)

@> is the "contains" operator — it checks whether the left JSONB value contains the structure on the right, at any depth. It's the natural way to filter rows by a nested field without writing out a full chain of -> operators.