I have a record that looks like this:
{
"id": 93824701,
"lines": [
{"number": 1, "unit_price": 9.77},
{"number": 2, "unit_price": 8.36}
]
}
From the query:
select * from sales where sale @> '{"id": 93824701}'::jsonb;
How do I calculate the sum of all the unit_prices elements (in SQL), so that the result would be 18.13?
JSON is not a part of the SQL and PostgreSQL just provides some tasty bonuses for it. Storing structured data in the plain tables instead of JSON structures you can avoid many problems in the future.
Answering to the question, hope this little example will help:
select sum((value->'unit_price')::text::numeric) from jsonb_array_elements('{
"id": 93824701,
"lines": [
{"number": 1, "unit_price": 9.77},
{"number": 2, "unit_price": 8.36}
]
}'::jsonb->'lines');
Good luck.
--> and provided type cast just for educational purpose. real may cause well-known calculation issues, numeric is the default type for money.