Update a json item with another in postgres query

Question

I am using the latest postgres 9.3, which has fancy support for json and also querying for a particular key inside a json field. But I am facing problems while updating a json field.

Basically, my json column is a dictionary which contains frequency of some fields, now since the fields are dynamic, I have put them in a json struct. Now I am facing trouble in an update query, where I just want to add the frequencies of corresponding fields of that json dict.

So, if I have sth like this - {"a":10, "b":2}, and while updating the incoming item is {"a":2,"c":3}, my new row should have {"a":12, "b":2, "c":3}

Appreciate your help.

roman · Accepted Answer · 2013-10-24 11:18:29Z

1

You can do something like this in PostgreSQL:

update Test1 as t set
    data = (
        select
        ('{' || string_agg(a.data, ',') || '}')::json
        from (
            select '"' || a.key || '" :' || sum(a.value::int)::text as data
            from (
                select * from json_each_text(t.data)
                union all
                select * from json_each_text('{"a":2,"c":3}'::json) -- incoming
            ) as a
            group by a.key
       ) as a
   )

But may be easier way would be to write Python (or other PL) function.

answered Oct 24, 2013 at 11:18

roman

118k30 gold badges205 silver badges209 bronze badges

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

1 Comment

Agniva De Sarker Over a year ago

Thanks for taking the time ! I think I prefer the pl/python approach. Much cleaner and easier to maintain.

Collectives™ on Stack Overflow

Update a json item with another in postgres query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related