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](https://stackoverflow.com/q/38996212/1995738) — klin, Oct 28 '18 at 15:03
Possible duplicate of [postgresql 9.5 using jsonb\_set for updating specific jsonb array value](https://stackoverflow.com/questions/38996212/postgresql-9-5-using-jsonb-set-for-updating-specific-jsonb-array-value) — S-Man, Oct 30 '18 at 08:52

score 1 · Answer 1 · answered Apr 22 '19 at 06:12

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 '19 at 06:12

Garach Ankur

48
1
5

1

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. – Keivan Esbati Apr 22 '19 at 06:36

score 0 · Answer 2 · answered Oct 29 '18 at 02:30

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

Postgres: update specific json object in array

2 Answers2

Linked