how to modify an array jsonb in postgresql?

Question

In postgresql,I have a table which defined like this:

create table carts(
    id serial,
    cart json
)

has data like this:

id     cart
3      [{"productid":5,"cnt":6},{"productid":8,"cnt":1}]
5      [{"productid":2},{"productid":7,"cnt":1},{"productid":34,"cnt":3}]

if i want to modify the data "cnt", with id=n and productid=m, how can I do this? for example, when id=3,and productid=8, i want to change the cnt to cnt+3, how to realize it?

Maybe [this](http://stackoverflow.com/questions/26703476/how-to-perform-update-operations-on-columns-of-type-jsonb-in-postgres-9-4) will help. — Tim Biegeleisen, Aug 11 '16 at 01:24
I think Postgres has limited ability to update the JSON in place. Instead, you might have to extract the JSON, modify it, and then update the entire value. — Tim Biegeleisen, Aug 11 '16 at 04:11

Paarth · Answer 1 · 2016-08-11T05:59:41.527

0

Try This, here we will use jsonb_set method

jsonb_set(target jsonb, path text[], new_value jsonb) which will return jsonb object

 update carts
        set cart = (
    (select json_agg(val)from 
    (SELECT 
    CASE
    WHEN value->>'productid'='8' THEN jsonb_set(value::jsonb, '{cnt}', (((value->>'cnt')::int)+3)::text::jsonb)::json --again convert to json object
    ELSE value END val
      FROM carts,json_array_elements(cart) where id=3)) 
where id=3;

Hope it works for you

EDIT: you can generalized this by creating a function with id and productid as input parameter.

edited Aug 11 '16 at 05:59

answered Aug 11 '16 at 05:43

Paarth

580
3
10

You can refer [Json in PostgrSql](https://www.postgresql.org/docs/9.5/static/functions-json.html) for more details – Paarth Aug 11 '16 at 06:03

how to modify an array jsonb in postgresql?

1 Answers1