Postgres JSONB — The Best of SQL and NoSQL
Learn when to use JSONB columns, how to query and index nested JSON data, and when structured columns are still the better choice.
Not everything fits into neatly defined columns. User preferences are different for every user. API responses have nested objects. Product attributes vary wildly between categories — a laptop has RAM and screen size, but a book has page count and ISBN.
You could create a column for every possible attribute, leaving most of them NULL. You could create an Entity-Attribute-Value (EAV) table, which works but is painful to query. Or you could use PostgreSQL's JSONB type and get the flexibility of a document database with the power of SQL.
JSON vs JSONB
PostgreSQL has two JSON types:
- JSON — Stores the raw JSON text exactly as you inserted it. Preserves formatting, duplicate keys, and key order. Parsing happens on every read.
- JSONB — Stores JSON in a decomposed binary format.
This lesson is part of the Guild Member curriculum. Plans start at $29/mo.