PostgreSQL 22036 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22036: non numeric sql json item

PostgreSQL error code 22036 (non numeric sql json item) occurs when a SQL/JSON path expression attempts to perform a numeric operation on a JSON item that is not a number — such as a string, boolean, array, or object. This error was introduced alongside the SQL/JSON Path feature in PostgreSQL 12 and typically surfaces in queries using jsonb_path_query, jsonb_path_exists, or the @@ and @? operators.

Top 3 Causes

1. Numeric Values Stored as Strings

The most common cause is JSON data where numbers are stored as quoted strings (e.g., "price": "100" instead of "price": 100). This frequently happens with data from external APIs or legacy systems that don't enforce type consistency.

-- Triggers 22036: "price" is a string, not a number
SELECT jsonb_path_query('{"price": "100"}', '$.price + 50');
-- ERROR:  non numeric SQL/JSON item

-- Fix: Use the .double() conversion method in JSON Path
SELECT jsonb_path_query('{"price": "100"}', '$.price.double() + 50');
-- Result: 150

-- Alternative fix: Cast at the SQL level
SELECT (data->>'price')::numeric + 50
FROM (SELECT '{"price": "100"}'::jsonb AS data) t;
-- Result: 150

2. Arithmetic Applied Directly to Arrays or Objects

Developers sometimes write JSON Path expressions that target an entire array or object instead of individual elements, then attempt arithmetic on the result.

-- Triggers 22036: $.scores returns an array, not a number
SELECT jsonb_path_query('{"scores": [80, 90, 70]}', '$.scores + 10');
-- ERROR:  non numeric SQL/JSON item

-- Fix: Target a specific array index
SELECT jsonb_path_query('{"scores": [80, 90, 70]}', '$.scores[0] + 10');
-- Result: 90

-- Fix: Use wildcard to apply operation to each element
SELECT jsonb_path_query('{"scores": [80, 90, 70]}', '$.scores[*] + 10');
-- Results: 90, 100, 80

-- Fix: Use unnest for aggregation use cases
SELECT elem::numeric + 10
FROM jsonb_array_elements(
    '{"scores": [80, 90, 70]}'::jsonb -> 'scores'
) AS elem;

3. null or boolean Values in Numeric Paths

When JSON fields contain null, true, or false and a numeric JSON Path operation is applied to them, PostgreSQL raises 22036. This is common in datasets where missing values default to null or status flags are stored as booleans.

-- Triggers 22036: null is not numeric
SELECT jsonb_path_query('{"value": null}', '$.value + 10');
-- ERROR:  non numeric SQL/JSON item

-- Fix: Filter by type inside the JSON Path expression
SELECT jsonb_path_query(
    '{"items": [10, null, 30]}',
    '$.items[*] ? (@ != null)'
);
-- Results: 10, 30

-- Fix: Guard with jsonb_typeof at SQL level
SELECT
    CASE
        WHEN jsonb_typeof(data -> 'value') = 'number'
        THEN (data ->> 'value')::numeric + 10
        ELSE NULL
    END AS result
FROM (SELECT '{"value": null}'::jsonb AS data) t;
-- Result: NULL (no error)

Quick Fix Solutions

Use this reusable helper function to safely extract numeric values from any JSON document without risking a 22036 error in production:

CREATE OR REPLACE FUNCTION safe_json_numeric(
    p_data   jsonb,
    p_path   text,
    p_default numeric DEFAULT 0
)
RETURNS numeric
LANGUAGE plpgsql AS $$
DECLARE
    v_raw text;
BEGIN
    v_raw := jsonb_path_query_first(p_data, p_path::jsonpath)::text;
    IF v_raw IS NULL OR v_raw = 'null' THEN
        RETURN p_default;
    END IF;
    RETURN v_raw::numeric;
EXCEPTION
    WHEN SQLSTATE '22036' THEN RETURN p_default;
    WHEN others           THEN RETURN p_default;
END;
$$;

-- Usage
SELECT safe_json_numeric('{"price": "99.9"}'::jsonb, '$.price', 0);
-- Result: 99.9

SELECT safe_json_numeric('{"price": null}'::jsonb, '$.price', -1);
-- Result: -1

Prevention Tips

Enforce types at insert time using CHECK constraints:

CREATE TABLE orders (
    id   serial PRIMARY KEY,
    data jsonb  NOT NULL,
    CONSTRAINT chk_amount_is_number
        CHECK (jsonb_typeof(data -> 'amount') = 'number')
);

Validate types before running JSON Path arithmetic in queries:

Always pair numeric JSON Path operations with a jsonb_typeof() guard or a ? (@ != null) filter. Treat all externally sourced JSON as untrusted and validate types explicitly before processing. This simple habit eliminates the vast majority of 22036 errors in production systems.

Related Errors

Code	Name	Notes
`22032`	invalid input syntax for type json	Malformed JSON at parse time
`22033`	invalid SQL JSON subscript	Bad array index in JSON Path
`22034`	more than one SQL JSON item	Multiple items where one expected
`22035`	no SQL JSON item	Empty result where one required

📖 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