How to convert full-text search highlighted fragments into rows in PostgreSQL full-text search?

Question

I am using PostgreSQL 10. I have a "booktbl" table which has 4

book_name
paragraph_title
content
ts_content

ts_content column is a tsvector column. When Iexecute this query:

select
    book_name , 
    paragraph_title ts_headline(content,to_tsquery('english','happy'), 'HighlightAll=true MaxFragments=100 FragmentDelimiter=$')
from bookstbl 
where ts_content @@ (to_tsquery('happy')) ;

I got a single row as a result which has a "ts_headline" column with all highlighted text search fragments. I want to convert all these fragments to individual rows. Like this:

book    para_title1     fragment1
book    para_title2     fragment2

How can I achieve this in PostgreSQL full text search?

Answer 1

One could use regexp_split_to_table, splitting on the FragmentDelimiter, like so:

REGEXP_SPLIT_TO_TABLE(
  ts_headline(content,
              to_tsquery('english','happy'), 
              'HighlightAll=true MaxFragments=100 FragmentDelimiter=$'), 
  '$')

Assembled into a query:

SELECT
    REGEXP_SPLIT_TO_TABLE(
        ts_headline(content,
                    to_tsquery('english','happy'), 
                    'HighlightAll=true MaxFragments=100 FragmentDelimiter=$'), 
        '$')
from bookstbl 
where ts_content @@ (to_tsquery('happy')) ;