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

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

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.

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

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.

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

-- 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.

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

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.

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

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:

SELECT 
  COALESCE(
    (data->>'score')::numeric,
    0
  ) AS score
FROM table_name
WHERE data->>'score' ~ '^\d+(\.\d+)?$';

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

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.