Jellyfin as a Music Server – Duplicates
Updated query for better detail!
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT a.artists,a.album,a.IndexNumber,a.name,a.path FROM TypedBaseItems a JOIN (SELECT name, album, IndexNumber FROM TypedBaseItems WHERE album IS NOT NULL AND IsFolder = 0 GROUP BY name, album, IndexNumber HAVING COUNT(*) > 1 ) b ON a.name = b.name AND a.album = b.album AND a.IndexNumber = b.IndexNumber ORDER BY a.artists, a.album, a.IndexNumber, a.name |
I run 2 Jellyfin servers – one for video, and one for music. In the world of streaming I’m old school in that I still curate a huge library of audio files. I don’t like being at the whim of streaming services, I don’t like how they treat artists, and I don’t like how I’m limited to artists on their platforms. I listen to a lot of obscure music across nearly all genres, and quite a bit of it can’t be found on *any* streaming service.
Jellyfin isn’t the perfect solution for a music server, but so far it’s the best I’ve found. Admittedly I may be biased due to my familiarity with the product, but every time I come across an alternative I give it a go only to wind up back at Jellyfin. Over the course of decades I’ve wound up with many duplicates in my library and, because often they aren’t identical in name or size, it’s not easy to identify them. The other day, as I saw yet another double listing for an album in Jellyfin, it dawned on me: Jellyfin knows these are dupes. Can I leverage it to my advantage?
Indeed I can.
First you’ll need DB Browser for SQLite. It’s free. Then you’ll want to locate your database file for Jellyfin. I’m running on Windows, and my path is
C:\Users\USERNAME\AppData\Local\Jellyfin\data\library.db
For the purpose of learning, I recommend making a copy of this file elsewhere to tinker with. If you accidentally change data in it, you could sink yourself. Everything happening below is strictly reading data, not manipulating, but still. You’ll have to shut Jellyfin down to access this file in DB Browser. Open it up, head to the Execute SQL tab, slap this query in, and execute it.
1 2 3 4 5 6 7 8 9 10 | SELECT a.artists,a.album,a.name,a.path FROM TypedBaseItems a JOIN (SELECT name, album FROM TypedBaseItems WHERE album IS NOT NULL GROUP BY name, album HAVING COUNT(*) > 1 ) b ON a.name = b.name AND a.album = b.album ORDER BY a.name |
This will return you a comma delimited list of dupes. It’s keying off of song title and album title. Mind you, it’s not perfect. It’s going to find dupes that aren’t here and there, especially on boxed sets and compilations where there might be multiple takes of the same tune. You could obviously add in track number (IndexNumber in the database) and leverage IsFolder to skip directory names (1 is yes, 0 is no), but for my purposes the above gave me accurate enough data to sort in a Google spreadsheet and start hacking away at my duplicates.
