Sample Database queries for Movies and TV Shows

ltcolumbo0 · March 19, 2025, 10:29pm

Many of us want to report on items in the Plex database and I’ve spent years hacking the database for data I want to see, so sharing it here for the benefit of others.

The queries were created using “DB Browser (SQLite)” and works connected to a live or static copy of the database. Note: Connecting to a live database requires reconnecting to see updates.

Copy and paste to Excel
Select the upper left corner of the query results and then Ctrl-C to copy the contents of the grid. Next, paste into an Excel spreadsheet. Note: Column headings are not copied.

Movies summary

-----------------------------------------
---                                  ---
---    Movie Summary                 ---
---    Version 2.0 - Jul 14, 2025    ---
---                                  ---
----------------------------------------

SELECT
	metadata_items.title AS Title,
	metadata_items.year AS Year,
	edition_title AS Edition

	
FROM metadata_items
INNER JOIN library_sections ON metadata_items.library_section_id = library_sections.id

WHERE 
--	metadata_items.metadata_type = 18	-- Collections 
	metadata_items.metadata_type = 1 	-- Movie	
	
------------------------------
--  Select Library name(s)  --
------------------------------
AND
	library_sections.name = 'Movies + 4K'
--	library_sections.name = 'Movies'
--	library_sections.name = 'Short Films'
--	library_sections.name = 'Documentaries'

-----------------------------
--  Specify other filters  --
-----------------------------
--Title mask
--	AND title LIKE 'Iron Man%'
--	AND title LIKE '%Wick%'
	
GROUP BY Title, Year, Edition
ORDER BY Title, Year, Edition

Movie details

----------------------------------------
---                                  ---
---    Movie Details                 ---
---    Version 2.0 - Jul 14, 2025    ---
---                                  ---
----------------------------------------

SELECT
	metadata_items.title AS Title,
	metadata_items.year AS Year,
	metadata_items.edition_title AS Edition,
	CASE
		WHEN media_items.video_codec = 'h264'
		THEN 'H.264 (AVC)'
		WHEN media_items.video_codec = 'hevc'
		THEN 'H.265 (HEVC)'
		WHEN media_items.video_codec = 'mpeg2video'
		THEN 'MPEG-2'
		WHEN media_items.video_codec = 'vc1'
		THEN 'VC-1'
		ELSE UPPER(media_items.video_codec)
	END AS "Video codec",
	CASE		
		WHEN instr(media_streams.extra_data,'level=') = 0
		THEN NULL
		WHEN media_items.video_codec = 'mpeg2video' -- only 1 digit
		THEN substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+6, 1)
		WHEN media_items.video_codec = 'vc1'
		THEN substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+6, 1)
		WHEN CAST(substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+6, 1) AS INTEGER) > 7
		THEN CAST(substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+6, 1) AS INTEGER) - 6
				|| '.'
				|| substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+7, 1)
		ELSE 
				substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+6, 1)
				|| '.'
				|| substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+7, 1)
	END AS Level,
	CASE 
		WHEN media_streams.extra_data LIKE '%scanType=interlaced%'
		THEN CASE
			WHEN media_streams.extra_data LIKE '%height=480%'
			THEN '480i'
			WHEN media_streams.extra_data LIKE '%height=720%'
			THEN '720i'
			WHEN media_streams.extra_data LIKE '%height=1080%'
			THEN '1080i'
			ELSE '480i'
		END
		ELSE CASE -- null OR media_streams.extra_data LIKE '%AscanType=progressive%' --
			WHEN media_streams.extra_data LIKE '%height=480%'
			THEN '480p'
			WHEN media_streams.extra_data LIKE '%height=720%'
			THEN '720p'
			WHEN media_streams.extra_data LIKE '%height=1080%'
			THEN '1080p'
			WHEN media_streams.extra_data LIKE '%height=2160%'
			THEN '4K'
			ELSE '480p'
		END
	END AS Size,
	CASE
		WHEN round(media_items.display_aspect_ratio, 2) <= 1.33
		THEN '4:3'
		ELSE 'Widescreen'
	END AS Aspect,
	CASE
		WHEN media_items.frames_per_second <=24
		THEN 'Yes'
		ELSE NULL
	END AS Film,
	round(media_items.frames_per_second,3) AS "Frame rate",
