Table: shopify_collection_product - Query Shopify Collection Products using SQL
Shopify Collection Products represent the relationship between products and their associated collections within the Shopify platform. Collections are a way to group and organize products, making them easier to manage and find. They can be created manually, or they can be automatically generated based on conditions that you specify.
Table Usage Guide
The shopify_collection_product
table provides insights into the relationship between products and collections within Shopify. As a store manager or developer, explore product-specific details and their collection associations through this table, including collection IDs, product IDs, and position in the collection. Utilize it to uncover information about product organization, such as which products belong to which collections, and the order of products within collections.
Examples
Basic info
Explore which products are included in your Shopify collections, along with their vendors and status. This can help you manage and organize your products more effectively.
select collection_id, collection_title, product_id, product_title, vendor, statusfrom shopify_collection_product;
select collection_id, collection_title, product_id, product_title, vendor, statusfrom shopify_collection_product;
List all products in a specific collection by collection ID
Explore which products belong to a certain collection using a unique identifier, allowing you to assess the range and variety within that collection. This could be useful for inventory management or identifying gaps in your product offerings.
select collection_id, product_id, product_title, handle, vendor, statusfrom shopify_collection_productwhere collection_id = 444300460327;
select collection_id, product_id, product_title, handle, vendor, statusfrom shopify_collection_productwhere collection_id = 444300460327;
List all products in a specific collection by collection title
Explore which products fall under a specific collection in your Shopify store. This is particularly useful to assess the range of items within a given collection, aiding in inventory management and marketing efforts.
select collection_id, product_id, product_title, collection_title, handle, vendor, statusfrom shopify_collection_productwhere collection_title = 'Jelly';
select collection_id, product_id, product_title, collection_title, handle, vendor, statusfrom shopify_collection_productwhere collection_title = 'Jelly';
Get the total count of products in each collection
Explore which collections have the most products to better manage inventory and sales strategies. This allows for a comprehensive understanding of product distribution across different collections.
select collection_title, count(*) as total_countfrom shopify_collection_productgroup by collection_title;
select collection_title, count(*) as total_countfrom shopify_collection_productgroup by collection_title;
Get the number of products in each collection that are currently in stock
Explore which collections have products currently in stock. This query is useful for inventory management, allowing you to see the distribution of available products across different collections.
select collection_title, count(*) as total_countfrom shopify_collection_productwhere status = 'in_stock'group by collection_title;
select collection_title, count(*) as total_countfrom shopify_collection_productwhere status = 'in_stock'group by collection_title;
List all the products in a collection created in the last one month
Identify all the products added to any collection within the past month. This can assist in understanding recent inventory changes and tracking product performance.
select collection_id, collection_title, product_id, product_title, vendor, status, created_atfrom shopify_collection_productwhere created_at >= now() - interval '30' day;
select collection_id, collection_title, product_id, product_title, vendor, status, created_atfrom shopify_collection_productwhere created_at >= datetime('now', '-30 day');
Schema for shopify_collection_product
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
collection_id | bigint | The ID of the custom collection. | |
collection_title | text | Title of the custom collection. | |
created_at | timestamp with time zone | The product creation date. | |
handle | text | Product handle. | |
image | jsonb | The product image. | |
images | jsonb | List of images associated with the product. | |
options | jsonb | The product options. | |
product_id | bigint | Product ID. | |
product_title | text | Product title. | |
product_type | text | The type of the products sold. | |
published_at | timestamp with time zone | The product publish time. | |
published_scope | text | The product publish scope. | |
shop_name | text | =, !=, ~~, ~~*, !~~, !~~* | The name of the shop. |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
status | text | The product status. | |
tags | text | The product tags. | |
template_suffix | text | The product template suffix. | |
title | text | Title of the resource. | |
updated_at | timestamp with time zone | The time product was updated. | |
variants | jsonb | The product variants. | |
vendor | text | The vendor of the shopify products. |
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_collection_product