PostgreSQL error code
PostgreSQL error 22038
22038 is the SQLSTATE PostgreSQL reports for singleton sql json item required, in class 22, Data Exception.
| SQLSTATE | 22038 |
|---|---|
| Condition name | singleton_sql_json_item_required |
| Class | 22 — Data Exception |
Reproduce it, then fix it
Arithmetic in a JSON path needs a single number on each side, and $.prices[*] is two. Pull the items out with the path, then do the maths in SQL. 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('{"prices": [10, 20]}', '$.prices[*] * 2');SELECT jsonb_path_query('{"prices": [10, 20]}', '$.prices[*]')::numeric * 2;