update value in list Postgres jsonb

Question

I am trying to update json

[{"id": "1", "name": "myconf", "icons": "small", "theme": "light", "textsize": "large"},
 {"id": 2, "name": "myconf2", "theme": "dark"}, {"name": "firstconf", "theme": "dark", "textsize": "large"},
 {"id": 3, "name": "firstconxsf", "theme": "dassrk", "textsize": "lassrge"}]

and this is the table containing that json column :

CREATE TABLE USER_CONFIGURATIONS ( ID BIGSERIAL PRIMARY KEY, DATA JSONB );

adding new field is easy I am using:

UPDATE USER_CONFIGURATIONS
SET DATA = DATA || '{"name":"firstconxsf", "theme":"dassrk", "textsize":"lassrge"}'
WHERE id = 9;

But how to update single with where id = 1 or 2

CREATE TABLE USER_CONFIGURATIONS ( ID BIGSERIAL PRIMARY KEY, DATA JSONB ); — user5084949
– user5084949, Commented Nov 18, 2019 at 10:10
i am following idalko.com/crud-operations-postgres-jsonb this is easy when you are trying to add with one key with list. — user5084949
– user5084949, Commented Nov 18, 2019 at 10:10

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Click: step-by-step demo:db<>fiddle

UPDATE users                                                   -- 4
SET data = s.updated
FROM (
    SELECT
        jsonb_agg(                                             -- 3
            CASE                                               -- 2
                WHEN ((elem ->> 'id')::int IN (1,2)) THEN
                    elem || '{"name":"abc", "icon":"HUGE"}'
                ELSE elem
            END
        ) AS updated
    FROM
        users,
        jsonb_array_elements(data) elem                        -- 1
) s;

Expand array elements into one row each
If element has relevant id, update with || operator; if not, keep the original one
Reaggregate the array after updating the JSON data
Execute the UPDATE statement.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 18, 2019 at 12:05

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

5 Comments

user5084949 Over a year ago

is that possible we can put condition in to check where id = 321 on table id

S-Man Over a year ago

You simply can add a WHERE clause:dbfiddle.uk/…

user5084949 Over a year ago

dbfiddle.uk/… its adding one more value with same id and updating existing.

S-Man Over a year ago

Currently all expanded values of all array rows will be aggregated. For this, of course, you need a grouping: dbfiddle.uk/…

S-Man Over a year ago

Did this help? Please do not forget to upvote ALL helpful answers (which honors the work and time the repliers invested into your question) and accept the best fitting answer (to close the question). Otherwise please tell us, what to change.

Collectives™ on Stack Overflow

update value in list Postgres jsonb

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related