MySQL Query to Extract WordPress Posts with Featured Thumbnails

Recently, I helped a friend migrate content from WordPress to Shopify.
The default WordPress export tool didn’t quite meet our needs, and due to an older version of WordPress, we couldn't install new plugins either. That made it quite a challenge to export the post data properly.

What surprised me the most was that WordPress's default export didn’t include featured image data (thumbnails).
Of course, since we were migrating blog posts, we wanted the thumbnail data to be tied to the articles as well.

So, I figured — why not just write a SQL query?
It worked well, so I'm sharing the query and an explanation of how it works.

✅ Final Query

SELECT 
  p.ID AS id,
  p.post_title AS title,
  p.post_date AS date,
  p.post_content AS content,
  CONCAT('https://example.com/wp-content/uploads/', pm2.meta_value) AS thumbnail_url
FROM wp_posts p
LEFT JOIN wp_postmeta pm1
  ON p.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'
LEFT JOIN wp_postmeta pm2
  ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'

Let me walk you through how I built this query.

🧱 WordPress Database Tables Involved

WordPress stores data across around 12 tables.
For this task, we’ll focus on the following two:

Table	Description
wp_posts	Stores post data
wp_postmeta	Stores post metadata

(Source: https://codex.wordpress.org/Database_Description)

🗂️ wp_posts Table

Column	Description
ID	Unique post ID (Primary Key)
post_title	Post title
post_name	Slug (used in URL)
post_content	Post content
post_type	Post type (post, page, custom_post_type, etc.)
post_status	Post status (publish, draft, etc.)
post_date	Publish date/time

🗃️ wp_postmeta Table

Column	Description
meta_id	Metadata ID
post_id	Related post ID (from wp_posts.ID)
meta_key	Metadata key (e.g., _thumbnail_id)
meta_value	Metadata value (e.g., image ID or path)

🛠️ Planning the Output Format

We wanted something like this:

id	title	date	content	thumbnail_url
101	Sample Title	2024-05-01 12:00:00	(Post body)	https://example.com/wp-content/uploads/2024/05/sample.jpg

🧪 Step-by-Step Query Breakdown

Step 1: Start with basic post data

SELECT 
  p.ID AS id,
  p.post_title AS title,
  p.post_date AS date,
  p.post_content AS content
FROM wp_posts p

Then filter to only published blog posts:

WHERE p.post_type = 'post'
  AND p.post_status = 'publish'

To filter custom post types, replace 'post' with your custom slug, e.g.:

WHERE p.post_type = 'work'

Step 2: Get the Thumbnail ID

LEFT JOIN wp_postmeta pm1
  ON p.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'

This gives us a result like:

id	title	thumbnail_id
101	Sample Post A	321
102	Sample Post B	322

Step 3: Get the File Path of the Image

Next, we take the thumbnail ID and use it to find the file path from _wp_attached_file

LEFT JOIN wp_postmeta pm2
  ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'

Step 4: Build the Full URL

Finally, we concatenate the base path with the file name:

CONCAT('https://example.com/wp-content/uploads/', pm2.meta_value) AS thumbnail_url

🎉 Final Query (Again for Reference)

SELECT 
  p.ID AS id,
  p.post_title AS title,
  p.post_date AS date,
  p.post_content AS content,
  CONCAT('https://example.com/wp-content/uploads/', pm2.meta_value) AS thumbnail_url
FROM wp_posts p
LEFT JOIN wp_postmeta pm1
  ON p.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'
LEFT JOIN wp_postmeta pm2
  ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'

📝 Conclusion

While AI can generate SQL for you quickly, understanding why and how it works is super important — especially when dealing with systems like WordPress that have specific data structures.

I don’t write raw SQL very often at work, but I’d like to keep growing in this area step-by-step!