Migrate from an array of strings to an array of objects (JSONB)

Question

I have an array of strings in a jsonb column in a postgres DB, which I'd like to migrate to an array of objects with 2 fields. So, turn this:

"Umbrella": [
    "green|bubbles",
    "blue|clouds"
  ],

into this:

"items": {
    "umbrella": [
      {
        "color": "green",
        "pattern": "bubbles"
      },
      {
        "color": "blue",
        "pattern": "clouds"
      }
    ]
  }

I managed to migrate the first value of the array, but I don't know how to implement a "foreach" to do this for all items.

What I tried (public.metadata is the table and metadata is the jsonb column):

update public.metadata set metadata = jsonb_set(metadata, '{items}', '{}');

update public.metadata set metadata = jsonb_set(metadata, '{items, umbrella}', '[]');

update public.metadata set metadata = jsonb_insert(metadata, '{items, umbrella, 0}', '{"color":"1", "pattern":"2"}');

update public.metadata
set metadata = jsonb_set(
        metadata, 
        '{items, umbrella, 0, color}', 
        to_jsonb(split_part(metadata -> 'Umbrella' ->> 0, '|', 1))
        );
update public.metadata
set metadata = jsonb_set(
        metadata, 
        '{items, umbrella, 0, pattern}', 
        to_jsonb(split_part(metadata -> 'Umbrella' ->> 0, '|', 2))
        );

I thought maybe this could lead me to the final solution, but I'm stuck.

Answer 1

I manage to solve it like this:

-- from array of strings to array of objects
update public.metadata set metadata = jsonb_set(metadata #- '{Umbrella}', '{items, umbrella}',
       (select jsonb_agg(
          jsonb_build_object(
            'color', (split_part(x::text, '|', 1)),
            'pattern', (split_part(x::text, '|', 2))
          )
        ) from public.metadata, jsonb_array_elements_text(metadata.metadata->'Umbrella') x)
) where metadata->'Umbrella' is not null and metadata -> 'Umbrella' != '[]'::jsonb;;