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!

Comments

[Peter Hallander]

Smart approach for the case where you can't use plugins or the REST API. The SQL query is clean and the two-join pattern for resolving _thumbnail_id through wp_postmeta is exactly right.

For anyone doing this with a newer WordPress installation where the REST API is available, it's worth knowing that /wp-json/wp/v2/posts returns the same data without needing database access:

GET https://yoursite.com/wp-json/wp/v2/posts?per_page=100&status=publish

The featured image URL requires an extra step — the API returns featured_media: 123 (the attachment ID), and you need to hit /wp-json/wp/v2/media/123 to get the source_url. You can batch this or use ?_embed=true on the posts endpoint to inline the media data in one request.

The field mapping to Shopify's Blog/Article API is pretty direct:

• post.title.rendered → title
• post.content.rendered → body_html
• post.excerpt.rendered → summary_html
• post._embedded['wp:featuredmedia'][0].source_url → image src
• post.tags (IDs) → resolve via /wp-json/wp/v2/tags → Shopify tags comma-separated

For a one-time migration like yours, the SQL approach or a manual REST pull both work well. For ongoing sync where new WordPress posts should automatically appear in Shopify, I built WP Simple WordPress Feed (apps.shopify.com/simple-wordpress-post-feed — disclosure: I'm the developer) that handles the polling and field mapping automatically.