How to fix More than one SQL/JSON item in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 117 views

This error occurs when a JSON_VALUE() function or similar SQL/JSON scalar function returns multiple items instead of the expected single value. It typically happens when your JSON path expression matches multiple array elements or objects.

Quick fix

Fixes 22034

-- This will fail: "$[*]" matches ALL array elements
SELECT JSON_VALUE(
  '[1, 2, 3]'::jsonb,
  '$[*]' -- Returns 3 items, but JSON_VALUE expects 1
);
-- ERROR: More than one SQL/JSON item
  1. Identify the JSON structure and path expression
  2. Use array indexing to extract a specific item
  3. Switch to JSON_QUERY() for multiple items
  4. Use jsonb_path_query for set-returning functions
postgresql — error
-- This will fail: "$[*]" matches ALL array elements
SELECT JSON_VALUE(
  '[1, 2, 3]'::jsonb,
  '$[*]' -- Returns 3 items, but JSON_VALUE expects 1
);
-- ERROR: More than one SQL/JSON item
More than one SQL/JSON item

What this error means

PostgreSQL's SQL/JSON functions like JSON_VALUE() are designed to extract and return a single scalar value from a JSON document. When the path expression you provide matches more than one item in the JSON structure—such as multiple array elements—PostgreSQL raises this error because it cannot coerce multiple values into a single scalar result. This is a safety mechanism to prevent data loss and ambiguous operations. The error code 22034 (more_than_one_sql_json_item) is part of PostgreSQL's SQL/JSON error handling system, which distinguishes between structural errors, type mismatches, and item count violations.

Typical symptoms

  • Error "More than one SQL/JSON item" when running JSON_VALUE() on JSON arrays
  • Query fails with error code 22034 during JSON path evaluation
  • Queries using strict path expressions on arrays with multiple elements
  • JSON_VALUE() functions that match multiple object properties or array items

Common causes

  1. Using JSON_VALUE() with a path expression that matches multiple array elements (e.g., "$[*]" or "$.items[*]")
  2. Applying scalar functions to JSON arrays without filtering or unwrapping array elements first
  3. Using strict mode path expressions that return sets instead of single values
  4. Incorrect jsonpath syntax that unintentionally matches multiple items instead of one

How to fix "More than one SQL/JSON item"

1Identify the JSON structure and path expression

First, understand what your JSON data looks like and which path expression is causing the error. For example:

sql
-- This will fail: "$[*]" matches ALL array elements
SELECT JSON_VALUE(
  '[1, 2, 3]'::jsonb,
  '$[*]' -- Returns 3 items, but JSON_VALUE expects 1
);
-- ERROR: More than one SQL/JSON item

Determine whether you actually need a single value or if you need to handle multiple values.

2Use array indexing to extract a specific item

If you only need one specific element from an array, use explicit array indexing instead of the wildcard operator:

sql
-- Correct: Get the first element
SELECT JSON_VALUE(
  '[1, 2, 3]'::jsonb,
  '$[0]' -- Returns the first item only
);
-- Result: 1

-- Get a specific index
SELECT JSON_VALUE(
  '[{"name": "Alice"}, {"name": "Bob"}]'::jsonb,
  '$[1].name' -- Returns just Bob
);
3Switch to JSON_QUERY() for multiple items

If you need to retrieve multiple items or complex structures, use JSON_QUERY() instead of JSON_VALUE():

sql
-- JSON_VALUE: Single scalar value only
SELECT JSON_VALUE(
  '[1, 2, 3]'::jsonb,
  '$[0]' -- Works: returns 1
);

-- JSON_QUERY: Any JSON structure
SELECT JSON_QUERY(
  '[1, 2, 3]'::jsonb,
  '$[*]' -- Works: returns [1,2,3]
);

JSON_QUERY() can return objects, arrays, and multiple values without throwing errors.

4Use jsonb_path_query for set-returning functions

For extracting multiple items as separate rows, use jsonb_path_query() or jsonb_path_query_array():

sql
-- Returns multiple rows, one per array element
SELECT jsonb_path_query(
  '[{"id":1}, {"id":2}, {"id":3}]'::jsonb,
  '$[*].id'
);
-- Result: 1, 2, 3 (3 separate rows)

-- For set-of operations
SELECT jsonb_path_query(
  '["apple", "banana", "cherry"]'::jsonb,
  '$[*]',
  silent => true -- Suppress errors if needed
);

These functions return a SET, not a single value, so they're appropriate for multiple items.

5Add ON ERROR clause to handle failures gracefully

If you're uncertain whether your path will return single or multiple items, use the ON ERROR clause:

sql
-- Return default value on error
SELECT JSON_VALUE(
  '[1, 2, 3]'::jsonb,
  '$[*]',
  DEFAULT -1 ON ERROR
);
-- Instead of raising error, returns -1

-- Return NULL on error (default behavior)
SELECT JSON_VALUE(
  '[1, 2, 3]'::jsonb,
  '$[*]',
  NULL ON ERROR
);

This allows your application to handle edge cases without crashing.

Advanced notes

PostgreSQL's SQL/JSON path language has two modes: lax (default) and strict. In lax mode, structural errors are suppressed and converted to empty sequences. In strict mode, errors are raised immediately. You can use strict mode to catch these issues early:

sql
SELECT JSON_VALUE(
  '[1, 2, 3]'::jsonb,
  'strict $[*]'
);
-- Explicitly throws error in strict mode

When dealing with nested JSON structures, remember that wildcards like $[*] and $.* always return multiple items. Use explicit indexing or the jsonb_path_query() family of functions instead. For complex filtering, consider whether you actually need SQL/JSON path expressions or if simpler operators like -> and ->> would suffice. Also note that JSON_VALUE() always removes quotes from returned scalar strings (equivalent to OMIT QUOTES), while JSON_QUERY() can preserve them with KEEP QUOTES.

Frequently asked questions

What does "22034" mean?
PostgreSQL's SQL/JSON functions like JSON_VALUE() are designed to extract and return a single scalar value from a JSON document. When the path expression you provide matches more than one item in the JSON structure—such as multiple array elements—PostgreSQL raises this error because it cannot coerce multiple values into a single scalar result. This is a safety mechanism to prevent data loss and ambiguous operations.
How do I fix "22034"?
This error occurs when a JSON_VALUE() function or similar SQL/JSON scalar function returns multiple items instead of the expected single value. It typically happens when your JSON path expression matches multiple array elements or objects.
What causes "22034"?
Using JSON_VALUE() with a path expression that matches multiple array elements (e.g., "$[]" or "$.items[]") Applying scalar functions to JSON arrays without filtering or unwrapping array elements first Using strict mode path expressions that return sets instead of single values
Is the "22034" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading