Using jsonb in PostgreSQL: Benefits, Operations, and Practical Examples

What is jsonb

Starting with PostgreSQL 9.4, the binary JSON type jsonb provides significant speed improvements by storing JSON data in a decomposed binary format rather than as plain text.

The main differences are that json keeps the exact input text, while jsonb stores a binary representation, enabling faster processing, indexing support, and simpler schema design, though inserts can be slightly slower and disk usage may increase.

Use Case: Book Entries

To illustrate basic operations, a toy table books is created with a jsonb column:

CREATE TABLE books ( book_id serial NOT NULL, data jsonb );

Sample rows are inserted as full JSON strings:

INSERT INTO books VALUES (1, '{"title":"Sleeping Beauties","genres":["Fiction","Thriller","Horror"],"published":false}');
INSERT INTO books VALUES (2, '{"title":"Influence","genres":["Marketing & Sales","Self-Help","Psychology"],"published":true}');
INSERT INTO books VALUES (3, '{"title":"The Dictator''s Handbook","genres":["Law","Politics"],"authors":["Bruce Bueno de Mesquita","Alastair Smith"],"published":true}');
INSERT INTO books VALUES (4, '{"title":"Deep Work","genres":["Productivity","Reference"],"published":true}');
INSERT INTO books VALUES (5, '{"title":"Siddhartha","genres":["Fiction","Spirituality"],"published":true}');

Querying a specific key returns the titles: SELECT data->'title' AS title FROM books; Filtering with a WHERE clause works on JSON keys as well:

SELECT * FROM books WHERE data->'published' = 'false';

Array expansion turns a JSON array into a set of rows:

SELECT jsonb_array_elements_text(data->'genres') AS genre FROM books WHERE book_id = 1;

Containment checks use the @> operator to test whether one JSON value contains another:

SELECT '["Fiction", "Thriller", "Horror"]'::jsonb @> '["Fiction", "Horror"]'::jsonb;  -- returns t

The existence operator ? determines whether a key or array element is present:

SELECT COUNT(*) FROM books WHERE data ? 'authors';

Creating a GIN index on a JSON key dramatically speeds up look‑ups:

CREATE INDEX idx_published ON books (data->'published');

Important considerations when switching to jsonb include stricter Unicode handling, loss of whitespace and key order, and the fact that duplicate object keys are collapsed to the last value.

Conclusion

PostgreSQL recommends using jsonb for most applications because of its performance gains and powerful indexing capabilities, despite minor trade‑offs such as slower inserts and incompatibility with legacy code that relies on key ordering.