PostgreSQL has first-class JSON support with JSONB.
Operators¶
SELECT data->>'name' FROM events;
SELECT * FROM events WHERE data->>'type'='click';
SELECT * FROM events WHERE data @> '{"type":"click"}';
SELECT * FROM events WHERE data ? 'email';
Indexes¶
CREATE INDEX idx_data ON events USING GIN (data);
CREATE INDEX idx_type ON events ((data->>'type'));
Manipulation¶
UPDATE users SET data=data||'{"verified":true}';
UPDATE users SET data=data-'temp';
UPDATE users SET data=jsonb_set(data,'{address,city}','"Prague"');
When JSONB vs Relational Schema¶
Use JSONB for semi-structured data with a variable schema — user preferences, metadata, configurations, or data from external APIs. For firmly structured data with clearly defined relationships, stick with classic relational tables — they will be faster and type-safe.
A GIN index on a JSONB column supports the @>, ?, ?|, and ?& operators, covering most query patterns. For queries on a specific key (e.g., data->>'type'), create a B-tree index on the expression. PostgreSQL 14+ added jsonb_path_query for SQL/JSON path queries, which are more standardized and often more readable than traditional operators.
JSONB = Flexibility + Performance¶
GIN index for fast queries.
postgresqljsonjsonb