How to fix SQL/JSON number not found in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 27, 2025· 122 views

This error occurs when a JSON_VALUE() or jsonb_path_query() call in strict mode tries to extract a numeric value from a JSON path that doesn't contain a number or doesn't exist. Use ON ERROR/ON EMPTY clauses or switch to lax mode to handle missing numeric values.

Quick fix

Fixes SQL/JSON number not found

-- Before (fails if no number found)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric);

-- After (returns NULL if no number found)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric NULL ON ERROR);

-- With ON EMPTY clause (handles both missing and non-numeric)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric NULL ON EMPTY NULL ON ERROR);
  1. Add ON ERROR NULL to handle missing or invalid numeric values
  2. Switch to lax mode or verify your JSON path expression
  3. Use the silent parameter with jsonb_path_query functions
  4. Validate and normalize your JSON data structure
postgresql — error
-- Before (fails if no number found)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric);
-- After (returns NULL if no number found)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric NULL ON ERROR);
-- With ON EMPTY clause (handles both missing and non-numeric)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric NULL ON EMPTY NULL ON ERROR);
SQL/JSON number not found

What this error means

PostgreSQL's SQL/JSON functions (JSON_VALUE, JSON_QUERY, jsonb_path_query) can operate in two modes: lax (default) and strict. In strict mode, the database enforces strict structural rules. When you use a JSON path expression that tries to access a numeric value but the path doesn't exist, the value is not a number, or the array/object structure is invalid, PostgreSQL raises an error. This error specifically indicates that a numeric extraction operation failed because no number was found at the specified JSON path. The error originates from the SQL/JSON path language implementation in PostgreSQL 12+ and becomes more common with PostgreSQL 17's new JSON_VALUE(), JSON_TABLE(), and enhanced JSON constructors.

Typical symptoms

  • Queries using JSON_VALUE() with numeric return type fail with "SQL/JSON number not found"
  • jsonb_path_query() in strict mode raises this error when accessing numeric paths
  • The error occurs inconsistently when JSON structure changes across rows
  • Code works in development but fails in production with different JSON payloads
  • ON ERROR and ON EMPTY clauses are missing from your SQL/JSON function calls

Common causes

  1. Using strict mode without proper error handling when a JSON path may not contain a number
  2. JSON data structure doesn't match your path expression (wrong key name, missing field)
  3. Attempting to extract a number from a JSON value that is a string, array, or object instead of a scalar
  4. The JSON path expression is syntactically valid but semantically points to non-numeric data
  5. Mixing lax and strict mode expectations without understanding the differences
  6. Using .type() or .size() methods in strict mode on paths that don't match expected structures

How to fix "SQL/JSON number not found"

1Add ON ERROR NULL to handle missing or invalid numeric values

Modify your SQL/JSON function to gracefully handle cases where a number isn't found:

sql
-- Before (fails if no number found)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric);

-- After (returns NULL if no number found)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric NULL ON ERROR);

-- With ON EMPTY clause (handles both missing and non-numeric)
SELECT JSON_VALUE(data, '$.price' RETURNING numeric NULL ON EMPTY NULL ON ERROR);
2Switch to lax mode or verify your JSON path expression

Use lax mode (default) instead of strict mode for more forgiving path evaluation:

sql
-- Lax mode (default): returns NULL/empty result if path doesn't match
SELECT jsonb_path_query(data, 'lax $.price');

-- Strict mode: raises error if path doesn't match
SELECT jsonb_path_query(data, 'strict $.price');

Or verify that your JSON path expression matches your actual data structure:

sql
-- Check the JSON structure first
SELECT data->'price', jsonb_typeof(data->'price') FROM my_table LIMIT 5;

-- Adjust path if needed
SELECT JSON_VALUE(data, '$.invoice.total' RETURNING numeric NULL ON ERROR);
3Use the silent parameter with jsonb_path_query functions

When using jsonb_path_query, jsonb_path_exists, or jsonb_path_match, add the silent parameter to suppress numeric errors:

sql
-- With silent=true, suppresses numeric errors
SELECT jsonb_path_query(data, '$.price', vars => 'null'::jsonb, silent => true);

-- Alternatively, cast to text first if the value might not be numeric
SELECT jsonb_path_query_first(data, '$.price') AS raw_value;
4Validate and normalize your JSON data structure

Ensure all JSON documents follow a consistent structure, or use conditional logic in your path:

sql
-- Use COALESCE with multiple possible paths
SELECT COALESCE(
  JSON_VALUE(data, '$.price' RETURNING numeric),
  JSON_VALUE(data, '$.amount' RETURNING numeric),
  0
) AS amount;

-- Or use CASE for complex logic
SELECT CASE
  WHEN jsonb_typeof(data->'price') = 'number' THEN (data->'price')::numeric
  ELSE NULL
END AS price;

Advanced notes

The "SQL/JSON number not found" error is part of PostgreSQL's SQL/JSON standard implementation introduced in PostgreSQL 12. In PostgreSQL 17+, this is more common because JSON_VALUE(), JSON_TABLE(), JSON_SERIALIZE(), and enhanced JSON/JSON_SCALAR constructors are now available with strict type coercion. If you're using prepared statements or ORMs, ensure they don't accidentally switch to strict mode. For jsonpath expressions, understand that .type() returns a string representation of the JSON type, and .size() only works on arrays—using these on non-matching paths in strict mode will also trigger similar errors. Consider using jsonb operators like -> and ->> for simpler cases, and reserve SQL/JSON functions for complex path queries where their error handling capabilities justify the overhead.

Frequently asked questions

What does "SQL/JSON number not found" mean?
PostgreSQL's SQL/JSON functions (JSON_VALUE, JSON_QUERY, jsonb_path_query) can operate in two modes: lax (default) and strict. In strict mode, the database enforces strict structural rules. When you use a JSON path expression that tries to access a numeric value but the path doesn't exist, the value is not a number, or the array/object structure is invalid, PostgreSQL raises an error.
How do I fix "SQL/JSON number not found"?
This error occurs when a JSON_VALUE() or jsonb_path_query() call in strict mode tries to extract a numeric value from a JSON path that doesn't contain a number or doesn't exist. Use ON ERROR/ON EMPTY clauses or switch to lax mode to handle missing numeric values.
What causes "SQL/JSON number not found"?
Using strict mode without proper error handling when a JSON path may not contain a number JSON data structure doesn't match your path expression (wrong key name, missing field) Attempting to extract a number from a JSON value that is a string, array, or object instead of a scalar
Is the "SQL/JSON number not found" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading