PostgreSQL 22036 Error: Causes and Solutions Complete Guide

PostgreSQL 22036 Error: Causes and Solutions Complete Guide



Tópico: PostgreSQL 22036 Error: Causes and Solutions Complete Guide
Categoria: Tutoriais | Programação & Tecnologia
Idioma Principal: Português (Conteúdo de Tecnologia)

Descrição do Conteúdo / Informações:
-------------------------------------------------------------------------


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.