PostgreSQL JSONB

Learning Focus

PostgreSQL offers two JSON types: json and jsonb. Always default to jsonb — it stores binary-encoded JSON, supports GIN indexing, and queries significantly faster.

json vs jsonb

Aspect	json	jsonb
Storage	Exact text	Binary-encoded
Indexing	Not supported	GIN, Hash
Query speed	Re-parses each time	Fast
Duplicate keys	Preserved	Last value wins
Recommendation	Avoid	Always use

Create and Insert

CREATE TABLE products (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       TEXT NOT NULL,
    attributes JSONB
);

INSERT INTO products (name, attributes) VALUES
('Keyboard', '{
    "color": "black", "switches": "Cherry MX Red",
    "connectivity": ["USB-C", "Bluetooth"],
    "dimensions": {"width": 440, "depth": 135}
}'),
('Mouse', '{"color": "white", "dpi": 1600}');

Querying Operators

Operator	Returns	Description
->	JSON	Get JSON child by key
->>	TEXT	Get text child by key
#>	JSON	Get JSON at path array
#>>	TEXT	Get text at path array
?	boolean	Does key exist?
@>	boolean	Left contains right?
<@	boolean	Left contained by right?

-- Text value
SELECT attributes ->> 'color' FROM products;

-- Nested value
SELECT attributes -> 'dimensions' ->> 'width' FROM products;

-- Key existence
SELECT name FROM products WHERE attributes ? 'dpi';

-- Containment
SELECT name FROM products WHERE attributes @> '{"color": "black"}';

-- Array containment
SELECT name FROM products
WHERE attributes -> 'connectivity' @> '["Bluetooth"]';

GIN Index

CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- Path-specific expression index
CREATE INDEX idx_products_color ON products ((attributes ->> 'color'));

Updating JSONB

-- Set a value
UPDATE products
SET attributes = jsonb_set(attributes, '{color}', '"red"')
WHERE name = 'Mouse';

-- Merge / add keys
UPDATE products
SET attributes = attributes || '{"backlit": true}'
WHERE name = 'Keyboard';

-- Remove a key
UPDATE products
SET attributes = attributes - 'color'
WHERE name = 'Keyboard';

Concept Map

Concept Flow

JSONB Column
  ├── -> / ->>   Operators    →  SQL WHERE / SELECT
  ├── #> / #>>   Path access  →  SQL WHERE / SELECT
  ├── @>         Containment  →  SQL WHERE / SELECT
  ├── ?          Existence    →  SQL WHERE / SELECT
  └── GIN Index               →  Fast lookup

Common Pitfalls

Pitfall	Consequence	Prevention
Using json type	No GIN indexing	Always use jsonb
No type cast on extracted text	Text comparison not numeric	(attributes->>'price')::NUMERIC > 100
No GIN index on queried column	Full table scan	Add GIN index for all queried JSONB cols

What's Next

• Next: MySQL JSON — Compare with MySQL's JSON support.
• Section Overview — Return to the Databases module index.