How to extract json array elements in postgresql

Question

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:

---

Debits

[{"amount":29.0,"description":"Fee_Type_1"}
[{"amount":"34.65","description":"Fee_Type_1"}

---

P_id

16
16

---

I tried using the solution mentioned here

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?

Answer 1

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;