PostgreSQL JSON Operations

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"');

JSONB = Flexibility + Performance¶

GIN index for fast queries.