PostgreSQL JSONB and Full-Text Search

JSONB Columns

-- Create table with JSONB
CREATE TABLE products (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  metadata    JSONB NOT NULL DEFAULT '{}',
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- GIN index for fast JSONB queries
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);

-- Insert with JSONB
INSERT INTO products (name, metadata) VALUES
  ('Sony WH-1000XM5', '{"brand": "Sony", "category": "headphones", "color": "black", "features": ["ANC", "30h battery"], "price": 399}'),
  ('Apple AirPods Pro', '{"brand": "Apple", "category": "earbuds", "features": ["ANC", "adaptive transparency"], "price": 249}');

JSONB Querying

-- Contains operator @>
SELECT name, metadata
FROM products
WHERE metadata @> '{"brand": "Sony"}';

-- Field access ->> (returns text)
SELECT name, metadata->>'brand' AS brand, (metadata->>'price')::numeric AS price
FROM products
WHERE (metadata->>'price')::numeric < 300;

-- Array contains ?& (has all keys) or ?| (has any key)
SELECT name
FROM products
WHERE metadata->'features' ? 'ANC';  -- Has ANC feature

-- Nested access
SELECT metadata->'specs'->>'weight' FROM products;

-- Aggregation on JSONB
SELECT
  metadata->>'brand' AS brand,
  COUNT(*) as product_count,
  AVG((metadata->>'price')::numeric) AS avg_price
FROM products
WHERE metadata ? 'price'
GROUP BY metadata->>'brand';

JSONB Updates

-- Add/update field
UPDATE products
SET metadata = metadata || '{"in_stock": true}'
WHERE id = '...';

-- Remove field
UPDATE products
SET metadata = metadata - 'discontinued_field'
WHERE id = '...';

-- Update nested field
UPDATE products
SET metadata = jsonb_set(metadata, '{specs, weight}', '"250g"')
WHERE id = '...';

-- Append to array
UPDATE products
SET metadata = jsonb_set(metadata, '{features}', (metadata->'features') || '["Multipoint"]')
WHERE id = '...';

Full-Text Search

-- Add tsvector column for search
ALTER TABLE products
ADD COLUMN search_vector TSVECTOR
GENERATED ALWAYS AS (
  to_tsvector('english',
    coalesce(name, '') || ' ' ||
    coalesce(metadata->>'description', '') || ' ' ||
    coalesce(metadata->>'brand', '') || ' ' ||
    coalesce(metadata->>'category', '')
  )
) STORED;

-- GIN index for fast search
CREATE INDEX idx_products_search ON products USING GIN(search_vector);

-- Full-text search
SELECT name, metadata->>'brand' AS brand,
       ts_rank(search_vector, query) AS relevance
FROM products,
     to_tsquery('english', 'noise & cancelling & headphones') query
WHERE search_vector @@ query
ORDER BY relevance DESC;

-- Web-friendly search (handles partial words)
SELECT name FROM products
WHERE search_vector @@ websearch_to_tsquery('english', 'noise cancelling headphones')
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', 'noise cancelling headphones')) DESC;

Highlighting Search Results

SELECT
  name,
  ts_headline(
    'english',
    name || ' ' || coalesce(metadata->>'description', ''),
    query,
    'StartSel=<mark>, StopSel=</mark>, MaxWords=30'
  ) AS highlighted
FROM products,
     websearch_to_tsquery('english', 'wireless headphones') query
WHERE search_vector @@ query
LIMIT 10;

When PostgreSQL Search Is Enough

Use PostgreSQL FTS when:
  - < 10M documents
  - Search is one feature among many
  - Data is already in PostgreSQL
  - Simple keyword search sufficient
  - Team knows SQL well

Use Elasticsearch when:
  - > 10M documents or complex analytics
  - Advanced features: fuzzy, synonyms, autocomplete at scale
  - Multiple data sources indexed together
  - Real-time streaming analytics
  - Search is the primary product feature

For most applications, PostgreSQL's built-in full-text search is powerful enough and eliminates the operational complexity of Elasticsearch.