PostgreSQL JSON-Operationen

PostgreSQL bietet erstklassige JSON-Unterstützung mit JSONB.

Operatoren¶

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

Indizes¶

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

JSONB = Flexibilität + Leistung¶

GIN-Index für schnelle Abfragen.