--	round(media_items.display_aspect_ratio, 2) AS "Aspect ratio",
	round(media_items.bitrate / CAST(1024 AS REAL) / 1024, 2) AS "Bitrate (Mbits/s)",
	CASE
		WHEN media_items.audio_codec = 'ac3'
		THEN 'AC-3'
		WHEN media_items.audio_codec = 'dca'
		THEN 'DTS (DCA)'
		WHEN media_items.audio_codec = 'truehd'
		THEN 'True HD'
		ELSE UPPER(media_items.audio_codec) 
	END AS "Audio codec",
	CASE
		WHEN media_items.audio_channels = 6
		THEN '5.1'
		WHEN media_items.audio_channels = 8
		THEN '7.1'
		ELSE media_items.audio_channels
	END AS "Audio channels"

	
FROM metadata_items
INNER JOIN library_sections ON metadata_items.library_section_id = library_sections.id
INNER JOIN media_items ON metadata_items.id = media_items.metadata_item_id
INNER JOIN media_streams ON media_items.id = media_streams.media_item_id

WHERE 
	metadata_items.metadata_type = 1 	-- Movie	
	-- Selects only the video streams, eliminating duplicaets caused by the audio and subtitle tracks
AND	media_streams.stream_type_id = 1   -- Media streams (1/Video, 2/Audio, 3/Subtitle)

------------------------------
--  Select Library name(s)  --
------------------------------
AND
	library_sections.name = 'Movies + 4K'
--	library_sections.name = 'Movies'
--	library_sections.name = 'Short Films'
--	library_sections.name = 'Documentaries'

-----------------------------
--  Specify other filters  --
-----------------------------
--Title mask
--	AND title LIKE 'Iron Man%'
--	AND title LIKE '%Wick%'

-- Audio
--	AND (media_items.audio_codec = 'truehd' OR audio_channels = 8)

-- High bitrate
--	AND (media_items.video_codec = 'h264' AND round(media_items.bitrate / CAST(1024 AS REAL) / 1024, 2) >= 30)

-- Video codec
--Both MPEG-2 and VC-1 can be stored more efficiently in H.264 (AVC) or H.265 (HEVC)
--AND "Video codec" = 'MPEG-2'		-- Unsupported by Roku, so PLEX transcodes
--AND "Video codec" = 'VC-1'		-- Unsupported by Fire TV & Roku, so PLEX attempts to transcode and may fail to play with higher bit rates
	
ORDER BY 
	metadata_items.title ASC, 
	media_items.bitrate DESC, 
	media_items.video_codec, 
	media_items.audio_codec

TV Shows & Episode counts

----------------------------------------
---                                  ---
---    TV Show Episode Counts        ---
---    Version 2.0 - Jul 10, 2025    ---
---                                  ---
----------------------------------------

SELECT
	mdi_2.title AS "Show name",
	min(metadata_items.year) AS "Start Year",
	max(metadata_items.year) AS "End Year",
	count(metadata_items.ID) AS Episodes
	 
	
FROM metadata_items
INNER JOIN library_sections ON metadata_items.library_section_id = library_sections.id
INNER JOIN media_items ON metadata_items.id = media_items.metadata_item_id
INNER JOIN metadata_items AS mdi_1 ON metadata_items.parent_id = mdi_1.id
INNER JOIN metadata_items AS mdi_2 ON mdi_1.parent_id = mdi_2.id
INNER JOIN media_streams ON media_items.id = media_streams.media_item_id

WHERE 
	media_streams.stream_type_id = 1   -- Media streams (1/Video, 2/Audio, 3/Subtitle)
AND
------------------------------
--  Select Library name(s)  --
------------------------------
	library_sections.name = 'TV Shows'

-----------------------------
--  Specify other filters  --
-----------------------------
--Title mask
--AND title LIKE 'Iron Man%'

--Episode name mask
--AND Episode LIKE 'Day of Days%'

--Show name
--AND "Show name" = 'Band of Brothers'

GROUP BY "Show name" 
ORDER BY "Show name", metadata_items.title, media_items.bitrate DESC, media_items.video_codec, media_items.audio_codec

TV Show Episode Details

----------------------------------------
---                                  ---
---    TV Show Episode Details       ---
---    Version 2.0 - Jul 10, 2025    ---
---                                  ---
----------------------------------------

