CODEWITHSUNDEEP
postgresql
user3353167
user3353167

Reputation: 892

Postgres: update specific json object in array

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.

Upvotes: 1

Views: 2403

Answers (2)

Garach Ankur
Garach Ankur

Reputation: 58

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

Upvotes: 1

Emilio Platzer
Emilio Platzer

Reputation: 2469

You can use:

  1. jsonb_to_recordset to split the array into rows
  2. jsonb_build_object to build each element using the splited rows
  3. 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

Upvotes: 0

Related Questions