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.