SELECT
	mdi_2.title AS "Show name",
	metadata_items.title AS Episode,
	metadata_items.year AS Year,
	CASE
		WHEN media_items.video_codec = 'h264'
		THEN 'H.264 (AVC)'
		WHEN media_items.video_codec = 'hevc'
		THEN 'H.265 (HEVC)'
		WHEN media_items.video_codec = 'mpeg2video'
		THEN 'MPEG-2'
		WHEN media_items.video_codec = 'vc1'
		THEN 'VC-1'
		ELSE UPPER(media_items.video_codec)
	END as "Video codec",
	CASE		
		WHEN instr(media_streams.extra_data,'level=') = 0
		THEN NULL
		WHEN media_items.video_codec = 'mpeg2video' -- only 1 digit
		THEN substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+6, 1)
		WHEN media_items.video_codec = 'vc1'
		THEN substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+6, 1)
		WHEN CAST(substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+6, 1) as INTEGER) > 7
		THEN CAST(substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+6, 1) as INTEGER) - 6
				|| '.'
				|| substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+7, 1)
		ELSE 
				substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+6, 1)
				|| '.'
				|| substr(media_streams.extra_data,instr(media_streams.extra_data,'level=')+7, 1)
	END as Level,
	CASE 
		WHEN media_streams.extra_data LIKE '%scanType=interlaced%'
		THEN CASE
			WHEN media_streams.extra_data LIKE '%height=480%'
			THEN '480i'
			WHEN media_streams.extra_data LIKE '%height=720%'
			THEN '720i'
			WHEN media_streams.extra_data LIKE '%height=1080%'
			THEN '1080i'
			ELSE '480i'
		END
		ELSE CASE -- null OR media_streams.extra_data LIKE '%AscanType=progressive%' --
			WHEN media_streams.extra_data LIKE '%height=480%'
			THEN '480p'
			WHEN media_streams.extra_data LIKE '%height=720%'
			THEN '720p'
			WHEN media_streams.extra_data LIKE '%height=1080%'
			THEN '1080p'
			WHEN media_streams.extra_data LIKE '%height=2160%'
			THEN '4K'
			ELSE '480p'
		END
	END as Size,
	CASE
		WHEN round(media_items.display_aspect_ratio, 2) <= 1.33
		THEN '4:3'
		ELSE 'Widescreen'
	END as Aspect,
	CASE
		WHEN media_items.frames_per_second <=24
		THEN 'Yes'
		ELSE NULL
	END as Film,
	round(media_items.frames_per_second,3) as "Frame rate",
--	round(media_items.display_aspect_ratio, 2) as "Aspect ratio",
	round(media_items.bitrate / CAST(1024 as REAL) / 1024, 2) as "Bitrate (Mbits/s)",
	CASE
		WHEN media_items.audio_codec = 'ac3'
		THEN 'AC-3'
		WHEN media_items.audio_codec = 'dca'
		THEN 'DTS (DCA)'
		WHEN media_items.audio_codec = 'truehd'
		THEN 'True HD'
		ELSE UPPER(media_items.audio_codec) 
	END as "Audio codec",
	CASE
		WHEN media_items.audio_channels = 6
		THEN '5.1'
		WHEN media_items.audio_channels = 8
		THEN '7.1'
		ELSE media_items.audio_channels
	END as "Audio channels"

	
FROM metadata_items
INNER JOIN library_sections ON metadata_items.library_section_id = library_sections.id
INNER JOIN media_items ON metadata_items.id = media_items.metadata_item_id
INNER JOIN metadata_items AS mdi_1 ON metadata_items.parent_id = mdi_1.id
INNER JOIN metadata_items AS mdi_2 ON mdi_1.parent_id = mdi_2.id
INNER JOIN media_streams ON media_items.id = media_streams.media_item_id

WHERE 
	media_streams.stream_type_id = 1   -- Media streams (1/Video, 2/Audio, 3/Subtitle)
AND
------------------------------
--  Select Library name(s)  --
------------------------------
	library_sections.name = 'TV Shows'

-----------------------------
--  Specify other filters  --
-----------------------------
--Show name
--AND "Show name" = 'Band of Brothers'

--Episode name mask
--AND Episode LIKE 'Day of Days%'

ORDER BY "Show name", metadata_items.title, media_items.bitrate DESC, media_items.video_codec, media_items.audio_codec

Video Files

----------------------------------------
---                                  ---
---    Video Files                   ---
---    Version 2.0 - Jul 14, 2025    ---
--                                   ---
----------------------------------------

