Table: shopify_smart_collection - Query Shopify Smart Collections using SQL
A Shopify Smart Collection is a feature within Shopify that allows you to create product groups based on predefined conditions. These conditions can include product title, type, price, tag, weight, and more. Smart Collections provide a dynamic way to organize and display products in your Shopify store, improving product discovery and shopping experience for your customers.
Table Usage Guide
The shopify_smart_collection
table provides insights into Smart Collections within Shopify. As an e-commerce manager or developer, explore collection-specific details through this table, including the rules that define each collection and associated metadata. Utilize it to uncover information about your product organization, such as collections that include specific product types or tags, and to verify the conditions that define each collection.
Examples
Basic info
Explore which smart collections in your Shopify store have been updated recently. This can help you track product trends and manage your inventory more effectively.
select id, title, updated_at, handlefrom shopify_smart_collection;
select id, title, updated_at, handlefrom shopify_smart_collection;
Get the number of rules for each smart collection
Explore the number of rules applied to each smart collection in your Shopify account, allowing you to assess the complexity of your collection filtering system. This can help streamline your product management by identifying collections with an excessive or minimal number of rules.
select id, title, handle, updated_at, jsonb_array_length(rules) as num_rulesfrom shopify_smart_collection;
select id, title, handle, updated_at, json_array_length(rules) as num_rulesfrom shopify_smart_collection;
Get the total number of published smart collections
Determine the total count of smart collections that have been made publicly available. This information can be useful for assessing the volume of collections that are accessible to customers.
select count(*) as published_smart_collectionfrom shopify_smart_collectionwhere published;
select count(*) as published_smart_collectionfrom shopify_smart_collectionwhere published = 1;
Get the smart collections published with in last 30 days
Explore which smart collections were published within the last 30 days. This can help keep track of recent updates and manage your collections more effectively.
select id, title, handle, updated_at, published_atfrom shopify_smart_collectionwhere published_at >= now() - interval '30' dayorder by published_at;
select id, title, handle, updated_at, published_atfrom shopify_smart_collectionwhere published_at >= datetime('now', '-30 day')order by published_at;
Get the IDs, titles, and image URLs of all custom collections that have an image
Discover the custom collections that include images, which can be useful for auditing visual content or identifying collections for promotional campaigns.
select id, title, updated_at, handle, image ->> 'src' as image_urlfrom shopify_smart_collectionwhere image ->> 'src' is not null;
select id, title, updated_at, handle, json_extract(image, '$.src') as image_urlfrom shopify_smart_collectionwhere json_extract(image, '$.src') is not null;
Retrieve all smart collections that contain a specific metafield value
Discover the segments that contain a certain metafield value within all smart collections. This is particularly useful when you want to identify and analyze the collections that are associated with a specific attribute or characteristic.
select id, title, handle, published, updated_at, metafieldsfrom shopify_smart_collectionwhere metafields @> '[{"value": "hello test 123"}]';
select id, title, handle, published, updated_at, metafieldsfrom shopify_smart_collectionwhere json_extract(metafields, '$[*].value') = 'hello test 123';
List all disjunctive smart collections
Explore the smart collections on your Shopify store that use the disjunctive condition, allowing for a broader product inclusion in each collection. This can be useful in understanding the diversity of your product range and how it is categorized.
select id, title, updated_at, handlefrom shopify_smart_collectionwhere disjunctive;
select id, title, updated_at, handlefrom shopify_smart_collectionwhere disjunctive = 1;
Schema for shopify_smart_collection
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
body_html | text | Body HTML of the smart collection. | |
disjunctive | boolean | Whether the smart collection is disjunctive or not. | |
handle | text | The handle of the smart collection | |
id | bigint | = | The ID of the smart collection. |
image | jsonb | The image of the smart collection. | |
metafields | jsonb | Smart collection metafields. | |
published | boolean | Whether the smart collection is published or not. | |
published_at | timestamp with time zone | The time when the smart collection was published. | |
published_scope | text | Scope of the published smart collection. | |
rules | jsonb | Smart collection rules. | |
smart_collection_title | text | Title of the smart collection. | |
sort_order | text | A specific sort order for the smart collection. | |
template_suffix | text | The template suffix of the smart collection. | |
title | text | Title of the resource. | |
updated_at | timestamp with time zone | Time when the smart collection was last updated. |
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_smart_collection