How to use JSON data types in PostgreSQL

JSON vs JSONB

PostgreSQL offers two JSON types: json stores raw text, while jsonb stores parsed binary data. jsonb is generally preferred because it supports indexing, is faster to process, and eliminates duplicate keys.

Creating and Inserting JSONB

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

INSERT INTO events (payload) VALUES
('{"user": "alice", "action": "login"}');

Querying JSONB

Use the -> and ->> operators to extract values:

SELECT payload->>'user' AS user_name
FROM events
WHERE payload->>'action' = 'login';

Indexing

Create a GIN index for fast containment and key lookups:

CREATE INDEX idx_events_payload ON events USING GIN (payload);

PostgreSQL JSONB bridges SQL and document store flexibility. For comparisons with document databases, see what is the difference between SQL and NoSQL databases. If you are designing schemas that mix relational and JSON data, how to design a database schema from scratch provides helpful guidelines.