How to diagnose and fix the 22035 no_sql_json_item error code in Postgres.

by

The 22035 error code in PostgreSQL refers to the no_sql_json_item error. This error can occur when querying JSON data in PostgreSQL, and the query expects an item that is not found in the JSON structure. Here are some examples and sample code to diagnose and fix this error:

Example 1: Querying a Non-Existent JSON Field

Problematic Query:

SELECT my_json_column->>'non_existent_key' FROM my_table;

If my_json_column does not contain the key non_existent_key, this will raise the 22035 error.

Sample Code to Fix:
You can use the ? operator to check if the key exists before trying to access it.

SELECT my_json_column->>'existing_key' FROM my_table WHERE my_json_column ? 'existing_key';

Example 2: Expecting a JSON Array Item When There Is None

Problematic Query:

SELECT my_json_column->>0 FROM my_table;

If my_json_column does not contain an array or the array is empty, this will raise the 22035 error.

Sample Code to Fix:
You can check if the JSON array is not empty before querying for an item.

SELECT my_json_column->>0 FROM my_table WHERE jsonb_typeof(my_json_column) = 'array' AND jsonb_array_length(my_json_column) > 0;

Example 3: Using JSON Array Elements in a WHERE Clause

Problematic Query:

SELECT * FROM my_table WHERE my_json_column->>0 = 'some_value';

If my_json_column is not an array or the array does not have an element at index 0, this will result in the 22035 error.

Sample Code to Fix:
Ensure that you are querying an existing index of a JSON array.

SELECT * FROM my_table WHERE my_json_column->0 ?| array['some_value'];

Example 4: Incorrect JSON Path Query

Problematic Query:

SELECT jsonb_path_query(my_json_column, '$.nonExistentPath') FROM my_table;

If the JSON path does not exist in my_json_column, this may result in the 22035 error.

Sample Code to Fix:
Ensure the JSON path exists or handle the case where the path may not exist.

SELECT jsonb_path_query(my_json_column, '$.existingPath') FROM my_table WHERE jsonb_path_exists(my_json_column, '$.existingPath');

When diagnosing and fixing the 22035 error, ensure that your queries are structured to anticipate and handle cases where the JSON structure might not contain the expected items or paths. It’s also good practice to validate the JSON data structure before running queries against it, to ensure it meets the expected schema.

The PostgreSQL documentation provides a comprehensive list of error codes and their descriptions, which can help in diagnosing PostgreSQL errors.

Leave a Comment