JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations
JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations
PostgreSQL's JSON support has matured into a powerful feature set. The `jsonb` type combined with GIN indexes makes PostgreSQL a competitive document database while retaining all relational capabilities. This article covers the types, operators, indexes, and when JSON in PostgreSQL is the right choice.
JSON vs JSONB
PostgreSQL offers two JSON data types:
| Aspect | `json` | `jsonb` | |--------|--------|---------| | Storage | Exact copy of input text | Decomposed binary format | | Duplicate keys | Preserved | Last key wins | | Whitespace | Preserved | Removed | | Key ordering | Preserved | Not preserved | | Indexing | Function-based only | GIN indexes supported | | Parsing overhead | On each access | On insert only |
**Always use `jsonb`** for new projects unless you have a specific need for `json` (such as preserving duplicate keys exactly as input).
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
attributes JSONB NOT NULL DEFAULT '{}'
);
INSERT INTO products (name, attributes) VALUES
('Widget', '{"color": "red", "weight": 1.5, "tags": ["sale", "new"]}'),
('Gadget', '{"color": "blue", "dimensions": {"width": 10, "height": 5}}');
Query Operators
PostgreSQL provides a rich set of JSONB operators:
-- -> returns JSONB (preserves types)
SELECT attributes -> 'color' FROM products; -- "red" (jsonb)
-- ->> returns TEXT
SELECT attributes ->> 'color' FROM products; -- red (text)
-- Path access
SELECT attributes #> '{dimensions, width}' FROM products;
SELECT attributes #>> '{dimensions, width}' FROM products;
-- Containment check
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
-- Key existence
SELECT * FROM products WHERE attributes ? 'dimensions';
-- Multiple key existence
SELECT * FROM products WHERE attributes ?| ARRAY['color', 'size'];
SELECT * FROM products WHERE attributes ?& ARRAY['color', 'weight'];
Indexing JSONB
GIN Index
The default GIN index supports containment (`@>`), key existence (`?`), and key/element existence (`?|`, `?&`):
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
GIN with jsonb_path_ops
The `jsonb_path_ops` operator class creates a more focused index that is faster for containment queries:
CREATE INDEX idx_products_attributes_path ON products
USING GIN (attributes jsonb_path_ops);
The `jsonb_path_ops` index is typically 1/3 the size of the default GIN index and 2-3x faster for `@>` queries. However, it does not support `?`, `?|`, or `?&` operators.
B-tree Index for JSONB Fields
For equality and range queries on specific JSONB fields, use a B-tree index on an expression:
CREATE INDEX idx_products_color ON products ((attributes ->> 'color'));
-- Now this query uses the index:
SELECT * FROM products WHERE attributes ->> 'color' = 'red';
Partial Index for Specific JSON Structures
CREATE INDEX idx_products_sale ON products ((attributes ->> 'price'))
WHERE attributes @> '{"sale": true}';
JSONB Modification Functions
-- Add or update a key
UPDATE products
SET attributes = jsonb_set(attributes, '{stock}', '42')
WHERE id = 1;
-- Remove a key
UPDATE products
SET attributes = attributes - 'temporary_flag'
WHERE id = 1;
-- Concatenate (merge) JSONB
UPDATE products
SET attributes = attributes || '{"on_sale": true, "discount_pct": 15}'
WHERE id = 1;
-- Delete from array
UPDATE products
SET attributes = attributes #- '{tags, 0}'
WHERE id = 1;
JSONB in Queries with Relational Data
The real power of JSONB in PostgreSQL is combining document flexibility with relational integrity:
-- Join JSONB data with relational tables
SELECT p.name,
p.attributes ->> 'color' AS color,
o.order_date,
o.quantity
FROM products p
JOIN orders o ON o.product_id = p.id
WHERE p.attributes @> '{"color": "red"}'
AND o.order_date > '2026-01-01';
-- Aggregate JSONB data per category
SELECT category,
jsonb_agg(attributes ORDER BY name) AS product_attrs
FROM products
GROUP BY category;
PostgreSQL vs MongoDB
When does PostgreSQL's JSONB make sense as a MongoDB alternative?
| Capability | PostgreSQL JSONB | MongoDB | |------------|-----------------|---------| | Schema enforcement | Optional (CHECK constraints) | Optional (schema validation) | | Joins | Full SQL JOIN support | $lookup (limited) | | Transactions | ACID, multi-document | Multi-document (since 4.0) | | Index types | B-tree, GIN, GiST, BRIN | B-tree, compound, text, geospatial | | Aggregation | SQL + JSONB functions | Aggregation pipeline | | Horizontal scaling | Read replicas, sharding (Citus) | Native sharding | | Geospatial | PostGIS (very mature) | Built-in 2dsphere |
Choose PostgreSQL with JSONB when you need:
* A mix of relational and document data with referential integrity.
* Complex joins and aggregations across structured and semi-structured data.
* ACID guarantees with JSON document consistency.
* Familiar SQL tooling and ORM integration.
Choose MongoDB when you need:
* Native horizontal sharding out of the box.
* Deeply nested, varied document structures across collections.
* A document-first data model without relational baggage.
Best Practices
* **Always use `jsonb`**, not `json`, unless you have a specific reason.
* **Add CHECK constraints** to validate JSON structure when possible:
ALTER TABLE products ADD CONSTRAINT valid_attributes
CHECK (jsonb_typeof(attributes -> 'price') = 'number');
* **Combine JSONB with regular columns**: Use relational columns for primary keys, timestamps, and foreign keys. Use JSONB for variable or extensible attributes.
* **Benchmark GIN indexes**: The default GIN index covers more operators. The `jsonb_path_ops` variant is faster for containment but less flexible.
* **Avoid JSONB for everything**: If your "attributes" are always queried with equality or range conditions, use regular columns with proper types. JSONB is best for truly variable schemas.
PostgreSQL's JSONB support bridges the gap between relational and document databases. Used wisely, it eliminates the need for a separate document store in many applications.