What I want to do is sum 29.0 and 34.65 and group by P_id
Table: transaction_items Column name: Debits, P_id Column data type: text, text Data:
[{"amount":29.0,"description":"Fee_Type_1"}
[{"amount":"34.65","description":"Fee_Type_1"}
16
16
I tried using the solution mentioned here [How to get elements from Json array in PostgreSQL
select transaction_line_items.P_id,
each_attribute ->> 'amount' Rev
from transaction_line_items
cross join json_array_elements(to_json(Debits)) each_section
cross join json_array_elements(each_section -> 'attributes') each_attribute
where (each_attribute -> 'amount') is not null;
However, I got an error saying "cannot deconstruct a scalar".
Can someone please let me know how to parse the values I am looking for?
Thank you.
It seems that your data is broken. The values of Debits
column are not valid json due to the lack of right square brackets. Assuming that your data should look like this:
[{"amount":29.0,"description":"Fee_Type_1"}]
[{"amount":"34.65","description":"Fee_Type_1"}]
the following query does what you want:
select p_id, sum(amount)
from (
select p_id, (elements->>'amount')::numeric amount
from transaction_items
cross join json_array_elements(debits::json) elements
) sub
group by p_id;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With