1

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.

askedOct 28, 2018 at 14:53
user3353167's user avatar
2

2 Answers2

1

Usejsonb_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

answeredApr 22, 2019 at 6:12
Garach Ankur's user avatar
Sign up to request clarification or add additional context in comments.

1 Comment

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.
0

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

answeredOct 29, 2018 at 2:30
Emilio Platzer's user avatar

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.