Error
Error Code:
22036
PostgreSQL Error 22036: Non-Numeric SQL JSON Item
Description
This error indicates that a JSON value, expected to be numeric within a SQL operation, was found to be non-numeric. It typically occurs when attempting to perform calculations or type casts on JSON data that contains strings or other non-number types where a number is required.
Error Message
non numeric sql json item
Known Causes
4 known causesMismatched JSON Data Type
Providing a string or other non-numeric value (e.g., "123" instead of 123) to a PostgreSQL function or operation that expects a numeric JSON type.
Invalid Type Casting
Attempting to explicitly cast a non-numeric JSON element to a numeric SQL data type (like INT or DECIMAL) using operators like '->>' or '::numeric'.
Unexpected External Data
Receiving JSON data from an external source or application where a field expected to contain a number instead holds a string or null, leading to a type mismatch during processing.
Incorrect JSON Function Usage
Using PostgreSQL JSON functions (e.g., `jsonb_extract_path_text`, `jsonb_array_elements_text`) in a context that implicitly or explicitly expects a numeric value when the actual value is non-numeric.
Solutions
3 solutions available1. Validate JSON Input Before Insertion/Update medium
Ensure that JSON data conforms to expected numeric types before it's processed by PostgreSQL.
1
When constructing or receiving JSON data, implement validation logic in your application code. This logic should check if values intended to be numeric are indeed numeric. You can use built-in functions in most programming languages (e.g., `isNaN()` in JavaScript, `isnumeric()` in Python) to perform this check.
2
If the JSON is being generated within SQL (e.g., using `json_build_object` or `json_object`), ensure that the values being passed to these functions are of the correct type. Explicitly cast or convert values to numeric types if necessary before building the JSON.
SELECT json_build_object('id', 123, 'value', 45.67, 'name', 'example'); -- Correct
SELECT json_build_object('id', 123, 'value', 'not_a_number', 'name', 'example'); -- Will cause 22036 if 'value' is later expected to be numeric in a query.3
If you're receiving JSON from an external source, sanitize and validate it before inserting it into your PostgreSQL database. Reject or log malformed JSON.
2. Use `jsonb` and `->>` Operator Correctly easy
Leverage `jsonb` for efficiency and ensure the `->>` operator is used for text extraction when expecting non-numeric values or when casting later.
1
If you are storing JSON in a `json` column, consider migrating to `jsonb` if performance is a concern and you frequently query JSON contents. `jsonb` stores data in a decomposed binary format, which is faster to process. Note that `jsonb` also performs some validation on input.
ALTER TABLE your_table ALTER COLUMN your_json_column TYPE jsonb USING your_json_column;2
When extracting a JSON value that you intend to treat as text (even if it *looks* like a number but might be an empty string or have other non-numeric characters), use the `->>` operator. This operator returns the value as `text`.
SELECT your_json_column ->> 'some_key' FROM your_table;3
If you need to cast a JSON value to a numeric type, do so explicitly after extraction. If the extraction results in a non-numeric string that cannot be cast, the error will occur at the casting stage, making it clearer where the problem lies.
SELECT (your_json_column ->> 'numeric_field')::numeric FROM your_table;
-- If 'numeric_field' contains 'abc', this will fail. The error will be related to the cast, not necessarily a 'non numeric sql json item' during extraction.3. Modify JSON Data at the Source medium
Correct erroneous non-numeric values within the JSON data itself.
1
Identify the specific JSON document and the key/path within it that is causing the error. This usually involves examining the query that triggered the error and the data being processed.
2
Update the problematic JSON data to contain valid numeric types where expected. If the data is coming from an external API or user input, this might involve fixing the source or implementing a data cleaning process.
UPDATE your_table SET your_json_column = jsonb_set(your_json_column, '{path,to,non_numeric_field}', '"123.45"'::jsonb) WHERE ...;3
Alternatively, if the non-numeric item is not intended to be numeric, you might need to adjust your query to treat it as text or handle its absence. For example, if a field is sometimes a number and sometimes a string, and your query expects a number, you'll need to adjust the query or the data.