PostgreSQL 22036 Error: Causes and Solutions Complete Guide

Source: Dev.to

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

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;

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)

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

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.

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? oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.