JSONB set-returning function in WHERE

Severity: Info

JSONB set-returning functions like jsonb_array_elements() expand each matched row into multiple rows in-heap. When used inside an EXISTS subquery in a WHERE clause, the function itself cannot serve as an index access path — PostgreSQL must evaluate the expansion for every candidate row.

Example

-- Cannot use a GIN index: jsonb_array_elements expands the array row-by-row
SELECT count(*) FROM items
WHERE EXISTS (
  SELECT 1 FROM jsonb_array_elements(items.modifiers -> 'mods') AS mod
  WHERE mod ->> 'name' = 'fire'
);

-- May use a GIN index: @> is a GIN-compatible containment operator
SELECT count(*) FROM items
WHERE items.modifiers -> 'mods' @> '[{"name": "fire"}]';

The rewrite using @> is not always semantically equivalent. Whether it works depends on the actual data structure and query intent.

Why it matters

When a JSONB column has a GIN index, PostgreSQL can use operators like @>, ?, ?|, ?&, @?, and @@ to probe the index directly. Set-returning functions bypass this entirely — the planner has no way to push the expansion into an index lookup.

This is most impactful when:

• The table is large and few rows match the containment condition.
• The JSONB column has (or could have) a GIN index.
• The EXISTS subquery runs for every row in the outer query.

Detected functions

This nudge fires for the following set-returning functions when they appear in a WHERE context and operate on a column reference:

• jsonb_array_elements / json_array_elements
• jsonb_array_elements_text / json_array_elements_text
• jsonb_each / json_each
• jsonb_each_text / json_each_text

GIN-compatible operators

These operators can use a GIN index on a JSONB column:

Operator	Meaning	GIN operator class
@>	Contains (key/value or array containment)	both
?	Key exists	jsonb_ops only
?|	Any key exists	jsonb_ops only
?&	All keys exist	jsonb_ops only
@?	JSON path exists	both
@@	JSON path predicate	both

The default GIN operator class (jsonb_ops) supports all six. The smaller jsonb_path_ops class supports only @>, @?, and @@.

Detection

Query Doctor flags FuncCall nodes in the AST where the function name matches one of the known JSONB set-returning functions, the call appears under a whereClause in the traversal stack, and the function arguments reference a column.