JSONB Operators & GIN Indexing

JSONB Operators & GIN Indexing

PostgreSQL's jsonb type is a first-class citizen. Its binary storage format strips whitespace, deduplicates keys, and sorts object keys — which enables a rich set of operators and efficient GIN indexing.

Complete Operator Reference

• -> — extract field as jsonb (keeps structure): metadata -> 'tags'
• ->> — extract field as text: metadata ->> 'brand'
• #> — extract at path as jsonb: metadata #> '{specs,ram_gb}'
• #>> — extract at path as text: metadata #>> '{specs,ram_gb}'
• @> — left contains right (supports GIN index): metadata @> '{"brand":"Acme"}'
• <@ — left is contained by right: '{"brand":"Acme"}' <@ metadata
• ? — key exists: metadata ? 'brand'
• ?| — any key exists: metadata ?| ARRAY['brand','sku']
• ?& — all keys exist: metadata ?& ARRAY['brand','sku']
• || — concatenate / merge two jsonb values
• - — delete a key: metadata - 'brand'
• #- — delete at path: metadata #- '{specs,ram_gb}'

Modifying JSONB Columns

-- Add or overwrite a top-level key
UPDATE products
SET metadata = metadata || '{"on_sale": true}'
WHERE id = 1;

-- Remove a key
UPDATE products
SET metadata = metadata - 'on_sale'
WHERE id = 1;

-- Update a nested value with jsonb_set
UPDATE products
SET metadata = jsonb_set(metadata, '{specs,ram_gb}', '32')
WHERE id = 1;

-- Append to a jsonb array
UPDATE products
SET metadata = jsonb_set(
  metadata,
  '{tags}',
  (metadata -> 'tags') || '"featured"'
)
WHERE id = 1;

GIN Index Variants

-- Default GIN: accelerates @>, ?, ?|, ?&
CREATE INDEX idx_products_meta_gin
  ON products USING GIN (metadata);

-- jsonb_path_ops GIN: smaller, only supports @>
CREATE INDEX idx_products_meta_path
  ON products USING GIN (metadata jsonb_path_ops);

-- Functional index on a specific key (B-tree, for = and ORDER BY)
CREATE INDEX idx_products_brand
  ON products ((metadata ->> 'brand'));