How to extract json array elements in postgresql
20,980
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;
Related videos on Youtube
Author by
user1940212
Updated on July 09, 2022Comments
-
user1940212 almost 2 years
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 [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.