How to find WooCommerce products in the database

WooCommerce stores products as posts in the database, but also uses custom taxonomies and metadata to save specific information such as price, attributes, product type, and more.

This article shows you how to query the database directly to retrieve products, variations, and their related values in a clear and updated way.

Displaying Products

WooCommerce stores all products (simple, variable, grouped, etc.) in the wp_poststable with post_type = 'product'.

SELECT ID, post_title, post_status
FROM wp_posts
WHERE post_type = 'product'
  AND post_status = 'publish';

This query returns all published products, regardless of whether they are simple or variable.

Getting the Product Type (simple, variable, etc.)

In recent versions, WooCommerce no longer stores the product type as a meta value, but instead uses a custom taxonomy called product_type.

SELECT p.ID, p.post_title, tt.term_id, t.name AS product_type
FROM wp_posts p
JOIN wp_term_relationships tr ON p.ID = tr.object_id
JOIN wp_term_taxonomy tt     ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t              ON tt.term_id = t.term_id
WHERE p.post_type = 'product'
  AND tt.taxonomy = 'product_type';

This gives you the product name and its type (simple, variable, grouped, external).

Getting Prices, SKU, and Stock

Values such as price, SKU, and stock are still stored in wp_postmeta:

SELECT p.ID              AS product_id,
       p.post_title      AS name,
       sku.meta_value    AS sku,
       price.meta_value  AS regular_price,
       sale.meta_value   AS sale_price,
       stock.meta_value  AS stock
FROM wp_posts p
LEFT JOIN wp_postmeta sku   ON p.ID = sku.post_id   AND sku.meta_key = '_sku'
LEFT JOIN wp_postmeta price ON p.ID = price.post_id AND price.meta_key = '_regular_price'
LEFT JOIN wp_postmeta sale  ON p.ID = sale.post_id  AND sale.meta_key = '_sale_price'
LEFT JOIN wp_postmeta stock ON p.ID = stock.post_id AND stock.meta_key = '_stock'
WHERE p.post_type = 'product'
  AND p.post_status = 'publish';

Viewing Product Attributes

The attributes assigned to the parent product are stored in the _product_attributesmeta key as a serialized array:

SELECT post_id, meta_value
FROM wp_postmeta
WHERE meta_key = '_product_attributes'
  AND post_id = 123; -- Replace with the product ID

To properly visualize the content, use a PHP unserializer tool or load it within WordPress to interpret it as an array.

Querying Variations of a Variable Product

Variations are stored with post_type = 'product_variation'and their post_parentset to the parent product ID.

SELECT v.ID               AS variation_id,
       v.post_title       AS variation_name,
       sku.meta_value     AS sku,
       price.meta_value   AS regular_price,
       sale.meta_value    AS sale_price
FROM wp_posts v
LEFT JOIN wp_postmeta sku   ON v.ID = sku.post_id   AND sku.meta_key = '_sku'
LEFT JOIN wp_postmeta price ON v.ID = price.post_id AND price.meta_key = '_regular_price'
LEFT JOIN wp_postmeta sale  ON v.ID = sale.post_id  AND sale.meta_key = '_sale_price'
WHERE v.post_type = 'product_variation'
  AND v.post_parent = 123; -- Replace with the variable product ID

Getting Attributes of a Variation

SELECT meta_key, meta_value
FROM wp_postmeta
WHERE post_id = 456 -- Variation ID
  AND meta_key LIKE 'attribute_%';

This is where selected attributes like attribute_pa_color, attribute_pa_size, etc. are stored.

Querying WooCommerce products directly from the database is useful for auditing data, generating custom reports, or integrating with external systems. Keep in mind:

• The product type is now stored in the product_type taxonomy, not in postmeta.
• The attributes are saved in _product_attributes (for the parent product) and as attribute_* in each variation.
• Variations are child posts with post_type = 'product_variation'.