How to fix Non-numeric SQL/JSON item in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 132 views

This error occurs when performing numeric operations on JSON values that are not actually numeric types. Fix by validating JSON types before operations, using error-suppressing operators, or specifying proper error handling modes.

Quick fix

Fixes Non-numeric SQL/JSON item

SELECT jsonb_typeof(data->>'score') FROM table_name LIMIT 5;
  1. Check JSON data types before numeric operations
  2. Use lax mode in SQL/JSON path expressions
  3. Use error-suppressing operators
  4. Add silent argument to error-handling functions
postgresql — error
SELECT jsonb_typeof(data->>'score') FROM table_name LIMIT 5;
Non-numeric SQL/JSON item

What this error means

PostgreSQL throws the "Non-numeric SQL/JSON item" error when you attempt to perform a numeric operation on a JSON/JSONB value that contains a non-numeric type such as a string, object, or array. This commonly happens when using SQL/JSON path expressions with arithmetic operators, casting JSON values to numeric types, or using numeric functions on JSON data without first validating the data type. The error prevents type mismatches that could corrupt calculations or produce unexpected results.

Typical symptoms

  • SQL/JSON queries with numeric operations fail unexpectedly
  • Casting jsonb values to numeric type throws errors
  • jsonpath expressions with arithmetic operators fail on certain rows
  • json_value() or other JSON functions fail when encountering string values in numeric contexts
  • Queries that worked on some JSON documents fail on others with different structures

Common causes

  1. JSON document contains a string value where numeric operation expects a number
  2. Attempting to cast non-numeric JSON content (such as "abc" or "n/a") to numeric type
  3. Using arithmetic operators in strict jsonpath mode on non-numeric JSON items
  4. JSON data structure varies across documents and not all have numeric values in expected fields
  5. Missing validation or type checking before performing numeric operations on JSON

How to fix "Non-numeric SQL/JSON item"

1Check JSON data types before numeric operations

First, inspect your JSON data to confirm it contains numeric values where you expect them. Use jsonb_typeof() to check the JSON type:

sql
SELECT jsonb_typeof(data->>'score') FROM table_name LIMIT 5;

If the result is "string" instead of "number", your JSON contains string values that cannot be used in numeric operations directly.

2Use lax mode in SQL/JSON path expressions

Use lax mode (the default) in jsonpath expressions to automatically suppress numeric errors:

sql
SELECT jsonb_path_query_array(
  data,
  'lax $.items[*].score'
) FROM table_name;

Lax mode implicitly adapts the queried data and suppresses non-matching structural errors, returning NULL or empty sequences instead of throwing an error.

3Use error-suppressing operators

The @? and @@ operators automatically suppress numeric errors:

sql
-- Check if a path exists without numeric errors
SELECT * FROM table_name
WHERE data @? '$.score > 50';

-- Return matching items while suppressing errors
SELECT jsonb_path_query_array(data, '$.items[*] ? (@ > 50)') FROM table_name;

These operators silently ignore numeric type mismatches instead of raising errors.

4Add silent argument to error-handling functions

For functions that support it, use the silent argument set to true:

sql
SELECT json_query(
  data,
  '$.score',
  RETURNING numeric,
  SILENT
) FROM table_name;

This tells PostgreSQL to suppress numeric errors and return NULL instead of throwing an error when encountering non-numeric JSON items.

5Validate and convert JSON values explicitly

Before performing numeric operations, validate and convert JSON values safely using CASE statements:

sql
SELECT 
  CASE 
    WHEN jsonb_typeof(data->>'score') = 'number' 
    THEN (data->>'score')::numeric
    ELSE NULL
  END AS score_numeric
FROM table_name;

Alternatively, use jsonb_extract_path_text() with type validation:

sql
SELECT 
  COALESCE(
    (data->>'score')::numeric,
    0
  ) AS score
FROM table_name
WHERE data->>'score' ~ '^\d+(\.\d+)?$';
6Use strict mode only when necessary

If you explicitly need strict mode to catch type errors during development, use it but handle exceptions:

sql
BEGIN
  SELECT jsonb_path_query_array(
    data,
    'strict $.score + 10'
  ) FROM table_name;
EXCEPTION WHEN others THEN
  RAISE NOTICE 'Non-numeric JSON item encountered: %', SQLERRM;
END;

Strict mode will raise errors for structural issues, but this is typically only useful during data validation and debugging, not in production queries.

Advanced notes

JSON path mode defaults to lax, which is lenient with non-numeric values and is the safest choice for production queries against varying JSON structures. Strict mode raises errors on structural mismatches and is better suited for data validation scripts. When casting JSON to numeric types directly (e.g., (data->'value')::numeric), PostgreSQL will throw an error if the value is a non-numeric JSON type; use conditional logic or the validation approach in Step 5 to handle this gracefully. In PostgreSQL 14+, the json_query() and json_value() functions provide more sophisticated error handling with the RETURNING and SILENT clauses. For complex JSON transformations, consider normalizing and validating your JSON schema at insert time rather than trying to handle all type variations at query time.

Frequently asked questions

What does "Non-numeric SQL/JSON item" mean?
PostgreSQL throws the "Non-numeric SQL/JSON item" error when you attempt to perform a numeric operation on a JSON/JSONB value that contains a non-numeric type such as a string, object, or array. This commonly happens when using SQL/JSON path expressions with arithmetic operators, casting JSON values to numeric types, or using numeric functions on JSON data without first validating the data type. The error prevents type mismatches that could corrupt calculations or produce unexpected results.
How do I fix "Non-numeric SQL/JSON item"?
This error occurs when performing numeric operations on JSON values that are not actually numeric types. Fix by validating JSON types before operations, using error-suppressing operators, or specifying proper error handling modes.
What causes "Non-numeric SQL/JSON item"?
JSON document contains a string value where numeric operation expects a number Attempting to cast non-numeric JSON content (such as "abc" or "n/a") to numeric type Using arithmetic operators in strict jsonpath mode on non-numeric JSON items
Is the "Non-numeric SQL/JSON item" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading