DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22039 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22039: sql_json_array_not_found

PostgreSQL error code 22039 (sql_json_array_not_found) occurs when a SQL/JSON path expression is evaluated in a context that expects a JSON array, but no array is found at the specified path. This error is most commonly encountered when using SQL/JSON functions introduced in PostgreSQL 14 and later, such as JSON_QUERY(), JSON_TABLE(), and jsonb_path_query_array(). Understanding why this happens and how to handle it gracefully is essential for building robust JSON-heavy applications.

Top 3 Causes

1. Path Expression Returns a Non-Array Value

The most common cause is when a JSON path points to a scalar value or a JSON object instead of an array. When JSON_QUERY() or similar functions are configured to expect an array (e.g., using WITH ARRAY WRAPPER with strict mode), encountering a non-array value triggers this error.

-- This will raise 22039 in strict error mode
-- because $.user is an object, not an array
SELECT JSON_QUERY(
    '{"user": {"name": "Alice"}}',
    'strict $.user'
    ERROR ON ERROR
);

-- Safe fix: use WITH ARRAY WRAPPER to auto-wrap non-array values
SELECT JSON_QUERY(
    '{"user": {"name": "Alice"}}',
    '$.user'
    WITH UNCONDITIONAL ARRAY WRAPPER
    NULL ON ERROR
);
-- Result: [{"name": "Alice"}]
Enter fullscreen mode Exit fullscreen mode

2. Missing ON ERROR / ON EMPTY Clause in Strict Mode

When SQL/JSON functions are called without explicit ON ERROR or ON EMPTY clauses, PostgreSQL defaults to raising an error the moment the path result doesn't match the expected type. Many developers overlook this behavior and write queries that work fine in development — where data is clean — but fail in production with inconsistent data.

-- Risky: no error handling, will throw 22039 if tags is not an array
SELECT JSON_QUERY(data, '$.tags') FROM events;

-- Safe: explicit ON ERROR and ON EMPTY handling
SELECT JSON_QUERY(
    data,
    'lax $.tags'
    WITH CONDITIONAL ARRAY WRAPPER
    NULL ON ERROR
    EMPTY ARRAY ON EMPTY
)
FROM events;
Enter fullscreen mode Exit fullscreen mode

3. Inconsistent JSON Data Structure Across Rows

When JSON data is inserted from multiple sources (different API versions, ETL pipelines, or client apps), the same key may hold different types across rows — sometimes an array, sometimes a plain string, sometimes null. A query that works on 99% of rows may suddenly fail on the outliers.

-- Detect rows where 'tags' is NOT an array
SELECT id, jsonb_typeof(data -> 'tags') AS tags_type
FROM events
WHERE data ? 'tags'
  AND jsonb_typeof(data -> 'tags') <> 'array';

-- Normalize non-array values to arrays before querying
SELECT id,
       CASE
           WHEN jsonb_typeof(data -> 'tags') = 'array'  THEN data -> 'tags'
           WHEN jsonb_typeof(data -> 'tags') = 'string' THEN jsonb_build_array(data -> 'tags')
           ELSE '[]'::jsonb
       END AS tags
FROM events;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Use lax mode combined with ON ERROR and ON EMPTY clauses as your default pattern for all SQL/JSON queries. This makes your queries resilient to unexpected data shapes without sacrificing correctness.

-- Universal safe pattern for JSON array access
SELECT
    id,
    JSON_QUERY(
        data,
        'lax $.tags'
        WITH CONDITIONAL ARRAY WRAPPER
        NULL ON ERROR
        EMPTY ARRAY ON EMPTY
    ) AS tags,
    jsonb_path_query_array(data, '$.tags[*]') AS tags_alt
FROM events
WHERE jsonb_typeof(data -> 'tags') = 'array';  -- pre-filter for safety
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Enforce array type with a CHECK constraint at the table level.
Adding a constraint ensures that bad data never enters your database in the first place, catching the problem at write time rather than at query time.

ALTER TABLE events
ADD CONSTRAINT chk_tags_is_array
CHECK (
    (data -> 'tags') IS NULL
    OR jsonb_typeof(data -> 'tags') = 'array'
);
Enter fullscreen mode Exit fullscreen mode

2. Always specify ON ERROR and ON EMPTY in every SQL/JSON function call.
Treat it like a seatbelt — make it a non-negotiable team coding standard. Add it to your code review checklist and lint rules so no JSON query ships to production without explicit error handling. Using lax mode as the default path mode also provides an extra layer of protection against structural mismatches.

Related Error Codes

  • 22032 (invalid_json_text): Malformed JSON string during parsing.
  • 22033 (impossible_json_subscript): Invalid index access on a JSON array.
  • 22034 (json_number_not_found): Expected a numeric value but found another type.
  • 22035 (json_object_not_found): Expected a JSON object but none was found — the mirror image of 22039.
  • 2203A (too_many_json_array_elements): Array exceeds the allowed element count.

📖 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.

Top comments (0)