--=====================================================================
---  Combine queries for Movies/TV Shows (parent) & Extras (child)  ---
--=====================================================================
SELECT 
	library_name AS Library,
	file AS File,
	--REPLACE(SUBSTR(file,1, LENGTH(file) - LENGTH(REPLACE(file, RTRIM(file, REPLACE(file, '/', '' ) ), ''))-1),'%20',' ')	AS Folder,
	title AS Title,
	year AS Year,
	edition_title AS Edition,
	extra_type AS "Extra Type",
	CASE
		WHEN video_codec = 'h264'
		THEN 'H.264 (AVC)'
		WHEN video_codec = 'hevc'
		THEN 'H.265 (HEVC)'
		WHEN video_codec = 'mpeg2video'
		THEN 'MPEG-2'
		WHEN video_codec = 'vc1'
		THEN 'VC-1'
		ELSE UPPER(video_codec)
	END AS "Video codec",
	(SELECT 
		CASE 
			WHEN ms.extra_data LIKE '%scanType=interlaced%'
			THEN CASE
				WHEN ms.extra_data LIKE '%height=480%'
				THEN '480i'
				WHEN ms.extra_data LIKE '%height=720%'
				THEN '720i'
				WHEN ms.extra_data LIKE '%height=1080%'
				THEN '1080i'
				ELSE '480i'
			END
			ELSE CASE -- null OR ms.extra_data LIKE '%AscanType=progressive%' --
				WHEN ms.extra_data LIKE '%height=480%'
				THEN '480p'
				WHEN ms.extra_data LIKE '%height=720%'
				THEN '720p'
				WHEN ms.extra_data LIKE '%height=1080%'
				THEN '1080p'
				WHEN ms.extra_data LIKE '%height=2160%'
				THEN '4K'
				ELSE '480p'
			END
		END
		
		FROM media_streams AS ms
		WHERE media_items_id = ms.media_item_id
	) AS Size,
	CASE
		WHEN frames_per_second <=24
		THEN 'Yes'
		ELSE NULL
	END AS Film,	
	round(frames_per_second,3) as "Frame rate"

	
FROM  -- Start Subquery
	(
	----------------------------------
	---  Movies/TV Shows (parent)  ---
	----------------------------------
	SELECT
		library_sections.name AS library_name,
		media_parts.file,
		metadata_items.title,
		metadata_items.year,
		NULL AS extra_type,
		media_items.video_codec,
		media_items.frames_per_second,
		metadata_items.edition_title,
		media_items.id AS media_items_id
		
	FROM metadata_items 
	INNER JOIN library_sections ON metadata_items.library_section_id = library_sections.id
	INNER JOIN media_items ON metadata_items.id = media_items.metadata_item_id
	INNER JOIN media_parts ON media_items.id = media_parts.media_item_id


	UNION ALL

	------------------------
	---  Extras (child)  ---
	------------------------
	SELECT
		library_sections.name as library_name,
		media_parts.file,
		related_metadata_items.title,
		related_metadata_items.year,
		
		CASE 
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"1"%'
			THEN 'Trailer'
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"2"%'
			THEN 'Deleted Scene'
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"3"%'
			THEN 'Interview'
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"4"%'
			THEN 'Music Video'
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"5"%'
			THEN 'Behind The Scenes'
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"6"%'
			THEN 'Scene'
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"7"%'
			THEN 'Lyrics Music Video'
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"8"%'
			THEN 'Live Music Video'
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"9"%'
			THEN 'Concert Performance'
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"10"%'
			THEN 'Featurette'
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"11"%'
			THEN 'Short'
			WHEN related_metadata_items.extra_data LIKE '{"ex:extraType":"12"%'
			THEN 'Other'
			ELSE NULL
		END AS extra_type,
		
		media_items.video_codec,
		media_items.frames_per_second,
		metadata_items.edition_title,
		media_items.id AS media_items_id

		
	FROM metadata_items
	INNER JOIN library_sections ON metadata_items.library_section_id = library_sections.id
	INNER JOIN metadata_relations ON metadata_items.id = metadata_relations.metadata_item_id
	INNER JOIN metadata_items AS related_metadata_items ON metadata_relations.related_metadata_item_id = related_metadata_items.id
	INNER JOIN media_items ON related_metadata_items.id = media_items.metadata_item_id
	INNER JOIN media_parts ON media_items.id = media_parts.media_item_id

	WHERE 
		NOT related_metadata_items.guid LIKE 'iva://api%'	-- Remove Internet Extras

	--Grouping eliminates duplicates for the same path (folder & file) caused by JOINs
	GROUP BY media_parts.file

	) -- End Subquery

