Postgres concatenating/aggregating a jsonb values into one string

Question

I have a table entities with a jsonb column dict which holds the locales as described below:

{
  "fr": {
    "key1": "french1",
    "key2": "french2"
  },
  "en": {
    "key1": "value1",
    "key2": "value2",
    "key3": "value3"
  }
}

In the same table I have created a new tsvector column en_locale which need to hold the tsvector of the concatenation of the dict -> 'en' values.

for the example above I would like to achieve the following which is the concatenation of the values (order doesn't need to be preserved):

value1 value2 value3 values are separated by spaces.

this is in order to populate the tsvector column to_tsvector('value1 value2 value3')

Any advice would be great! Thank you very much!

As far as I can tell to_tsvector(dict -> 'en') would only include the values as well: dbfiddle.uk/… (Btw: I think the duplicated key2 is a typo, right?) — user330315
– user330315, Commented Aug 18, 2020 at 9:12
@a_horse_with_no_name you are right, but as I'm using Postgresql 9.6 to to_tsvector(cfg, jsonb) is not supported :( — Jay
– Jay, Commented Aug 27, 2020 at 9:17
Then it's time to upgrade ;) Better support for full text search on JSONB columns, was included in Postgres 10 — user330315
– user330315, Commented Aug 27, 2020 at 9:19

Akhilesh Mishra · Accepted Answer · 2020-08-18 12:52:10Z

1

You question is not clear what exactly you want. Just based on your question's TITLE you want to concatenate the values of all keys in dict -> 'en'.

So try this:

select 
(select string_agg(j.value, ' ') 
from jsonb_each_text(dict->'en') j) "values" from your_table

DEMO

edited Aug 18, 2020 at 12:52

answered Aug 18, 2020 at 10:26

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Postgres concatenating/aggregating a jsonb values into one string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related