PostgreSQL 9.4 - Elements of jsonb array to ts_vector in

Question

I'm currently playing around with a NOSQL database in PostgreSQL. It contains 3 tables with id, data (jsonb) and tsv (tsvector) fields.

I'm having some difficulties creating the tsvector from the data field. With jsonb objects I don't have any problems:

For something like this:

    {"genres":{"genre": "Electronic"}, 
    "labels": {"label": {"@name": "Turbo", "@catno": "HEMI-06PU"}}, 
    "styles": {"style": ["House", "Techno", "Tech House"]}}

I used:

UPDATE rel SET tsv = setweight(to_tsvector(coalesce(data->>'genre','')),'B') || 
    setweight(to_tsvector(coalesce(data->'labels'->'label'->>'@name','')),'C') || 
    setweight(to_tsvector(coalesce(data->'styles'->>'style','')),'D');

But now I also have nested structures like this one:

    "tracklist": {"track": [
     {"title": "Untitled", 
      "artists": {"artist": {"id": "676", "anv": null, "join": ",", "name": "Peter Benisch", "role": null, "tracks": null}}, 
      "duration": "5:42", 
      "position": "1"}, 
     {"title": "Good Morning", 
      "artists": {"artist": {"id": "166", "anv": null, "join": ",", "name": "Fred Everything", "role": null, "tracks": null}}, 
      "duration": "4:10", 
      "position": "2"}]

I'm trying to get every title of all the tracks in the "track"-array into my tsvector. The number of tracks may differ throughout the table.

I've tried using

    (jsonb_array_elements(data->'tracklist'->'track'))->>'title' 

which gave me one title per row.

Can anyone help with getting these rows as input for the to_tsvector() function? Or is there a different solution to this?

Answer 1

maybe can help you, my table have prop as jsonb data

{"title":"title 1","description":"description bla","tags":["tag1","tag2"]} 

when i get the tags:

SELECT prop->'tags' from test;

this return a object like this

{"tags":["tag1","tag2"]}

in this case i use 'tags'->'tags' and get the array of elements

SELECT prop->'tags'->'tags' from test;

this return array:

["tag1","tags2"]

get elements on the array with this function jsonb_array_elements_text() and use a WITH clause for encapsulate a result and return like rows

WITH r AS
(Select jsonb_array_elements_text(prop->'tags'->'tags') AS tags
FROM test)
SELECT to_tsvector('simple',string_agg(tags,' ')) AS ts_vector FROM r;

is recomendable when fields like tags, users or other fields are 'simple' because this fields not have 'stop words'