How to execute a simple map in Postgres?

Question

I use triggers for full-text search in postgres as follows:

SELECT * FROM "Users" users WHERE 'search_text' % ANY(regexp_split_to_array(users.name,E'\\s+')) 

The query above checks to see if search_text matches any word (split by whitespace ) in User.displayName . This works, however, the results are not ordered by actual "rating".

The score can be calculated using the similarity(text,text) function.

The problem is that I have to sort by them the sum of all the similarities found for each word in User.name . Therefore, if the username is "ABC" , then its rating should be:

 similarity('search_text','A') + similarity('search_text','B') + similarity('search_text','C') 

So, I need to match the username and rating words and then sum them up. How can I do this in postgres?

Answer 1

Instead, instead of breaking into an array, broken into many, then rearrange the summation of similarities

 with users as ( select user_id, name from (values (1, 'John Smith')) t(user_id, name) ) select user_id, sum(similarity('smth', name_part)) from ( select user_id, regexp_split_to_table(name, E'\\s+') as name_part from users ) users where 'smth' % name_part group by user_id order by 2 desc ; user_id | sum ---------+------- 1 | 0.375 

I'm not sure if you want to eliminate those parts of the name for which the similarity is below the threshold. If you don’t just omit the where clause

By the way, why do you need to split the name? Why not just compare the entire name with the search bar?

 with users as ( select user_id, name from (values (1, 'John Smith')) t(user_id, name) ) select similarity('jon smth', name), * from users order by 1 desc ; similarity | user_id | name ------------+---------+------------ 0.333333 | 1 | John Smith