JSON and JSONB

PostgreSQL provides json and jsonb for JSON values. json stores the input text representation, while jsonb stores a decomposed binary representation that supports efficient operations and indexing. For most application workloads involving querying or updating JSON fields, jsonb is the usual choice.

JSONB is not a substitute for relational modeling in every case. Frequently queried, constrained, or joined attributes may belong in ordinary columns. Use JSONB for flexible or evolving attributes where its query model is useful, and consider generated columns or expression indexes for common access paths.

Query a JSONB document

CREATE TABLE events (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload JSONB NOT NULL
);

SELECT id, payload ->> 'type' AS event_type
FROM events
WHERE payload @> '{"type":"signup"}'::jsonb;

💡 ->> extracts a JSON field as text, while @> tests containment. A suitable GIN index may accelerate containment queries depending on the operator class and workload.

Key Takeaways

JSONB supports querying and indexing structured JSON values.
Use relational columns for stable, frequently queried domain attributes when appropriate.
Choose operators and indexes based on actual JSON query patterns.

Knowledge Check

Which type is commonly preferred for queryable JSON data in PostgreSQL?

JSON and JSONB

STAY UPDATED WITH BYTES!