How to make the jsonb_set index position dynamic

Question

I have a simple json data and I want to add another key:value to it.

select elem, position 
from jsonb_all_testing,jsonb_array_elements(jsonb_col->'UserProfile') with ordinality arr(elem,position) 
 where col1=2

The output is

{"ProfileName": "WalletCustomer"}   1
{"UserBand": "MEDIUM", "ProfileName": "PMAdmin"}    2
{"ProfileName": "CorporateAdmin"}   3

Now I would like to add another KEY:VALUE to element 2. After the addition it should look like

{"UserBand": "MEDIUM", "ProfileName": "PMAdmin","OneMoreKey":"NewValue"}

but I want to pass the position "2" (actually 2-1) dynamically as I would not know where to add.

So I would like to be able to do something like this

with t as (select elem, position 
from jsonb_all_testing,jsonb_array_elements(jsonb_col->'UserProfile') with ordinality arr(elem,position) 
 where col1=2 and elem->>'ProfileName'='PMAdmin')
 update jsonb_all_testing
 set jsonb_col=jsonb_set(jsonb_col,'{UserProfile,**t.position-1,**""OneMoreKey"}','"NewValue"')
where col1=2;

But this is not working. I have set the array position explicitly as numeral 1.

How can I achieve this and set the value dynamically?

I think you should use the answer given here: [How do I modify fields inside the new PostgreSQL JSON datatype?](https://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype) — Luuk, Sep 19 '21 at 17:19

score 2 · Answer 1 · answered Sep 19 '21 at 18:08

2

Yes, of course that made-up syntax won't work. You have to SELECT from your CTE, you can't just refer to it as if it were a variable. Also, You should construct your array using the array constructor ARRAY[...], rather than as a monolithic string. You could assemble it into the string with concatenation, but that is harder to read and more error prone.

...
update jsonb_all_testing
 set jsonb_col=jsonb_set(jsonb_col,ARRAY['UserProfile',(SELECT position-1 FROM t)::text,'OneMoreKey'],'"NewValue"')
where col1=2;

answered Sep 19 '21 at 18:08

jjanes

37,812
5
27
34

Actually, the *** I used in the syntax was to emphasize. I didn't realize someone might construe that bad syntax. Sorry. Your tip helped. I understood that the issue I was facing was that was passing an integer. However it should be TEXT. thanks much. – tenet testuser1 Sep 28 '21 at 18:02

score 0 · Answer 2 · answered Sep 19 '21 at 18:31

Apart from the CTE with proper syntax as suggested by @jjanes, an alternative would be to do

UPDATE jsonb_all_testing
SET jsonb_col = jsonb_set(jsonb_col, '{UserProfile}', (
  SELECT jsonb_agg(
    CASE elem->>'ProfileName'
      WHEN 'PMAdmin' THEN elem || '{"OneMoreKey": "NewValue"}'
      ELSE elem
    END
  )
  FROM jsonb_array_elements(jsonb_col->'UserProfile')
)
WHERE col1=2;

How to make the jsonb_set index position dynamic

2 Answers2

Linked