CODEWITHSUNDEEP
arrayspostgresqlsql-updatejsonb
user5084949
user5084949

Reputation: 307

update value in list Postgres jsonb

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

Upvotes: 0

Views: 818

Answers (1)

S-Man
S-Man

Reputation: 23676

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;
  1. Expand array elements into one row each
  2. If element has relevant id, update with || operator; if not, keep the original one
  3. Reaggregate the array after updating the JSON data
  4. Execute the UPDATE statement.

Upvotes: 1

Related Questions