JSON & Arrays
JSONB, arrays, and when to reach for them
Beyond the fixed columns in Data Types, Postgres has first-class support for semi-structured data — useful, but easy to overuse.
json vs jsonb
payload json -- stores exact text, re-parsed every time it's read
payload jsonb -- stores a decomposed binary format, indexableAlmost always reach for jsonb, not json. json stores the
input text verbatim (preserving key order and whitespace) and has
to re-parse it on every access. jsonb stores a parsed binary
representation — slightly slower to write, meaningfully faster to
query, and the only one of the two that indexes support.
Querying jsonb
SELECT payload -> 'address' FROM events; -- returns jsonb
SELECT payload ->> 'user_id' FROM events; -- returns text
SELECT * FROM events WHERE payload @> '{"type": "signup"}'; -- containment
SELECT jsonb_path_query(payload, '$.items[*].sku') FROM events;-> extracts a field as jsonb (keep chaining), ->> extracts as
text (use it at the end of a chain when you need a plain value to
compare or display). @> checks whether the left jsonb value
contains the right one — this is what a GIN index accelerates; see
Indexing.
Arrays
CREATE TABLE posts (
id serial PRIMARY KEY,
tags text[]
);
INSERT INTO posts (tags) VALUES (ARRAY['sql', 'postgres', 'internals']);
SELECT * FROM posts WHERE 'postgres' = ANY(tags);
SELECT unnest(tags) FROM posts; -- one row per array elementArrays are a native type for any base type (integer[], text[],
even jsonb[]), not an add-on — useful for small, denormalized lists
that don't warrant a separate join table.
jsonb is a relief valve, not a schema strategy. It's the right tool for genuinely variable or sparse attributes (event payloads, third-party API responses), but a table where every column is really jsonb loses constraints, foreign keys, and most of the planner's ability to reason about your data. If a field is always present and has a known type, it belongs in a real column.
That closes out the Foundations and Data Types groups. Next up: Heap Storage — how a row you just inserted actually ends up on disk.