PostgreSQL Error - 22039 sql_json_array_not_found Error Explained

What is sql_json_array_not_found and how do I fix it?

The PostgreSQL error sql_json_array_not_found (code 22039) is raised by SQL/JSON functions when a path expression reaches a location that is not an array, even though an array was required by the query.

The error often appears in SELECT lists, WHERE clauses, or UPDATE statements that use jsonb_path_query, jsonb_path_exists, or similar SQL/JSON operators with the strict (#>) array accessor.

Resolving the issue quickly is important because the statement fails completely – no rows are returned or modified until the JSON path is corrected.

What Causes This Error?

PostgreSQL throws 22039 when the query assumes a JSON element is an array but encounters an object, scalar, or missing key.

Any strict array accessor in the path immediately aborts.

Using zero-based index notation (like [0]) against a scalar value or optional array causes the same failure in strict mode.

How to Fix sql_json_array_not_found

First, verify the JSON column really contains an array at the targeted path.

Inspect a sample row with jsonb_path_query_first or -> operator.

If the data model allows null or scalar values, switch to lax mode by prefixing the array step with ? or use the optional (#?) operator so non-arrays are skipped instead of raising an error.

Common Scenarios and Solutions

Scenario 1 - Selecting the first tag in an events column:

SELECT data#>>'{tags,0}' FROM logs;

This fails when tags is not an array.

Use:

SELECT data#>>'{tags?(@type() == "array"),0}' FROM logs;

Scenario 2 - Updating nested items:

UPDATE orders
SET details = jsonb_set(details,'{items,0,price}', '9.99')
WHERE id = 1;

Ensure items is an array or cast it first.

Best Practices to Avoid This Error

Validate JSON schemata before ingestion so array fields are always arrays. Apply CHECK constraints using jsonb_typeof().

Use lax path modifiers or COALESCE to default missing arrays to an empty array.

Document JSON contracts in your API layer.

Related Errors and Solutions

Error 22032 (sql_json_item_cannot_be_array) occurs when you try to create an array where one is not allowed.

Error 22030 (sql_json_scalar_required) indicates a scalar was expected at the path. Fixes mirror those for 22039 - inspect types and correct the path.

.