How to diagnose and fix the 22036 non_numeric_sql_json_item error code in Postgres.

by

The 22036 error code in PostgreSQL, represented by non_numeric_sql_json_item, indicates that a non-numeric JSON item was encountered where a numeric one was expected. This error typically occurs when you’re performing operations that require numeric JSON values, such as arithmetic operations or functions that expect numbers, but the JSON data includes non-numeric values.

Here are a few examples of how this error might occur and how to diagnose and fix it:

  1. Arithmetic Operations on Non-numeric JSON Values:
    When performing arithmetic operations on JSON values, ensure that the data referenced is numeric. Example of an incorrect operation on a non-numeric JSON value:
   SELECT ('{"value": "ten"}'::jsonb ->> 'value')::numeric + 5;

To fix this, make sure the JSON value is numeric:

   SELECT ('{"value": 10}'::jsonb ->> 'value')::numeric + 5;
  1. Using Numeric Functions with JSON Data:
    When using functions that expect numeric arguments with JSON data, ensure that the JSON value is numeric. Example of a function encountering a non-numeric JSON value:
   SELECT jsonb_extract_path_text('{"number": "five"}'::jsonb, 'number')::integer;

Correct usage with a numeric JSON value:

   SELECT jsonb_extract_path_text('{"number": 5}'::jsonb, 'number')::integer;
  1. Aggregating JSON Data:
    If you’re aggregating JSON data that is supposed to be numeric, such as summing values, verify that all items are numeric. Example of attempting to sum non-numeric JSON values:
   SELECT SUM((json_array_elements_text('["1", "2", "three"]'::json))::numeric) AS total;

To fix this, ensure all elements are numeric before summing:

   SELECT SUM((json_array_elements_text('["1", "2", "3"]'::json))::numeric) AS total;
  1. Filtering for Numeric JSON Values:
    When filtering or searching for numeric values in JSON data, ensure that the values you’re working with are indeed numeric. Example of a query expecting numeric JSON values:
   SELECT *
   FROM your_table
   WHERE (jsonb_column ->> 'numeric_key')::numeric > 100;

If jsonb_column ->> 'numeric_key' yields a non-numeric value, you’ll encounter the 22036 error.

To diagnose the 22036 error, inspect the JSON data to ensure that any values you’re treating as numeric are actually numeric. Correct any non-numeric values that are causing the error, or adjust your queries and operations to handle non-numeric values appropriately.

The search results provided do not contain specific information about resolving the 22036 error code in PostgreSQL, but the general guidance and examples above should help you address issues related to non-numeric SQL/JSON items in PostgreSQL.

Leave a Comment