2

I need to update a jsonb column which is called "verticals" and the array of values it holds are like HOM, BFB etc. There are no keys in the array.

Table: Product(verticals jsonb, code int)

sample value stored in "verticals" column is

[HOM,rst,NLF,WELSAK,HTL,TRV,EVCU,GRT]

I need to update the value 'HOM' to 'XXX' in the column "verticals" where code =1

My expected output is 

[XXX,rst,NLF,WELSAK,HTL,TRV,EVCU,GRT]
DDD
  • 45
  • 1
  • 6
  • Please **[edit]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](https://meta.stackoverflow.com/a/251362) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments) –  Sep 19 '18 at 07:34
  • 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) – klin Sep 19 '18 at 08:00
  • http://blog.2ndquadrant.com/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/ –  Sep 19 '18 at 08:03
  • Is the order of the elements important? –  Sep 19 '18 at 08:14

2 Answers2

0

You should use jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]) and array_position() OR array_replace(anyarray, anyelement, anyelement)

https://www.postgresql.org/docs/9.5/static/functions-json.html https://www.postgresql.org/docs/10/static/functions-array.html

Sheruan Bashar
  • 506
  • 4
  • 7
0

Because you chose to store your data in a de-normalized way, updating it is more complicated then it has to be.

You need to first unnest the array (essentially normalizing the data), replace the values, then aggregate them back and update the column:

update product p
  set verticals = t.verticals
from (
  select jsonb_agg(case when x.v = 'HOM' then 'XXX' else x.v end order by idx) as verticals
  from product p2, jsonb_array_elements_text(p2.verticals) with ordinality as x(v,idx)
  where code = 1
) t
where p.code = t.code;

This assumes that product.code is a primary (or unique) key!

Online example: http://rextester.com/KZQ65481

If the order of the array elements is not important, this gets easier:

update product
   set verticals = (verticals - 'HOM')||'["XXX"]'
where code = 1;

This removes the element 'HOM' from the array (regardless of the posisition) and then appends 'XXX' to the end of the array.