'Weighted + ordered tag search using Postgres

After an AI file analysis across tens of thousands of audio files I end up with this kind of data structure in a Postgres DB:

id | name          | tag_1 | tag_2   | tag_3 | tag_4          | tag_5
1  | first song    | rock  | pop     | 80s   | female singer  | classic rock
2  | second song   | pop   | rock    | jazz  | electronic     | new wave
3  | third song    | rock  | funk    | rnb   | 80s            | rnb

Tag positions are really important: the more "to the left", the more prominent it is in the song. The number of tags is also finite (50 tags) and the AI always returns 5 of them for every song, no null values expected.

On the other hand, this is what I have to query:

{"rock" => 15, "pop" => 10, "soul" => 3}

The key is a Tag name and the value an arbitrary weight. Numbers of entries could be random from 1 to 50. According to the example dataset, in this case it should return [1, 3, 2]

I'm also open for data restructuring if it could be easier to achieve using raw concatenated strings but... is it something doable using Postgres (tsvectors?) or do I really have to use something like Elasticsearch for this?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source