How to use JSONB in PostgreSQL

Short answer

JSONB is a binary JSON storage format in PostgreSQL that supports indexing, advanced querying, and efficient updates compared to plain JSON.

Steps

1. Create a JSONB column: CREATE TABLE logs (id SERIAL PRIMARY KEY, data JSONB);
2. Insert JSONB: INSERT INTO logs (data) VALUES ('{\"level\": \"error\"}');
3. Query with operators: SELECT * FROM logs WHERE data @> '{\"level\": \"error\"}';
4. Extract values: SELECT data->>'level' FROM logs;
5. Create a GIN index: CREATE INDEX idx_logs_data ON logs USING GIN (data);

Tips

• Use jsonb_path_exists and jsonb_path_query for advanced path-based queries.
• Update specific keys with jsonb_set without rewriting the entire document.

Common issues

• Large JSONB values increase table and index bloat.
• JSONB does not preserve key order or duplicate keys, unlike JSON.r