Gotcha: PostgreSQL JSONB operators and indexing

JSONB queries are slow because the wrong operator or missing index is used.

JSONB operators and proper indexing:

-- JSONB access operators
SELECT data->'name'           -- JSONB value (returns JSONB)
SELECT data->>'name'          -- Text value (returns TEXT)
SELECT data->'address'->>'city'  -- Nested text value
SELECT data#>'{a,b,c}'        -- Path access (JSONB)
SELECT data#>>'{a,b,c}'       -- Path access (TEXT)

-- Containment (most useful for queries)
SELECT * FROM items WHERE data @> '{"status": "active"}';
SELECT * FROM items WHERE data @> '{"tags": ["python"]}';

-- Existence
SELECT * FROM items WHERE data ? 'email';      -- Key exists
SELECT * FROM items WHERE data ?| array['a','b']; -- Any key exists
SELECT * FROM items WHERE data ?& array['a','b']; -- All keys exist

-- INDEXING (critical for performance)

-- GIN index on entire JSONB column (most flexible)
CREATE INDEX idx_items_data ON items USING GIN (data);
-- Supports: @>, ?, ?|, ?&

-- GIN with jsonb_path_ops (smaller, faster for @>)
CREATE INDEX idx_items_data_path ON items USING GIN (data jsonb_path_ops);
-- Only supports: @> (containment)

-- B-tree index on specific field (fastest for equality/range)
CREATE INDEX idx_items_status ON items ((data->>'status'));
SELECT * FROM items WHERE data->>'status' = 'active';  -- Uses index!

-- Expression index for computed values
CREATE INDEX idx_items_name_lower ON items (lower(data->>'name'));

Gotcha: -> returns JSONB, ->> returns TEXT. Comparing with wrong type won't use index:

-- BAD: data->'age' returns JSONB, comparing with integer
WHERE data->'age' > 18  -- WRONG type comparison!

-- GOOD: Cast properly
WHERE (data->>'age')::int > 18

Without proper indexes, JSONB queries do full table scans. The choice between GIN and B-tree depends on query patterns: GIN for containment, B-tree for specific field lookups.

PostgreSQL databases using JSONB columns