Postgresql convert array of objects to map of objects

Question

I have a table in the data base which has a column texts of type jsonb which looks like:

{
  "texts": [
    {
      "locale": "ar",
      "textDictionary": {
        "key1": "arabic1",
        "key2": "arabic2"
      }
    },
    {
      "locale": "en",
      "textDictionary": {
        "key1": "english1",
        "key2": "english2"
      }
    }
  ]
}

Given the structure above, the goal is to convert this column to use a map of locales to dictionary instead of an array of locales, this is in order to support indexing the tsvector by locale in a more decent way like this:

CREATE INDEX texts_index ON docs
    USING gin ( to_tsvector('english',texts->'en') );

Desired structure:

{
  "ar": {
    "key1": "arabic1",
    "key2": "arabic2"
  },
  "en": {
    "key1": "english1",
    "key2": "english2"
  }
}

Any advice would be great! Thank you very much! - Is there anyway doing that in PostgreSQL, or should this be done in the application level?

Answer 1

You can convert this by unnesting and aggregating back:

select id, jsonb_object_agg(x.e ->> 'locale', x.e -> 'textDictionary')
from data d
  cross join jsonb_array_elements(d.texts -> 'texts') as x(e)
group by id;

This can be used for an UPDATE statement:

update data
  set texts = t.new_texts
from (  
  select id, jsonb_object_agg(x.e ->> 'locale', x.e -> 'textDictionary') as new_texts
  from data d
    cross join jsonb_array_elements(d.texts -> 'texts') as x(e)
  group by id
) t
where t.id = data.id;