PostgreSQL raises non_numeric_sql_json_item (SQLSTATE 22036) when a SQL/JSON query tries to treat a non-numeric JSON value as a number.
non_numeric_sql_json_item appears when a SQL/JSON path or function expects a numeric value but finds a string, boolean, or null instead. Cast the JSON field to the correct type or validate the data before running the query to resolve the error.
non_numeric_sql_json_item
non_numeric_sql_json_item (SQLSTATE 22036) is thrown by PostgreSQL when SQL/JSON processing encounters a JSON item that is not numeric where a numeric value is required.
The error usually appears in JSON path queries, jsonb_path_query(), jsonb_path_exists(), or when casting JSON text to numeric types inside SQL expressions.
A JSON string, boolean, object, or null is supplied where the SQL/JSON runtime needs a number, triggering SQLSTATE 22036.
Explicit casts like (jsonb_column ->> 'age')::int fail if the stored value is not purely digits.
Incorrect path filters such as $.items[?(@.price > 10)] cause the engine to compare non-numeric text to a number, producing the condition.
Validate that the JSON element is numeric before casting or filtering.
Use jsonb_typeof() or a JSON path type filter to guard the operation.
Coerce text to numeric safely with NULLIF and regular expressions, or clean the data upstream.
Replace invalid JSON values or default them to 0 to keep analytics queries running.
Path filter comparison: add a type filter like ?(@.price.type() == "number" && @.price > 10).
Bad cast in SELECT: wrap with CASE WHEN (jsonb_column ->> 'age') ~ '^\d+$' THEN ...
END.
Aggregations on mixed data: create a generated column storing a numeric copy and index it for faster querying.
Enforce JSON schema at ingestion time to keep numeric fields numeric.
Use generated computed columns to materialize numeric values and attach CHECK constraints.
Include defensive type checks in all ad-hoc queries or rely on Galaxy’s AI copilot to suggest safe casts.
invalid_json_text: raised when JSON input is malformed.
invalid_sql_json_number: occurs when a JSON number is out of range for the requested type.
jsonb_path_invalid_data_type: appears when a path step meets an unexpected data type.
.
No. SQL/JSON and the 22036 code were introduced in PostgreSQL 12.
No. The error is part of SQL/JSON standard compliance and cannot be turned off.
Yes. Use expression indexes on ((data ->> 'amount')::numeric) once the data is clean.
Galaxy’s editor warns when your query casts JSON text to numeric without a type guard, preventing deployment of broken SQL.
.avif)
.avif){kind=logo}
