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'))
tagsmatches 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)
contentsecond, 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?