Elliot Chance Elliot Chance - 5 months ago 34
SQL Question

PostgreSQL: SUM of JSON nested elements?

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
?

Answer

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.

Comments