Fix: PostgreSQL JSONB Query Not Working — Operator Errors, Wrong Results, or Slow Queries

The Problem

A PostgreSQL JSONB query returns no results despite matching data:

SELECT * FROM products WHERE attributes->>'color' = 'red';
-- Returns 0 rows, but SELECT attributes FROM products shows {"color": "Red"}
-- Case mismatch — 'red' vs 'Red'

Or a containment query doesn’t match as expected:

SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';
-- Error: operator does not exist: jsonb @> text
-- Missing ::jsonb cast

Or JSONB queries are extremely slow on large tables:

EXPLAIN ANALYZE SELECT * FROM events WHERE data->>'type' = 'purchase';
-- Seq Scan on events (cost=0.00..45000.00 rows=12 width=500) (actual time=0.1..8200.3 rows=1200 loops=1)
-- Full table scan — no index

Why This Happens

PostgreSQL JSONB has two similar-looking operators (-> and ->>) that behave differently, and several non-obvious indexing requirements:

• -> returns JSONB, ->> returns text — data->'key' returns the value as JSONB (so '"red"' with quotes). data->>'key' returns the value as plain text ('red'). Using -> for text comparison silently fails because you’re comparing JSONB to a string literal.
• No automatic indexes on JSONB keys — a regular B-tree index on a JSONB column doesn’t help with key-level queries. You need a GIN index or a functional index on specific keys.
• Type mismatch in JSONB values — JSON stores numbers as text. data->>'count' = 5 fails because 5 is an integer. You need (data->>'count')::int = 5.
• @> operator requires JSONB on both sides — column @> '{"key": "value"}' requires the right side to be cast to JSONB: column @> '{"key": "value"}'::jsonb.

Fix 1: Use the Correct Operator

Understand ->, ->>, #>, and #>>:

-- Sample data
INSERT INTO products (id, attributes) VALUES
  (1, '{"color": "red", "size": "L", "tags": ["sale", "new"]}'),
  (2, '{"color": "blue", "size": "M", "tags": ["new"]}');

-- -> returns JSONB (value with type preserved)
SELECT attributes->'color' FROM products;
-- Returns: "red"   (JSONB string, includes quotes in display)
-- Returns: "blue"

-- ->> returns TEXT (value as plain text)
SELECT attributes->>'color' FROM products;
-- Returns: red   (plain text, no quotes)
-- Returns: blue

-- WRONG — comparing JSONB to text literal
SELECT * FROM products WHERE attributes->'color' = 'red';
-- ERROR: operator does not exist: jsonb = text

-- CORRECT — use ->> for text comparison
SELECT * FROM products WHERE attributes->>'color' = 'red';

-- CORRECT — or compare to JSONB literal
SELECT * FROM products WHERE attributes->'color' = '"red"';

-- #> for nested paths (returns JSONB)
SELECT attributes#>'{address,city}' FROM users;

-- #>> for nested paths (returns TEXT)
SELECT attributes#>>'{address,city}' FROM users;

-- Nested access
SELECT *
FROM users
WHERE attributes#>>'{address,city}' = 'London';

Fix 2: Fix Type Casting

JSONB stores all values as text internally. Cast when comparing to non-string types:

-- Sample: data = '{"count": 42, "price": 19.99, "active": true}'

-- WRONG — comparing text to integer
SELECT * FROM items WHERE data->>'count' = 5;
-- ERROR: operator does not exist: text = integer

-- CORRECT — cast to the appropriate type
SELECT * FROM items WHERE (data->>'count')::int = 5;
SELECT * FROM items WHERE (data->>'price')::numeric > 10.00;
SELECT * FROM items WHERE (data->>'active')::boolean = true;

-- Date comparison
SELECT * FROM events WHERE (data->>'created_at')::timestamptz > NOW() - INTERVAL '7 days';

-- Numeric range
SELECT * FROM products
WHERE (attributes->>'price')::numeric BETWEEN 10 AND 100;

-- NULL handling — missing key vs null value
SELECT * FROM products WHERE attributes->>'size' IS NULL;
-- Matches rows where 'size' key is absent OR has JSON null value

-- Check key exists
SELECT * FROM products WHERE attributes ? 'size';
-- ? operator: returns true if key exists (regardless of value)

SELECT * FROM products WHERE attributes ?| ARRAY['size', 'color'];
-- ?| any of these keys exist

SELECT * FROM products WHERE attributes ?& ARRAY['size', 'color'];
-- ?& all of these keys exist

Fix 3: Use @> for Containment Queries

The @> (contains) operator is the most efficient for matching JSONB objects:

-- @> checks if left JSONB contains right JSONB
-- WRONG — missing ::jsonb cast on the right side
SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';
-- May work in some contexts but be explicit:

-- CORRECT
SELECT * FROM users WHERE preferences @> '{"theme": "dark"}'::jsonb;

-- Match multiple keys at once
SELECT * FROM products
WHERE attributes @> '{"color": "red", "size": "L"}'::jsonb;

-- Match array element
SELECT * FROM products
WHERE attributes @> '{"tags": ["sale"]}'::jsonb;
-- Returns rows where tags array contains "sale"

-- <@ (is contained by) — opposite direction
SELECT * FROM products
WHERE '{"color": "red"}'::jsonb <: attributes;
-- Returns rows where attributes contains at least {"color": "red"}

@> vs ->> performance:

