PostgreSQL error code
PostgreSQL error 22036
22036 is the SQLSTATE PostgreSQL reports for non numeric sql json item, in class 22, Data Exception.
| SQLSTATE | 22036 |
|---|---|
| Condition name | non_numeric_sql_json_item |
| Class | 22 — Data Exception |
Reproduce it, then fix it
.double() needs something that reads as a number, and "n/a" doesn’t. Filter the items first so only numeric-looking strings reach the conversion. Run the broken query to see the error, then switch to the fixed one. Edit either freely; every run is rolled back.
SELECT jsonb_path_query('{"price": "n/a"}', '$.price.double()');SELECT jsonb_path_query('{"price": "n/a"}', '$.price ? (@ like_regex "^[0-9.]+$").double()');