PostgreSQL 22039 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22039: sql_json_array_not_found — Causes, Fixes & Prevention

What Is Error 22039?

PostgreSQL error 22039 (sql_json_array_not_found) occurs when a SQL/JSON path expression expects to find an array at a specific location within a JSON document, but the value found is not an array — or the path itself doesn't exist. This error is most commonly triggered by functions like jsonb_path_query, jsonb_path_query_array, and SQL/JSON standard functions introduced in PostgreSQL 12 and later. It frequently appears in production systems that consume JSON data from external APIs where the structure isn't always consistent.

Top 3 Causes

1. Accessing Array Index on a Non-Array Value

The most common cause: a field you expect to be an array is actually a single object or scalar, but your JSON path uses array subscripts like [*] or [0].

-- This fails if "items" is an object, not an array
SELECT jsonb_path_query(data, 'strict $.items[*]')
FROM orders;

-- Fix: use lax mode and verify type first
SELECT jsonb_path_query(data, 'lax $.items[*]')
FROM orders
WHERE jsonb_typeof(data -> 'items') = 'array';

-- Or wrap non-arrays safely
SELECT
    id,
    CASE
        WHEN jsonb_typeof(data -> 'items') = 'array'
            THEN data -> 'items'
        ELSE jsonb_build_array(data -> 'items')
    END AS normalized_items
FROM orders;

2. Traversing a Non-Existent Intermediate Path

If an intermediate key in the JSON path doesn't exist, PostgreSQL can't reach the expected array and throws 22039.

-- Fails when "orders" key is missing from data
SELECT jsonb_path_query(data, '$.orders.items[*]')
FROM customer_data;

-- Fix: check path existence before querying
SELECT
    id,
    jsonb_path_query_array(data, '$.orders.items[*]') AS items
FROM customer_data
WHERE jsonb_path_exists(data, '$.orders.items[*]');

-- Safer alternative with COALESCE fallback
SELECT
    id,
    COALESCE(
        jsonb_path_query_array(data, 'lax $.orders.items[*]'),
        '[]'::jsonb
    ) AS items
FROM customer_data;

3. Using strict Mode Without Type Validation

In strict mode, PostgreSQL enforces that values accessed with array syntax must be arrays. Unlike lax mode, there is no automatic wrapping or silent failure — it raises 22039 immediately.

-- strict mode fails hard on non-arrays
SELECT jsonb_path_query(data, 'strict $.tags[*]')
FROM products;

-- Fix: switch to lax mode
SELECT jsonb_path_query(data, 'lax $.tags[*]')
FROM products;

-- If strict is required, validate first
SELECT jsonb_path_query(data, 'strict $.tags[*]')
FROM products
WHERE jsonb_typeof(data -> 'tags') = 'array';

Quick Fix Solutions

Create a reusable safe extraction function to avoid repetitive defensive code:

CREATE OR REPLACE FUNCTION safe_json_array(
    p_data JSONB,
    p_path TEXT
)
RETURNS JSONB
LANGUAGE plpgsql AS $$
DECLARE
    v_result JSONB;
BEGIN
    BEGIN
        v_result := jsonb_path_query_array(p_data, p_path::jsonpath);
    EXCEPTION
        WHEN sqlstate '22039' THEN
            RETURN '[]'::jsonb;
        WHEN OTHERS THEN
            RETURN '[]'::jsonb;
    END;
    RETURN v_result;
END;
$$;

-- Usage
SELECT id, safe_json_array(data, '$.items[*]') AS items
FROM orders;

Prevention Tips

Enforce array structure at insert time using CHECK constraints:

ALTER TABLE orders
ADD CONSTRAINT chk_items_must_be_array
CHECK (
    data -> 'items' IS NULL
    OR jsonb_typeof(data -> 'items') = 'array'
);

Monitor JSON structure consistency regularly with a diagnostic query:

SELECT
    jsonb_typeof(data -> 'items') AS field_type,
    COUNT(*) AS row_count
FROM orders
GROUP BY jsonb_typeof(data -> 'items')
ORDER BY row_count DESC;

Always prefer lax mode in production queries unless strict schema enforcement is explicitly required. Combine jsonb_path_exists checks before any array traversal to write resilient, error-proof SQL from the start.

Related Errors

Code	Name	Description
22035	no_sql_json_item	JSON path returns no items at all
22032	invalid_json_text	Malformed JSON input
22033	invalid_sql_json_subscript	Invalid array subscript used
2203A	sql_json_scalar_required	Expected scalar but got array/object

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

DEV Community