JSONB is PostgreSQL's binary JSON type — it stores JSON data in an efficient, queryable, and indexable format. It lets you store flexible, semi-structured data in a relational database, combining relational integrity with NoSQL-like flexibility. It's one of Postgres's most powerful features.
JSONB vs JSON
JSON → stores the exact text (preserves whitespace/key order/duplicates), parsed each query
JSONB → stores a DECOMPOSED BINARY format → slightly slower to write, but MUCH faster
to query, and supports INDEXING and rich operators
→ Use JSONB in almost all cases (JSON only if you need exact text preservation).
Storing and querying JSONB
CREATE TABLE products (id SERIAL, attributes JSONB);
INSERT INTO products (attributes) VALUES
('{"color": "red", "size": "L", "tags": ["new", "sale"]}');
-- extract values
SELECT attributes->'color' FROM products; -- -> returns JSONB: "red"
SELECT attributes->>'color' FROM products; -- ->> returns TEXT: red
SELECT attributes#>>'{tags,0}' FROM products; -- nested path: first tag
-- query by content
SELECT * FROM products WHERE attributes->>'color' = 'red';
SELECT * FROM products WHERE attributes @> '{"color": "red"}'; -- containment
SELECT * FROM products WHERE attributes ? 'color'; -- key exists
Operators: -> (get JSONB field), ->> (get as text), @> (containment), ? (key exists), #>> (nested path). These make JSONB richly queryable — unlike storing JSON as plain text.
Indexing JSONB (key to performance)
-- a GIN index makes containment/key queries fast
CREATE INDEX idx_attrs ON products USING GIN (attributes);
-- now WHERE attributes @> '{"color": "red"}' uses the index
-- or index a specific extracted field
CREATE INDEX idx_color ON products ((attributes->>'color'));
When to use JSONB (and when not)
✓ USE for: flexible/variable attributes, settings, API payloads, sparse fields,
semi-structured data that varies per row, or rapidly-evolving schemas
✗ AVOID for: data that's well-structured and queried relationally → use normal columns
(better constraints, types, and typically better performance for structured queries)
→ Mix both: structured data in columns, variable/flexible data in a JSONB column.
Why it matters
JSONB is one of PostgreSQL's most important and distinctive features, and understanding it is highly valuable because it lets you store and efficiently query semi-structured data within a relational database — combining the integrity, transactions, and relational power of Postgres with the flexibility of a document/NoSQL store, which is a major capability for modern applications.
The key advantage over storing JSON as plain text (or JSON type) is that JSONB is queryable and indexable: rich operators (->, ->>, @>, ?) let you extract and filter on JSON content, and GIN indexes (or indexes on extracted fields) make these queries fast — so you get document-store-like flexibility without sacrificing query performance.
This makes JSONB ideal for flexible or variable attributes, settings, API payloads, sparse data, and evolving schemas — common modern needs that would otherwise require a NoSQL database or awkward schema designs.
Understanding the JSONB vs JSON distinction (JSONB for almost all cases), the query operators, indexing (essential for performance), and crucially when to use JSONB versus normal columns (JSONB for flexible/variable/semi-structured data, normal columns for well-structured relationally-queried data — often mixing both) reflects mature design judgment.
Since the ability to handle flexible, semi-structured data is increasingly important, and since JSONB provides this within a robust relational database (often eliminating the need for a separate NoSQL store), understanding JSONB — its querying, indexing, and appropriate use — is valuable, frequently-relevant knowledge that's a key reason PostgreSQL is so popular for modern applications and a hallmark of leveraging Postgres's full power.