-- ->> with GIN index (functional index) — for exact key lookups
CREATE INDEX idx_color ON products ((attributes->>'color'));
SELECT * FROM products WHERE attributes->>'color' = 'red';

-- @> with GIN index — for containment, more flexible
CREATE INDEX idx_gin ON products USING GIN (attributes);
SELECT * FROM products WHERE attributes @> '{"color": "red"}'::jsonb;
-- GIN index is used automatically with @>

Fix 4: Add GIN Indexes for Fast JSONB Queries

Without the right index, JSONB queries do a full table scan:

-- JSONB query without index — full scan
EXPLAIN ANALYZE
SELECT * FROM events WHERE data->>'type' = 'purchase';
-- Seq Scan on events (actual time=...8200ms...)

-- Create a GIN index on the whole JSONB column (supports @>, ?, ?|, ?&)
CREATE INDEX idx_events_data_gin ON events USING GIN (data);

-- Now @> and ? queries use the index
EXPLAIN ANALYZE
SELECT * FROM events WHERE data @> '{"type": "purchase"}'::jsonb;
-- Bitmap Index Scan on idx_events_data_gin (actual time=...12ms...)

-- Create a functional index for a specific key (for ->> queries)
CREATE INDEX idx_events_type ON events ((data->>'type'));

-- Now exact key comparisons are fast
EXPLAIN ANALYZE
SELECT * FROM events WHERE data->>'type' = 'purchase';
-- Index Scan using idx_events_type (actual time=...0.5ms...)

-- Partial functional index for common values
CREATE INDEX idx_active_users ON users ((preferences->>'theme'))
WHERE (preferences->>'active')::boolean = true;

GIN index options:

-- Default GIN — indexes every key and value
CREATE INDEX idx_gin ON products USING GIN (attributes);

-- jsonb_path_ops — smaller, faster for @> only
-- Doesn't support ?, ?|, ?& operators
CREATE INDEX idx_gin_path ON products USING GIN (attributes jsonb_path_ops);

-- Choose based on query patterns:
-- If you only use @>: use jsonb_path_ops (faster, smaller)
-- If you use ?, ?|, ?& too: use default GIN

Fix 5: Query JSONB Arrays

Querying inside JSONB arrays requires specific operators and functions:

-- Sample: tags = ["sale", "new", "featured"]

-- Check if array contains a value
SELECT * FROM products
WHERE attributes @> '{"tags": ["sale"]}'::jsonb;

-- Check any array element matches (using jsonb_array_elements)
SELECT DISTINCT p.*
FROM products p,
     jsonb_array_elements_text(p.attributes->'tags') AS tag
WHERE tag = 'sale';

-- Get array length
SELECT id, jsonb_array_length(attributes->'tags') AS tag_count
FROM products
WHERE jsonb_array_length(attributes->'tags') > 2;

-- Unnest and filter
SELECT id, tag
FROM products,
     jsonb_array_elements_text(attributes->'tags') AS tag
WHERE tag LIKE 'sale%';

-- Check if any array element matches a condition
SELECT * FROM products
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements_text(attributes->'tags') AS tag
  WHERE tag IN ('sale', 'clearance')
);

Fix 6: Update and Modify JSONB Values

Updating specific JSONB keys without replacing the entire object:

-- Replace the entire JSONB column (avoid — loses other data)
UPDATE products SET attributes = '{"color": "blue"}' WHERE id = 1;

-- Set a specific key (preserves other keys)
UPDATE products
SET attributes = jsonb_set(attributes, '{color}', '"blue"')
WHERE id = 1;

-- Set a nested key
UPDATE products
SET attributes = jsonb_set(attributes, '{address,city}', '"London"')
WHERE id = 1;

-- Remove a key
UPDATE products
SET attributes = attributes - 'old_key'
WHERE id = 1;

-- Remove multiple keys
UPDATE products
SET attributes = attributes - ARRAY['key1', 'key2']
WHERE id = 1;

-- Append to a JSONB array
UPDATE products
SET attributes = jsonb_set(
  attributes,
  '{tags}',
  (attributes->'tags') || '["clearance"]'::jsonb
)
WHERE id = 1;

-- Merge two JSONB objects (|| operator)
UPDATE products
SET attributes = attributes || '{"new_key": "new_value", "color": "green"}'::jsonb
WHERE id = 1;
-- || merges, with right side winning on key conflicts

Still Not Working?

JSON vs JSONB — PostgreSQL has both json and jsonb column types. jsonb stores data in a decomposed binary format that supports indexing and operators like @>. json stores the raw text and doesn’t support GIN indexing. If your column is json, many operators don’t work — ALTER TABLE products ALTER COLUMN attributes TYPE jsonb USING attributes::jsonb.

EXPLAIN shows index not used — even with a GIN index, PostgreSQL may not use it if the query returns a large fraction of the table (the planner prefers a seq scan). Use SET enable_seqscan = off to force index usage temporarily and confirm it works. For the planner to choose the index in production, ensure table statistics are up to date: ANALYZE products.

jsonpath for complex queries (PostgreSQL 12+) — for complex nested queries, jsonb_path_query and the @@ operator provide XPath-like navigation:

-- Find products where any tag starts with 'sale'
SELECT * FROM products
WHERE attributes @@ '$.tags[*] starts with "sale"';

For related PostgreSQL issues, see Fix: PostgreSQL Index Not Used and Fix: PostgreSQL Slow Query.