Postgres: update specific json object in array

Question

having some troubles with POSTGRES queries.

I have a column jsonb called venue_menu which has an array of objects that looks like this:

[
    { "menu_id":"0", "menu_name":"name 1"},
    { "menu_id":"1", "menu_name":"name 2"},
    { "menu_id":"2", "menu_name":"name 3"}
]

I want to make an update, for instance at the object where menu_id is 2 for a particular row which is selected by the column client_id (the WHERE clause). I have the following query so far

UPDATE client SET venue_menu = jsonb_set(venue_menu, '{}', { "menu_id":"2", "menu_name":"name updated"}) WHERE client_id = "1";

I can't seem to figure out where to do the query that specifies the key name of the object I want to update, any ideas?

Thank you.

See postgresql 9.5 using jsonb_set for updating specific jsonb array value — klin
– klin, Commented Oct 28, 2018 at 15:03
Possible duplicate of postgresql 9.5 using jsonb_set for updating specific jsonb array value — S-Man
– S-Man, Commented Oct 30, 2018 at 8:52

Garach Ankur · Accepted Answer · 2019-04-22 06:12:41Z

1

Use jsonb_set

update "t" set "col"= jsonb_set("cols":: JSONB,('{'|| elem_index || ',"' ||'menu_name"}')::text[],'"name updated"'::jsonb,false)
from (select pos- 1 as elem_index,"col" as "cols","client_id" as "colId"
from  "t",jsonb_array_elements("t"."col" :: JSONB) with ordinality arr(elem, pos) where elem->>'menu_id' = '2') as "tble"     
where  client_id =1

DEMO

answered Apr 22, 2019 at 6:12

Garach Ankur

581 silver badge5 bronze badges

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

1 Comment

Keivan Esbati Over a year ago

A short description about the code snippet will improve the answer a lot. Please consider adding a description about what is the code and what it does.

Emilio Platzer · Accepted Answer · 2018-10-29 02:30:21Z

0

You can use:

jsonb_to_recordset to split the array into rows
jsonb_build_object to build each element using the splited rows
jsonb_agg to join each element into a new array

Here is the example:

select jsonb_agg(jsonb_build_object(
    'menu_id', menu_id, 
    'menu_name', case when menu_id='2' then 'name changed' else menu_name end
  ))
  from jsonb_to_recordset('[
    { "menu_id":"0", "menu_name":"name 1"},
    { "menu_id":"1", "menu_name":"name 2"},
    { "menu_id":"2", "menu_name":"name 3"}
]') as menu_t(menu_id text, menu_name text);

You can see a running example at: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=147c0de2abd5d0df786ed636793f1cc8

answered Oct 29, 2018 at 2:30

Emilio Platzer

2,5191 gold badge23 silver badges33 bronze badges

Collectives™ on Stack Overflow

Postgres: update specific json object in array

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related