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.

A second layer is that JSONB has been incrementally gaining capabilities over a decade of Postgres releases. Functions and syntax you read about on a recent blog post may not exist on the 9.6 cluster you inherited; conversely, ergonomic features you assume have to be opt-in via extensions may now be in core. Always check SELECT version(); first — half of “JSONB query not working” reports are simply running against a too-old Postgres.

PostgreSQL JSONB Version History — What Shipped When

• Postgres 9.2 (Sept 2012) introduced the json type — text storage with validation. No indexing, no operators for in-document search. This is still the type a lot of legacy schemas use; queries against it cannot use GIN indexes and behave very differently from jsonb.
• Postgres 9.4 (Dec 2014) added the jsonb binary type. This is the version where everything in this article starts working. jsonb supports the @>, ?, ?|, ?& operators and GIN indexing.
• Postgres 9.5 (Jan 2016) added jsonb_set(), jsonb_insert(), the || concat operator, and the - delete operator. Before 9.5, updating a single key required reading the whole object, modifying it client-side, and writing it back.
• Postgres 10 (Oct 2017) added jsonb_to_recordset improvements and the jsonb_array_elements_text set-returning function used heavily in Fix 5 below. Multi-element array unnesting became efficient.
• Postgres 12 (Oct 2019) introduced jsonpath support — the @?, @@, jsonb_path_query, jsonb_path_exists, and friends, implementing a large subset of the SQL/JSON path standard. Generated columns (also new in 12) enable indexing on computed JSONB expressions without a functional index.
• Postgres 13 (Sept 2020) improved jsonb_set argument handling for null paths and added jsonb_path_query_* variants that wrap results into JSONB arrays.
• Postgres 14 (Sept 2021) added the subscript syntax: data['key'] and data['arr'][0] instead of data->'key'. The shorter syntax is purely syntactic sugar — it compiles to the same operators — but it makes deeply nested access readable.
• Postgres 15 (Oct 2022) prepared the ground for JSON_TABLE by extending jsonpath support and the SQL/JSON constructor/predicate functions. The full JSON_TABLE standard implementation slipped to 17, but IS JSON and JSON_EXISTS came in 15 / 16.
• Postgres 16 (Sept 2023) delivered the SQL/JSON path predicates and IS JSON filter — WHERE data IS JSON OBJECT is a clean way to filter out malformed entries from a heterogeneous column.
• Postgres 17 (Sept 2024) shipped JSON_TABLE — finally a standards-compliant way to unfold JSON into a relational result set. On pre-17 you still need jsonb_array_elements + LATERAL joins.

If you run SELECT version(); and see 9.4 or 9.5, several functions in this article do not exist on your server — note the limitations as they come up.

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';

-- Postgres 14+ subscript syntax — equivalent to the above
SELECT attributes['address']['city'] FROM users;
SELECT * FROM users WHERE attributes['address']['city'] = '"London"';
-- Note: subscript returns JSONB, so the literal needs quotes — same as ->

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 functional B-tree 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;

-- Postgres 12+ generated columns — alternative to functional indexes
ALTER TABLE events ADD COLUMN event_type text
  GENERATED ALWAYS AS (data->>'type') STORED;
CREATE INDEX idx_event_type ON events (event_type);
-- Now you can query the generated column directly with full B-tree speed

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. The set-returning unnesting helpers below were added in Postgres 10 — on 9.4/9.5 you have jsonb_array_elements but the _text variant is less ergonomic.

-- 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')
);

-- Postgres 12+ jsonpath — concise alternative
SELECT * FROM products
WHERE attributes @@ '$.tags[*] == "sale"';

-- Postgres 17+ JSON_TABLE — standards-compliant unnest
SELECT p.id, t.tag
FROM products p,
     JSON_TABLE(
       p.attributes, '$.tags[*]'
       COLUMNS (tag text PATH '$')
     ) AS t
WHERE t.tag LIKE 'sale%';

Fix 6: Update and Modify JSONB Values

Updating specific JSONB keys without replacing the entire object. The jsonb_set function and || / - operators below all require Postgres 9.5 — on 9.4 you have to read, modify, and write the whole object from the client.

-- 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 (Postgres 10+)
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
-- Postgres 9.5 || is shallow; for deep merge use jsonb_set repeatedly or a helper function

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"';

-> vs ->> returns NULL when the key is missing, not an error — Postgres silently returns SQL NULL for missing keys, so WHERE data->>'key' = 'x' excludes those rows but also excludes rows where the key exists with a JSON null value. If you need to distinguish “missing key” from “explicit null”, use WHERE data ? 'key' first.

GIN index build is slow or bloats the table — GIN index creation locks the table by default. Use CREATE INDEX CONCURRENTLY to build it without blocking writes. Also consider jsonb_path_ops if you only query with @> — it produces a substantially smaller index (often 30-50% the size of default GIN).

Numeric comparison silently wrong because of text sort order — data->>'price' returns text, so WHERE data->>'price' > '100' does a lexicographic comparison: '9' is greater than '100'. Always cast with (data->>'price')::numeric for numeric comparisons. The same applies to dates — text-compare on ISO dates happens to work, but is brittle.

Generated column doesn’t update after row UPDATE — GENERATED ALWAYS AS ... STORED columns are recomputed on every row update, but the index on them is not automatically rebuilt during data migrations. If you backfill data with UPDATE and the generated column shows stale values in EXPLAIN, run REINDEX INDEX CONCURRENTLY idx_event_type; and VACUUM ANALYZE events;.

For related PostgreSQL issues, see Fix: PostgreSQL Index Not Used, Fix: PostgreSQL Slow Query, Fix: PostgreSQL Deadlock Detected, and Fix: PostgreSQL Relation Does Not Exist.