WHERE
------------------------------
--  Select Library name(s)  --
------------------------------
	library_name = 'Movies + 4K'
--	library_name = 'Movies'
--	library_name = 'Short Films'
--	library_name = 'Documentaries'
--	library_name = 'TV Shows'
	
-----------------------
--  Specify filters  --
-----------------------
--Title mask
--	AND title LIKE 'Iron Man%'
--	AND title LIKE '%Wick%'

-- Audio
--	AND (media_items.audio_codec = 'truehd' OR audio_channels = 8)

-- High bitrate
--	AND (media_items.video_codec = 'h264' AND round(media_items.bitrate / CAST(1024 AS REAL) / 1024, 2) >= 30)

-- Video codec
--Both MPEG-2 and VC-1 can be stored more efficiently in H.264 (AVC) or H.265 (HEVC)
--AND "Video codec" = 'MPEG-2'		-- Unsupported by Roku, so PLEX transcodes
--AND "Video codec" = 'VC-1'		-- Unsupported by Fire TV & Roku, so PLEX attempts to transcode and may fail to play with higher frame rates
	
ORDER BY file

Sample query results

tom80H · March 19, 2025, 10:32pm

Or you could use Webtools-NG.

ltcolumbo0 · March 20, 2025, 4:26pm

Would you happen to know which table and field represents the “soft delete”?

tom80H · March 20, 2025, 5:27pm

You mean what’s in Plex‘s „Trash“?

ltcolumbo0 · March 20, 2025, 5:34pm

Yes and no. I have a lot of “soft delete” items in my database for which the files no longer physically exist and that are no longer in trash, or at least Empty Trash does not clear them. Should this be considered a bug that I should report?

My thought is to use the “soft delete” flag to ignore and/or delete them from my queries.

OttoKerner · March 21, 2025, 7:48am

These should be one and the same.

Plex will often not clear the trash if the database is damaged.

If the DB file is healthy, a regular reason that the trash is retained is that not all the paths listed on the “Add Folders” tab of a library are:

existent
accessible to Plex server
not completely empty

There is a third reason cropping up lately, but only specifically if your server is running on a Mac and uses a Synology NAS as media storage: Plex Files Unavailable but they’re not missing - #525 by scuzzbono

MovieFan · March 21, 2025, 7:29pm

Don’t forget that if the media is in a folder/drive that is not accessible, these don’t get removed incase the location becomes available again. Recreate the folder/drive, run a scan, and done.

ltcolumbo0 · March 21, 2025, 7:43pm

Thanks. I tried recreating the directory structures earlier today and adding it to an existing library and a new one. Unfortunately, running a scan and then emptying trash did not remove the entries. I even tried adding one of the same files and one new one. All this did was add one more “ghost” entry for the new file.

Any other ideas?

MovieFan · March 22, 2025, 12:46am

Run a scan of the library with the ghost entry and upload the server log.

OttoKerner · March 22, 2025, 6:21am

Recreating the folder is not enough. It must also be not completely empty of files.

ltcolumbo0 · April 1, 2025, 5:43pm

Testing seems to indicate you must create the same file structure and files, add them, move them out of all libraries, and then empty the trash.

It would be great to allow a delete for any unused items over some period of time (ex. 1 year, 6 months), preferably user configurable.

ltcolumbo0 · April 1, 2025, 5:48pm

Added SQL example for displaying the path or, folder and file structure for movies and extras. Can also be used for other media types.

MovieFan · April 3, 2025, 2:26am

Not sure what you did, but you should only need to create the folder that your library pointed to. I.e. your movie library is to point to C:\movies, but now you changed it to C:\my movies. You only need to recreate C:\movies and place 1 file in there, any file. As long as that base folder doesn’t appear empty, Plex will remove any associated entries.

Topic		Replies	Views
HOWTO: query data from PLEX on a Mac Tips, Tricks & How-Tos server-mac	77	10433	March 9, 2023
Does not warn when deleting folders General Discussions	28	479	November 30, 2019
Export library to text/html format? Dev/API Corner other-dev	97	1134	December 20, 2019
Plex not deleting trash General Discussions	19	224	January 8, 2020
Movie and TV Extras Feature Suggestions	37	276	June 10, 2021

Sample Database queries for Movies and TV Shows

Related topics