Getting type cast issue when adding up the values from an array (array_agg)

user_1986039 0
Viewed 51

I was trying to sum up all the values from an array in PostgreSQL, tried different way but always getting the issue function sum(numeric[]) does not exist. Even I have tried with casting, but still that shows the same issue. Am I missing anything here?.

select array_to_json(array_agg(items)) FROM (SELECT SUM(p.total_price) FROM (SELECT 
ARRAY_AGG(cart_items.unit_price) FILTER (where cart_items.type = 2) AS total_price 
from cart inner join cart_items on cart_items.cart_id = cart.id where cart.id = 
40868884) AS p) AS items;

Result is:

                                          array_to_json                                          
-------------------------------------------------------------------------------------------------
[{"total_price":[1.867,1.867,1.867,1.867,1.867,1.21,1.867,1.867,1.56,1.867,1.867,1.867,44]}]

Basically, I want to sum up these values of the column prices after taking it from the database.

Fiddle

http://sqlfiddle.com/#!17/3fdd3/4

postgresql-11 postgresql
1 Answers

If what you want to do is sum up the prices in your json "totalprice" fields, then you can use:

select t.*,
       (select sum(price)
        from (select json_array_elements_text(j.el->'total_price')::numeric as price
              from json_array_elements(t.x) j(el)
             ) j
       )
from (select '[{"total_price":[1.867,1.867,1.867,1.867,1.867,1.21,1.867,1.867,1.56,1.867,1.867,1.867,44]}]'::json as x) t
user_11440350
Related