postgres how to add a key to dicts in a jsonb array

Question

I have an array of dicts in a jsonb column. I have to update and add a key to all the dicts in this array. Can this be done in a single update statement?

Jsonb column:

select '[{"a":"val1"}, {"b":"val2"}, {"c":"val3"}]'::jsonb;

How do I update it to:

[
    {
        "a": "val1",
        "x": "xval1"
    },
    {
        "b": "val2",
        "x": "xval2"
    },
    {
        "c": "val3",
        "x": "xval3"
    }
]

Answer 1

Firstly jsonb_array_elements_text() function might be used to unnest the elements of jsonb data, and then regexp_replace() might be applied to get new jsonb objects with common keys("x") within the subquery.

In the next step, replace() function together with jsonb_agg() would yield the desired result as in the following query :

select id,
       jsonb_agg(
                 (replace(jj.value,'}',',')||replace(jsonb_set(value2::jsonb, '{x}',
                 ('"x'||(jj.value2::jsonb->>'x')::text||'"')::jsonb)::text,'{',''))::jsonb
       ) 
    as result
  from
  (
   select t.id, j.value, regexp_replace(j.value,'[[:alpha:]]+','x') as value2
     from t
    cross join jsonb_array_elements_text(jsdata) j
  ) jj
 group by id;

Demo

Indeed, using '[[:alpha:]]' pattern for regexp_replace is enough, the plus sign is added for the cases of the data would have key values with more than one letter.