1

Say I have a table like this in Postgres 9.5:

CREATE TABLE public.posts
(
    content text,
    tags jsonb,
)

I'd like to design a single query that:

  • Finds results based on exact tag OR matches (tags ?! array['tag1','tag2']) AND on matched free-form text (to_tsvector(content) @@ plainto_tsquery('some phrase'))

  • Orders by tags matches first, based on the # of matches - i.e. if row A has [apple, orange] and row B has just [apple], then a search for array['orange', 'apple'] would yield row A higher than row B (but they'd both be returned)

  • Orders by content second, based on the weight/similarity of the returned result. So a search for the keyword 'french hello' would yield a row containing content of "how do I say 'hello' in French?' higher than 'what's the weather like in the French Riviera?'

How would I go about combining the above in a single query, so that both exact tags matches and/or fuzzy content matches yield results using the weighting above?

1
  • adding some tag's samples can help
    – McNets
    Dec 11 '16 at 18:15
1

Check if this is what you're looking for [I didn't fully understand your first OR / AND condition. I assumed it was just an OR].

WITH posts AS
(
SELECT 
    * 
FROM
    (VALUES
        ('how do I say ''hello'' in French?', '{"orange":1, "apple":2}'::jsonb),
        ('what''s the weather like in the French Riviera?', '{"peach":3, "lemon":4}'::jsonb),
        ('awful weather in England', '{"peach":5, "lemon":6}'::jsonb),
        ('awful weather in England', '{"pineapple":5, "strawberry":6}'::jsonb),
        ('doubtful french fries', '{"blueberry":5, "pear":6}'::jsonb),
        ('the rain, in Spain, is mainly in the plain', '{"melon":7, "watermelon":8, "banana":9}'::jsonb)
    ) AS posts(content, tags)
) 

SELECT 
    *, 
    /* Use ts_rank to compare level of full text search coincidence */
    ts_rank(to_tsvector(content), plainto_tsquery('french') ||
          plainto_tsquery('hello')) AS rank,
    /* Subquery to count number of tag matches */
    (SELECT 
          count(case when tags ? a then 1 end) 
     FROM 
          unnest(array['melon', 'banana', 'lemon']) AS a
    ) AS number_of_matching_tags
FROM 
    posts 
WHERE
    /* Check for any of the tags */
    tags ?| array['melon', 'banana', 'lemon']
    OR
    /* Check for any of the search terms. You have to || tsqueries */
    to_tsvector(content) @@ 
        (plainto_tsquery('french') || plainto_tsquery('hello'))
ORDER BY
    number_of_matching_tags desc nulls last,
    rank desc ;

(The inclusion of number_of_matching_tags and rank columns is only to clarify results)

1
  • This works perfectly, thanks so much @joanolo!
    – Lee Benson
    Dec 11 '16 at 23:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.