up vote 0 down vote favorite

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.

share|improve this question
up vote 2 down vote accepted

It seems that your data is broken. The values of Debtits 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;
share|improve this answer
    
Thank you. Thats awesome. – user1940212 Aug 7 '15 at 23:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.