TopolearnFlexible attributes and external payloads fit poorly in fixed columns; jsonb handles them inside SQL.
PostgreSQL has two JSON types. json stores the text as given; jsonb stores a parsed binary form, drops duplicate keys and whitespace, and supports indexing and rich operators. Use jsonb unless you must preserve the exact input text.
Operators to know:
attrs -> 'dpi'returns ajsonbvalue;attrs ->> 'dpi'returnstext. Cast for comparisons:(attrs ->> 'dpi')::int > 1000.attrs #>> '{dims,width}'follows a path.attrs @> '{"wireless": true}'tests containment: does the left document contain this structure?attrs ? 'dpi'tests whether a top-level key exists.attrs || '{"color": "black"}'merges;attrs - 'dpi'removes a key;jsonb_setreplaces a nested value.
Functions such as jsonb_array_elements and jsonb_each expand JSON into rows, and jsonb_build_object and jsonb_agg build JSON from rows, handy for API responses. PostgreSQL also implements the SQL/JSON standard: jsonb_path_query with JSONPath, and in version 17, JSON_TABLE to turn documents into relational rows.
Indexing: a GIN index on the column (USING gin (attrs)) accelerates @>, ? and related operators; the jsonb_path_ops operator class is smaller and faster but supports only containment and path operators. For one frequently filtered key, a B-tree expression index on (attrs ->> 'brand') is better.
Use jsonb for genuinely variable data: per-category product attributes, webhook payloads, user settings. Do not use it to avoid schema design. Fields every row has, fields you join or constrain on, and anything referenced by a foreign key belong in real columns, where types, constraints and statistics work properly.
MySQL has a JSON type with similar -> / ->> operators; SQL Server stores JSON in text columns with JSON_VALUE functions.
Example
ALTER TABLE products ADD COLUMN attrs jsonb NOT NULL DEFAULT '{}';
UPDATE products SET attrs = '{"wireless": true, "layout": "US"}' WHERE id = 1;
UPDATE products SET attrs = '{"wireless": true, "dpi": 1600}' WHERE id = 2;
UPDATE products SET attrs = '{"size_in": 27}' WHERE id = 3;
SELECT name, attrs ->> 'dpi' AS dpi FROM products WHERE attrs ? 'dpi';
-- Mouse | 1600
SELECT name FROM products WHERE attrs @> '{"wireless": true}';
-- Keyboard
-- Mouse
CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
UPDATE products SET attrs = attrs || '{"color": "black"}' WHERE id = 2;
-- Build JSON for an API response
SELECT jsonb_build_object('customer', c.name,
'orders', jsonb_agg(o.id ORDER BY o.id)) AS doc
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.id = 1 GROUP BY c.name;
-- {"orders": [101, 102], "customer": "Ana"}Remember
- Prefer jsonb over json
- -> returns jsonb, ->> returns text; @> tests containment
- GIN indexes accelerate containment; expression indexes serve single keys
- Keep core, constrained, joined fields in real columns
Watch out for
- Comparing attrs -> 'dpi' with a number or string literal instead of casting attrs ->> 'dpi'.