Change value from array to string in jsonb

Question

there is such a jsonb:

{
  "name": "Somedata",
  "attr": [
    {
      "type": "string",
      "otherdata": null,
      "info": "4cf1c0de-4ea5-439e-82be-efcf22b5c401",
    },
    {
      "type": "date",
      "otherdata": null,
      "info": [
      "eaffa971-ee96-4944-8145-4c5defa3cb2c",
      ]
    },
    {
      "type": "date",
      "otherdata": null,
      "info": "7c15ffcd-2011-4d73-8d05-65c70dab3302",
    }
  ]
}

need to search for all entries where the "attr" key exists and find all "info" keys that have an array value, and change the value from the array to a string.

Expected Result:

{
  "name": "Somedata",
  "attr": [
    {
      "type": "string",
      "otherdata": null,
      "info": "4cf1c0de-4ea5-439e-82be-efcf22b5c401",
    },
    {
      "type": "date",
      "otherdata": null,
      "info": "eaffa971-ee96-4944-8145-4c5defa3cb2c",
    },
    {
      "type": "date",
      "otherdata": null,
      "info": "7c15ffcd-2011-4d73-8d05-65c70dab3302",
    }
  ]
}

My manipulations with the jsonb_array_elements, jsonb_set did not lead to anything..

used PostgreSQL 12.3 and 13.4

Answer 1

Demo

select
  t.id,
  t.data || jsonb_build_object(
    'attr',
    jsonb_agg(
      case 
        when jsonb_typeof(e.value -> 'info') = 'array' then jsonb_set(e.value, '{info}',  coalesce(e.value -> 'info' -> 0, 'null'))
        else e.value
      end
    )
  )
from
  test t
  cross join jsonb_array_elements(t.data -> 'attr') e
where
  t.data ? 'attr'
group by t.id, t.data

---

P.S:

If you need to update record you can use below query:

update test u_t
set data = tmp.change_data
from (
  select
    t.id,
    t.data || jsonb_build_object(
      'attr',
      jsonb_agg(
        case 
          when jsonb_typeof(e.value -> 'info') = 'array' then jsonb_set(e.value, '{info}',  coalesce(e.value -> 'info' -> 0, 'null'))
          else e.value
        end
      )
    ) as change_data
  from
    test t
    cross join jsonb_array_elements(t.data -> 'attr') e
  where
    t.data ? 'attr'
  group by t.id, t.data
) tmp
where
  u_t.id = tmp.id;