Table: shopify_product_variant - Query Shopify Product Variants using SQL

A Shopify Product Variant represents different versions of a product in a Shopify store. These versions can differ in many aspects, such as size, color, material, or other attributes. They are crucial in providing customers with a variety of options and increasing the diversity of product offerings in a Shopify store.

Table Usage Guide

The shopify_product_variant table provides insights into the product variants within a Shopify store. As a store manager or data analyst, you can explore variant-specific details through this table, including price, SKU, and inventory quantities. Utilize it to uncover information about product diversity, pricing strategies, and inventory management in your Shopify store.

Examples

Basic info

Explore which product variants are available in your Shopify store, helping you to assess inventory and manage product listings effectively.

select
  id,
  product_id,
  title,
  inventory_item_id
from
  shopify_product_variant;

select
  id,
  product_id,
  title,
  inventory_item_id
from
  shopify_product_variant;

Retrieve all products where the weight is greater than 5.5 pounds

Explore which product variants in your Shopify store weigh more than 5.5 pounds. This can be useful for determining shipping costs or identifying heavy items in your inventory.

select
  id,
  product_id,
  title,
  weight,
  weight_unit,
  inventory_item_id
from
  shopify_product_variant
where
  weight :: decimal > 5.5
  and weight_unit = 'lb';

select
  id,
  product_id,
  title,
  weight,
  weight_unit,
  inventory_item_id
from
  shopify_product_variant
where
  cast(weight as decimal) > 5.5
  and weight_unit = 'lb';

Retrieve all products that have a price less than $50 and are not taxable

Explore which products are priced under $50 and are exempt from taxation. This can be beneficial for customers seeking affordable and tax-free options.

select
  id,
  product_id,
  title,
  weight,
  weight_unit,
  inventory_item_id
from
  shopify_product_variant
where
  price :: numeric < 50
  and not taxable;

select
  id,
  product_id,
  title,
  weight,
  weight_unit,
  inventory_item_id
from
  shopify_product_variant
where
  CAST(price AS REAL) < 50
  and not taxable;

Retrieve all products that have inventory management enabled and have less than 10 items in stock

Explore which products are running low on stock and have inventory management enabled. This query can help you proactively manage inventory and prevent product shortages.

select
  id,
  product_id,
  title,
  inventory_item_id
from
  shopify_product_variant
where
  inventory_management is not null
  and inventory_quantity < 10;

select
  id,
  product_id,
  title,
  inventory_item_id
from
  shopify_product_variant
where
  inventory_management is not null
  and inventory_quantity < 10;

Get the variant with the lowest price for a specific product

Discover the variant of a specific product that offers the lowest price. This can be useful in identifying the most cost-effective option for purchasing or selling a particular product.

select
  id,
  product_id,
  title,
  inventory_item_id
from
  shopify_product_variant
where
  product_id = '8264171749671'
order by
  price
limit
  1;

select
  id,
  product_id,
  title,
  inventory_item_id
from
  shopify_product_variant
where
  product_id = '8264171749671'
order by
  price
limit
  1;

Get the variants that are out of stock

Discover the variants that are currently out of stock. This is beneficial in managing inventory and understanding which products need to be restocked.

select
  id,
  product_id,
  title,
  inventory_item_id
from
  shopify_product_variant
where
  inventory_quantity = 0;

select
  id,
  product_id,
  title,
  inventory_item_id
from
  shopify_product_variant
where
  inventory_quantity = 0;

Retrieve all products that have a barcode and are fulfilled by a specific service

Explore which products are fulfilled by a specific service and also have a barcode. This can be useful for tracking inventory or managing product distribution.

select
  id,
  product_id,
  title,
  inventory_item_id
from
  shopify_product_variant
where
  barcode is not null
  and fulfillment_service = 'my_fulfillment_service';

select
  id,
  product_id,
  title,
  inventory_item_id
from
  shopify_product_variant
where
  barcode is not null
  and fulfillment_service = 'my_fulfillment_service';

Schema for shopify_product_variant

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
admin_graphql_api_id	text		The admin graphql API ID of the product.
barcode	text		The barcode of the product variant.
compare_at_price	double precision		The compare at price of the product variant.
created_at	timestamp with time zone		The date and time when the product variant was created.
fulfillment_service	text		The fulfillment service of the product variant.
grams	bigint		The weight of the product variant in grams.
id	bigint	=	The ID of the shopify product.
image_id	bigint		The ID for the image associated with the variant.
inventory_item_id	bigint		The inventory policy for the product variant.
inventory_management	text		The inventory management of the product variant.
inventory_policy	text		The inventory policy of the product.
inventory_quantity	bigint		The number of inventory items available for the product variant.
old_inventory_quantity	bigint		The old inventory quantity of the product variant.
option1	text		The first option for the variant.
option2	text		The second option for the variant.
option3	text		The third option for the variant.
position	bigint		The position of the variant in the list of variants for the product.
price	double precision		The price of the product variant.
product_id	bigint		The ID of the product variant.
product_title	text		The title of the product.
requires_shipping	boolean		Whether the product variant requires shipping.
sku	text		The Stock Keeping Unit (SKU) for the product variant.
tax_code	text		The tax code of the product variant.
taxable	boolean		Whether or not the product variant is taxable.
title	text		Title of the resource.
updated_at	timestamp with time zone		The date and time when the product variant was last updated.
weight	text		The weight of the product variant.
weight_unit	text		The unit of measurement for the product variant weight.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- shopify

You can pass the configuration to the command with the --config argument:

steampipe_export_shopify --config '<your_config>' shopify_product_variant