PostgreSQL 22036 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22036: non numeric sql json item

PostgreSQL error code 22036 (non numeric sql json item) is thrown when a SQL/JSON path expression attempts to perform a numeric operation — such as arithmetic or numeric methods like .abs(), .floor(), or .ceiling() — on a JSON item that is not actually a number (e.g., a string, boolean, null, array, or object). This error is most commonly encountered when using jsonb_path_query, jsonb_path_exists, or the @@ and @? operators introduced as part of the SQL/JSON standard in PostgreSQL 12+. If your JSON data originates from external APIs or legacy systems, type inconsistencies are a leading cause of this error appearing unexpectedly in production.

Top 3 Causes

1. Numeric-looking values stored as JSON strings

A very common real-world pattern: a price or score field is serialized as "123.45" (a string) instead of 123.45 (a number). Any numeric JSON Path method will immediately raise 22036.

-- This FAILS: "price" is a string, not a number
SELECT jsonb_path_query('{"price": "99.99"}', '$.price.floor()');
-- ERROR: 22036: non numeric sql json item

-- FIX: Use .double() to convert string to numeric in JSON Path
SELECT jsonb_path_query('{"price": "99.99"}', '$.price.double().floor()');
-- Result: 99

-- FIX: Or cast at SQL level
SELECT (data->>'price')::numeric AS price
FROM products
WHERE jsonb_typeof(data->'price') = 'string';

2. Applying numeric methods directly to arrays or objects without unwrapping

When a JSON node is an array or nested object, you must unwrap it using [*] before applying numeric methods. Applying .abs() or similar to the array itself triggers 22036.

-- This FAILS: $.scores returns an array, not a number
SELECT jsonb_path_query('{"scores": [-5, 10, -3]}', '$.scores.abs()');
-- ERROR: 22036

-- FIX: Unwrap the array with [*] first
SELECT jsonb_path_query('{"scores": [-5, 10, -3]}', '$.scores[*].abs()');
-- Results: 5, 10, 3

-- Real-world batch example
SELECT
    id,
    jsonb_path_query_array(payload, '$.readings[*].value.abs()') AS abs_readings
FROM telemetry
WHERE jsonb_path_exists(payload, '$.readings[*]');

3. NULL or boolean values reaching numeric operations

When a JSON field contains null, true, or false and no type guard is in place, any numeric JSON Path operation will raise 22036. This is especially dangerous in bulk processing jobs where a single bad record can roll back an entire transaction.

-- This FAILS: null is not numeric
SELECT jsonb_path_query('{"value": null}', '$.value.abs()');
-- ERROR: 22036

-- This FAILS too: boolean is not numeric
SELECT jsonb_path_query('{"active": true}', '$.active.abs()');
-- ERROR: 22036

-- FIX: Filter by type in SQL before applying JSON Path
SELECT jsonb_path_query(data, '$.value.abs()') AS result
FROM sensor_data
WHERE jsonb_typeof(data->'value') = 'number';

-- FIX: Use lax mode to silently skip non-numeric items
SELECT jsonb_path_query_first(data, 'lax $.value.floor()') AS floored
FROM sensor_data;

Quick Fix Solutions

-- 1. Check the actual JSON type before querying
SELECT jsonb_typeof(data->'field') FROM your_table LIMIT 10;

-- 2. Safe numeric extraction wrapper
CREATE OR REPLACE FUNCTION safe_json_number(p_data jsonb, p_path text)
RETURNS numeric AS $$
BEGIN
    IF jsonb_typeof(jsonb_path_query_first(p_data, p_path::jsonpath)) = 'number' THEN
        RETURN (jsonb_path_query_first(p_data, p_path::jsonpath))::numeric;
    END IF;
    RETURN NULL;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage
SELECT safe_json_number(data, '$.price') FROM products;

-- 3. Add a CHECK constraint at table level to prevent bad data entry
ALTER TABLE products
ADD CONSTRAINT chk_price_is_number
CHECK (jsonb_typeof(data->'price') = 'number');

Prevention Tips

Validate JSON types at ingestion time. Add CHECK constraints or triggers to ensure that critical numeric fields in your JSONB columns actually contain JSON numbers before data is stored. Catching the problem at write time is far cheaper than debugging it in complex queries at read time.

Standardize on lax mode and type-safe wrapper functions. Adopt lax JSON Path mode for queries where missing or incorrectly typed fields should be treated as NULL rather than errors, and build shared utility functions that encapsulate type checking. This protects every query written by your team without requiring each developer to remember to add explicit type guards every time.

📖 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