PostgreSQL JSON vs JSONB: Querying and Indexing
On this page
When to use jsonb
A shop sells mugs, shirts and laptops. Mugs have a capacity, shirts have sizes, laptops have RAM and a CPU. A column per attribute gives you a table that's mostly NULLs. A separate table per category means new tables every time the catalog changes.
A jsonb column handles this neatly: shared, important fields as normal columns, and the variable long tail in one document per row.
CREATE TABLE products (
sku text PRIMARY KEY,
name text NOT NULL,
list_price numeric(10,2) NOT NULL,
attributes jsonb NOT NULL DEFAULT '{}'
);
INSERT INTO products VALUES
('MUG-1', 'Mug', 12, '{"color": "red", "capacity_ml": 350, "tags": ["sale", "new"]}'),
('TEE-1', 'Shirt', 25, '{"color": "blue", "sizes": ["S", "M", "L"]}'),
('LAP-1', 'Laptop', 999, '{"specs": {"ram_gb": 16, "cpu": "M4"}, "tags": ["new"]}');json or jsonb?
PostgreSQL has two JSON types, and you'll almost always want the second:
| `json` | `jsonb` | |
|---|---|---|
| Stores | The exact text you sent | A parsed binary form |
| Whitespace and key order | Kept | Not kept |
| Duplicate keys | Kept | Last value wins |
| Query speed | Re-parses on every read | Fast |
| GIN indexes | No | Yes |
SELECT '{"a": 1, "a": 2}'::json; -- {"a": 1, "a": 2}
SELECT '{"a": 1, "a": 2}'::jsonb; -- {"a": 2}Use json only when you must keep a payload byte for byte, for example to verify a webhook signature later.
Reading JSON values with -> and ->>
Two operators do most of the work. -> returns JSON, so you can keep drilling down. ->> returns text, so you can compare or display it.
SELECT attributes -> 'color' FROM products WHERE sku = 'MUG-1'; -- "red" (jsonb)
SELECT attributes ->> 'color' FROM products WHERE sku = 'MUG-1'; -- red (text)
SELECT attributes -> 'specs' ->> 'ram_gb' FROM products WHERE sku = 'LAP-1'; -- 16
SELECT attributes #>> '{specs,cpu}' FROM products WHERE sku = 'LAP-1'; -- M4 (path form)
SELECT attributes -> 'tags' ->> 0 FROM products WHERE sku = 'MUG-1'; -- saleTwo rules prevent most errors:
- Use
->for every step except the last. Once you've used->>, you have text, and text has no JSON operators. ->>always returns text, so cast before you compare numbers:(attributes -> 'specs' ->> 'ram_gb')::int >= 16. If one row holds"lots"there, the cast fails, so be sure the field's type is consistent.
Filtering jsonb with @> containment
To ask "which documents include these values?", use @>, read as "contains":
SELECT sku FROM products WHERE attributes @> '{"color": "red"}'; -- MUG-1
SELECT sku FROM products WHERE attributes @> '{"tags": ["new"]}'; -- MUG-1, LAP-1
SELECT sku FROM products WHERE attributes ? 'sizes'; -- TEE-1: has the key@> matches nested structure too. The second query finds documents whose tags array contains "new". You can check several fields in one predicate, and it's the operator GIN indexes speed up most. The ? family tests for keys: ? for one key, ?| for any of a list, ?& for all of them.
For more complex conditions, there's the SQL/JSON path language:
SELECT sku FROM products
WHERE jsonb_path_exists(attributes, '$.specs ? (@.ram_gb >= 16)'); -- LAP-1Updating jsonb with jsonb_set
You don't edit a jsonb value in place. You compute a new value and store it. These cover most needs:
UPDATE products SET attributes = jsonb_set(attributes, '{capacity_ml}', '400')
WHERE sku = 'MUG-1'; -- set one path
UPDATE products SET attributes = attributes || '{"featured": true}'
WHERE sku = 'MUG-1'; -- merge in keys
UPDATE products SET attributes = attributes - 'featured'
WHERE sku = 'MUG-1'; -- remove a keyjsonb_set creates the key if it's missing. Pass false as a fourth argument if it should only update keys that already exist.
Going the other way, you can build JSON from ordinary columns, which is handy for API responses:
SELECT jsonb_build_object('sku', sku, 'name', name, 'price', list_price) FROM products;
SELECT jsonb_agg(sku ORDER BY sku) FROM products; -- ["LAP-1", "MUG-1", "TEE-1"]Indexing jsonb with GIN
Without an index, every jsonb filter reads every row. A GIN index (Generalized Inverted Index) indexes the keys and values inside each document, so @> and the ? operators can use it:
CREATE INDEX products_attributes_idx ON products USING gin (attributes);If you only filter with @>, the jsonb_path_ops variant is smaller and usually faster. It doesn't support the ? operators:
CREATE INDEX products_attributes_path_idx ON products USING gin (attributes jsonb_path_ops);If you always filter or sort on one known field, a plain B-tree index on that expression is simpler and also supports ranges and ORDER BY, which GIN can't:
CREATE INDEX products_ram_idx ON products (((attributes -> 'specs' ->> 'ram_gb')::int));The query must use the exact same expression for the index to apply. Day 23 covers GIN and expression indexes in more depth.
When the data belongs in columns
jsonb is a targeted tool, not a schema strategy. The data probably wants real columns or tables if:
- You filter, join or aggregate on a field across many rows, like "orders containing a product from category X".
- It needs a foreign key, a NOT NULL or a CHECK.
- You update single fields often. Each update rewrites the whole document, and large documents are compressed and stored out of line, so this gets expensive.
A good rule: if a field becomes important, promote it to a column. You can still enforce some structure on what's left in the document with CHECK constraints, such as CHECK (attributes ? 'color').
Check yourself
The first ->> returns text, and text has no JSON operators, so the second ->> fails. Use -> for every step except the last, which returns jsonb you can keep navigating: attributes -> 'specs' ->> 'cpu'. The path operator attributes #>> '{specs,cpu}' does the same in one step.
A GIN index on the column speeds up containment and key operators, not ->> comparisons. Rewrite the filter as attributes @> '{"color": "red"}', or add an expression index on (attributes ->> 'color').
Line items are records you aggregate across orders, so they belong in an order_items table with foreign keys (Day 13). Keep jsonb for genuinely variable data that's read as a whole.
jsonb stores a parsed binary form, so it supports indexing, containment queries like @> and fast key lookup, and it removes duplicate keys. json keeps the exact text, including whitespace and key order, which is only useful if you must return the original document byte for byte.
Use jsonb_set, for example UPDATE products SET attributes = jsonb_set(attributes, '{color}', '"blue"') WHERE id = 1. Note the new value must itself be JSON, so a string needs its own quotes. PostgreSQL still writes a new row version, but the change is atomic in one statement.