'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 |
|---|
