How to fix SQL/JSON number not found in PostgreSQL

-- 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);

-- 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');

-- 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);

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

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