How to fix SQL/JSON item cannot be cast to target type in PostgreSQL

SELECT JSON_VALUE(data, '$.price' RETURNING numeric DEFAULT NULL ON ERROR)
FROM products;

SELECT JSON_VALUE(data, '$.price' RETURNING numeric ERROR ON ERROR)
FROM products;

-- Don't do this:
SELECT (data -> 'count')::numeric FROM items;  -- Fails if count is a string

-- Do this instead:
SELECT (data ->> 'count')::text::numeric FROM items;
-- Or with JSON functions:
SELECT CAST(JSON_VALUE(data, '$.count' RETURNING text) AS numeric) FROM items;

-- Check if value exists and is the right type
SELECT 
  CASE 
    WHEN JSON_EXISTS(data, '$.price' ? @ > 0) THEN 
      JSON_VALUE(data, '$.price' RETURNING numeric)
    ELSE 
      NULL
  END AS price
FROM products;

-- For JSON numeric values:
SELECT JSON_VALUE(data, '$.count' RETURNING bigint) FROM items;

-- For JSON string values:
SELECT JSON_VALUE(data, '$.name' RETURNING text) FROM items;

-- For JSON boolean values:
SELECT JSON_VALUE(data, '$.is_active' RETURNING boolean) FROM items;

-- With ON ERROR (returns NULL by default):
SELECT COALESCE(
  JSON_VALUE(data, '$.age' RETURNING integer),
  0  -- Default value if JSON value is null or casting fails
) FROM users;

-- Or use DEFAULT in ON EMPTY clause:
SELECT JSON_VALUE(
  data, 
  '$.age' 
  RETURNING integer 
  NULL ON EMPTY 
  NULL ON ERROR
) FROM users;