PostgreSQL 22034 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22034: more than one sql json item

PostgreSQL error code 22034 occurs when a SQL/JSON function — such as JSON_VALUE() or JSON_QUERY() — expects to return a single item, but the provided JSON path expression matches multiple items. This error became more common with PostgreSQL 16+, which introduced full SQL-standard JSON function support. Understanding when and why this error fires will save you significant debugging time in production.

---

Top 3 Causes

1. Using a Wildcard Path in JSON_VALUE()

JSON_VALUE() is strictly designed to return one scalar value. Using [*] or any wildcard that resolves to multiple values will immediately raise error 22034.

-- This FAILS: [*] selects multiple prices
SELECT JSON_VALUE(
    '{"items": [{"price": 10}, {"price": 20}]}'::json,
    '$.items[*].price'
);
-- ERROR: more than one SQL/JSON item

-- FIXED: Use a specific index
SELECT JSON_VALUE(
    '{"items": [{"price": 10}, {"price": 20}]}'::json,
    '$.items[0].price'
);
-- Result: 10

-- SAFER: Use NULL ON ERROR to avoid runtime crashes
SELECT JSON_VALUE(
    '{"items": [{"price": 10}, {"price": 20}]}'::json,
    '$.items[*].price'
    NULL ON ERROR
);
-- Result: NULL (no crash)

---

2. Missing WITH ARRAY WRAPPER in JSON_QUERY()

JSON_QUERY() also expects a single JSON value or object by default. When the path returns multiple independent values, you must wrap them using WITH ARRAY WRAPPER.

-- This FAILS: multiple results without wrapper
SELECT JSON_QUERY(
    '{"tags": ["postgresql", "json", "database"]}'::jsonb,
    '$.tags[*]'
);
-- ERROR: more than one SQL/JSON item

-- FIXED: Wrap results into a JSON array
SELECT JSON_QUERY(
    '{"tags": ["postgresql", "json", "database"]}'::jsonb,
    '$.tags[*]'
    WITH ARRAY WRAPPER
);
-- Result: ["postgresql", "json", "database"]

---

3. Overly Broad Path Expressions on Nested JSON

Complex nested JSON structures can return more results than expected when using recursive descent operators like .**.

-- Problematic: selects all "id" fields at every nesting level
SELECT JSON_VALUE(
    '{"id": 1, "child": {"id": 2}}'::jsonb,
    '$.**.id'
);
-- ERROR: more than one SQL/JSON item

-- FIXED: Be explicit about the path depth
SELECT JSON_VALUE(
    '{"id": 1, "child": {"id": 2}}'::jsonb,
    '$.id'
);
-- Result: 1

-- ALTERNATIVE: Use jsonb_path_query() for multiple results
SELECT value
FROM jsonb_path_query(
    '{"id": 1, "child": {"id": 2}}'::jsonb,
    '$.**.id'
) AS value;
-- Returns multiple rows: 1, 2

---

Quick Fix Solutions

Scenario	Solution
Need a single value	Use explicit index: $.items[0].price
Need multiple values as array	Add WITH ARRAY WRAPPER to JSON_QUERY()
Need multiple values as rows	Use jsonb_path_query() instead
Want to suppress the error safely	Add NULL ON ERROR or DEFAULT x ON ERROR

-- Production-safe pattern with DEFAULT ON ERROR
SELECT JSON_VALUE(
    order_data,
    '$.shipping.cost'
    DEFAULT 0.00 ON ERROR
) AS shipping_cost
FROM orders;

---

Prevention Tips

Always specify an ON ERROR clause when using JSON_VALUE() or JSON_QUERY() in production code. This prevents a path expression bug from escalating into a full service outage.

Validate path expressions before deployment using jsonb_path_query_array() to check how many items a path returns against real data samples:

-- Pre-deployment validation: count matched items
SELECT
    id,
    jsonb_array_length(
        jsonb_path_query_array(order_data, '$.items[*].price')
    ) AS matched_count
FROM orders
LIMIT 10;
-- If matched_count > 1, JSON_VALUE() will fail on those rows

---

Related Errors

• 22035 – no sql json item: The opposite problem — the path matches nothing.
• 22032 – invalid json text: Malformed JSON input, often a prerequisite issue.
• 22033 – invalid sql json subscript: Bad array index format in a JSON path.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.