How to diagnose and fix the 22034 more_than_one_sql_json_item error code in Postgres.

by

The PostgreSQL error code 22034 corresponds to more_than_one_sql_json_item, which indicates that an operation on a JSON or JSONB value resulted in more than one item being returned where only one item was expected. This error can occur in various contexts, such as when using functions that expect a single JSON item as a result but are given an array or an object with multiple elements.

Here are some examples and sample code to diagnose and fix this error:

Example 1: Using jsonb_to_record with Multiple JSON Objects

When you attempt to convert a JSON array with multiple objects into a single record, you might encounter this error.

Problematic Query:

SELECT * FROM jsonb_to_record('[{"key": "value1"}, {"key": "value2"}]') AS x(key text);

Sample Code to Fix:
To fix this, ensure you’re working with a single JSON object or use jsonb_to_recordset for multiple objects.

SELECT * FROM jsonb_to_recordset('[{"key": "value1"}, {"key": "value2"}]') AS x(key text);

Example 2: Using jsonb_array_elements_text on a Non-array JSONB

Using jsonb_array_elements_text on a JSONB value that is not an array or contains more than one item can cause this error.

Problematic Query:

SELECT jsonb_array_elements_text('{"key": "value"}');

Sample Code to Fix:
Make sure to use this function on a JSON array.

SELECT jsonb_array_elements_text('["value1", "value2"]');

Example 3: Using ->> or -> Operators to Fetch Single JSON Value

When you use the ->> or -> operators to fetch a JSON value but the path leads to multiple items, you might get this error.

Problematic Query:

SELECT my_json_column->>'key' FROM my_table;

Assuming my_json_column contains multiple items for the key ‘key’.

Sample Code to Fix:
Ensure that the JSON path leads to a single item or handle multiple items appropriately.

SELECT my_json_column->'key'->>0 FROM my_table; -- If 'key' maps to an array

To diagnose the 22034 error, you should review the JSON data structure and the queries to ensure that where a single item is expected, a single item is indeed being provided. Use JSON processing functions and operators correctly according to whether you are working with JSON arrays or objects and according to how many items you expect to process.

For more detailed information on PostgreSQL error codes and their meanings, you can refer to the PostgreSQL documentation.

Leave a Comment