How to diagnose and fix the 22039 sql_json_array_not_found error code in Postgres.

by

The 22039 error code in PostgreSQL indicates sql_json_array_not_found. This error occurs when a JSON array is expected at a specified JSON path location, but it is not found. Here are some examples and sample code to diagnose and fix this error:

Example 1: Attempting to Access an Array Element at a Non-Array JSON Path

Problematic Query:

SELECT jsonb_path_query_first(my_json_column, '$.nonArrayField[0]') FROM my_table;

If nonArrayField is not an array, this will raise the 22039 error.

Sample Code to Fix:
Ensure the JSON path points to an array field.

SELECT jsonb_path_query_first(my_json_column, '$.actualArrayField[0]') FROM my_table WHERE jsonb_typeof(my_json_column->'actualArrayField') = 'array';

Example 2: Using Array Functions on Non-Array JSON Elements

Problematic Query:

SELECT my_json_column->'nonArrayField'->>0 FROM my_table;

If nonArrayField is not an array, this will result in the 22039 error.

Sample Code to Fix:
Check if the field is an array before attempting to use array functions.

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

Example 3: Unwrapping a Non-Existent JSON Array

Problematic Query:

SELECT jsonb_array_elements(my_json_column->'nonExistentArray') FROM my_table;

If nonExistentArray does not exist or is not an array, this will raise the 22039 error.

Sample Code to Fix:
Check for the existence and type of the JSON field before unwrapping.

SELECT jsonb_array_elements(my_json_column->'existentArray') FROM my_table WHERE my_json_column ? 'existentArray' AND jsonb_typeof(my_json_column->'existentArray') = 'array';

Example 4: Using JSON Array Functions on Non-Array Types

Problematic Query:

SELECT jsonb_array_length(my_json_column->'nonArrayField') FROM my_table;

If nonArrayField is not an array, this will trigger the 22039 error.

Sample Code to Fix:
Only call jsonb_array_length on valid JSON array fields.

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

To diagnose and fix the 22039 error, you should:

  1. Verify that the JSON path you are querying should indeed point to an array.
  2. Use the jsonb_typeof function to ensure that the element at the path is an array before performing array-specific operations.
  3. Use the ? operator to check for the existence of a key before attempting to access it.

When dealing with JSON data in PostgreSQL, it’s crucial to understand the structure of the JSON and to use the appropriate functions and operators for arrays and non-array elements. The PostgreSQL documentation provides detailed information on working with JSON and JSONB data types.

Leave a Comment