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?

score 2 · Accepted Answer · 2020-08-16 16:15:35Z

2

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;

edited Aug 16, 2020 at 16:15

answered Aug 16, 2020 at 14:31

user330315

Sign up to request clarification or add additional context in comments.

Comments

Akhilesh Mishra · Accepted Answer · 2020-08-16 14:54:12Z

1

You can try this:

update docs 
 set texts= (
            select jsonb_object_agg(j->>'locale',j->'textDictionary') 
            from 
            jsonb_array_elements(texts->'texts') j
            )

DEMO

answered Aug 16, 2020 at 14:54

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

1 Comment

Akhilesh Mishra Over a year ago

No it will not and don't mind but you have edited your answer with same.

Collectives™ on Stack Overflow

Postgresql convert array of objects to map of objects

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related