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?

the link is useful, but did not show as a clickable link. I tried to edit this, and while editing the preview showed a clickable link, but not after saving the edit. Using Firefox. Feel free to reverse or improve my edit :-) — Roland Kwee
– Roland Kwee, Commented Jun 9, 2023 at 9:39

Ben Scheirman · Accepted Answer · 2020-02-18 20:22:35Z

23

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;

edited Feb 18, 2020 at 20:22

Ben Scheirman

41.1k21 gold badges104 silver badges139 bronze badges

answered Aug 7, 2015 at 22:30

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to extract json array elements in postgresql

Debits

P_id

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Debits

P_id

1 Answer 1

Comments

Linked

Related