CatalogItems

Retrieves detailed information about each item in a Square seller's catalog, including names, descriptions, pricing variations, availability, and location settings.

Table Specific Information

CatalogItems returns only the ITEM objects in the Square catalog. The driver restricts every request to that one catalog type, so each row is an item and each of the item's fields is available as its own typed column, such as Name, DescriptionHtml, Abbreviation, and Variations.

The Catalog table is the generic counterpart. It returns objects of every catalog type in a single result set, and rather than giving each type its own columns it carries the type specific fields as a JSON document in the column matching the row type: ItemData for an item, CategoryData for a category, TaxData for a tax, and so on. Use Catalog to search across types or when the type is not known in advance, and use CatalogItems or another type specific table, such as CatalogCategories or CatalogTaxes, to work with one type through individual columns. Catalog is read-only, while the type specific tables also support INSERT, UPDATE, and DELETE.

Because CatalogItems is restricted to ITEM objects, a search that matches only other catalog types returns no rows here. Run the same search against Catalog to see those matches.

Select

The driver uses the Square API to filter the results by the following columns and operators while the rest of the filter is executed client-side within the driver.

Id supports the following operators: =, IN.
IncludeDeletedObjects supports the following operator: =.
Query supports the following operator: =.
SearchText supports the following operators: =, IN.
UpdatedAt supports the following operator: >.

Retrieve all catalog items:

SELECT * FROM CatalogItems

Filter on Id to retrieve specific items. Id = fetches a single item directly. Id IN is served by the batch retrieve endpoint in a single request rather than one request per Id; the endpoint takes up to 1000 Ids per request, so a longer list is split across as many requests as needed.

SELECT Id, Name FROM CatalogItems WHERE Id = 'D7SQKLAOZB6626JJNN4OV3OS'

SELECT Id, Name FROM CatalogItems WHERE Id IN ('D7SQKLAOZB6626JJNN4OV3OS', '7BCOHGOQ3EVMTSE4TMBY3TOH')

UpdatedAt supports only the > operator, which Square applies as the start of the search window. Equality and the upper bound operators are not supported.

SELECT Id, Name, UpdatedAt FROM CatalogItems WHERE UpdatedAt > '2025-01-01T00:00:00Z'

Set IncludeDeletedObjects to true to return items that have been deleted from the catalog. A deleted item reports IsDeleted as true, and Square no longer returns its item data, so Name comes back null.

SELECT Id, Name, Version FROM CatalogItems WHERE IncludeDeletedObjects = true

SearchText searches the searchable attributes of an item, such as name, description, and SKU. Matching is case-insensitive, and keywords of fewer than three alphanumeric characters are ignored.

SELECT Id, Name FROM CatalogItems WHERE SearchText = 'TestName'

Use IN to supply up to three keywords. Square combines them with AND and matches them in any order. Square rejects a fourth keyword with the error ARRAY_LENGTH_TOO_LONG, reporting that the collection must have at most 3 items.

SELECT Id, Name FROM CatalogItems WHERE SearchText IN ('Test', 'Name')

The Query column takes a Square CatalogQuery object, supplied as JSON, and gives access to every search Square offers. The following search types are available.

exact_query matches an attribute against an exact value. Takes attribute_name and attribute_value.
prefix_query matches an attribute against a leading substring. Takes attribute_name and attribute_prefix.
range_query matches a numeric attribute against a range. Takes attribute_name, attribute_min_value, and attribute_max_value.
sorted_attribute_query orders the results by an attribute. Takes attribute_name, sort_order, and optionally initial_attribute_value.
set_query matches an attribute against any value in a set. Takes attribute_name and attribute_values.
items_for_tax_query returns the items that use the given taxes. Takes tax_ids.
items_for_modifier_list_query returns the items that use the given modifier lists. Takes modifier_list_ids.
items_for_item_options_query returns the items that use the given item options. Takes item_option_ids.
item_variations_for_item_option_values_query returns the item variations that carry the given item option values. Takes item_option_value_ids.
modifiers_for_child_list_query returns the modifiers that nest the given modifier lists, matching a modifier whose child_modifier_list_ids contains any of the supplied IDs. Takes child_modifier_list_ids. This search type is in beta.

exact_query, prefix_query, range_query, and sorted_attribute_query may be set together in a single CatalogQuery. Each of set_query, items_for_tax_query, items_for_modifier_list_query, items_for_item_options_query, item_variations_for_item_option_values_query, and modifiers_for_child_list_query must be the only search type in the object.

Do not set text_query in the Query column if SearchText is also set: both write to the same search, and driver rejects the combination with an error instead of merging them. Express the whole text_query search inside the Query value, or drop it and filter with SearchText alone.

Because this table returns only ITEM objects, a search type that yields another catalog type produces no rows here even when it matches. item_variations_for_item_option_values_query returns ITEM_VARIATION objects and modifiers_for_child_list_query returns MODIFIER objects, so run those against Catalog, or against CatalogItemVariations and CatalogModifiers. The same applies to an attribute search whose matches happen to be another type: range_query on ordinal, for instance, matches categories in a catalog that has no items with that ordinal.

Match an attribute exactly, or by prefix:

SELECT Id, Name FROM CatalogItems WHERE Query = '{"exact_query":{"attribute_name":"name","attribute_value":"TestName1"}}'

SELECT Id, Name FROM CatalogItems WHERE Query = '{"prefix_query":{"attribute_name":"name","attribute_prefix":"TestName"}}'

Match a numeric attribute against a range. The attribute_name must be an attribute Square indexes for search, such as ordinal; Square reports Unknown attribute definition name for anything it does not recognize.

SELECT Id, Name FROM CatalogItems WHERE Query = '{"range_query":{"attribute_name":"ordinal","attribute_min_value":0,"attribute_max_value":100}}'

Order the results by an attribute. Square preserves this ordering in the result set.

SELECT Id, Name FROM CatalogItems WHERE Query = '{"sorted_attribute_query":{"attribute_name":"name","sort_order":"ASC"}}'

Match an attribute against a set of values. In Catalog the result set is the union of the matches across every type; here it is narrowed to the items among them.

SELECT Id, Name FROM CatalogItems WHERE Query = '{"set_query":{"attribute_name":"name","attribute_values":["TestName1","Heavenly Strike"]}}'

The searches that return items are a natural fit for this table. Find the items that use a given tax, modifier list, or item option:

SELECT Id, Name FROM CatalogItems WHERE Query = '{"items_for_tax_query":{"tax_ids":["RBBPBDDSWHILRTI4VGPZJEZW"]}}'

SELECT Id, Name FROM CatalogItems WHERE Query = '{"items_for_modifier_list_query":{"modifier_list_ids":["NXSN3MCXIK7VKWGWPOS7DHA4"]}}'

SELECT Id, Name FROM CatalogItems WHERE Query = '{"items_for_item_options_query":{"item_option_ids":["K3C42O2ES4235E5XK3JHEJ6Q"]}}'

The remaining two search types return other catalog types, so query them through Catalog:

SELECT Id, Type FROM Catalog WHERE Query = '{"item_variations_for_item_option_values_query":{"item_option_value_ids":["L3JZSQKCIEQ5YPBQ7SIDIFAJ"]}}'

SELECT Id, Type FROM Catalog WHERE Query = '{"modifiers_for_child_list_query":{"child_modifier_list_ids":["NXSN3MCXIK7VKWGWPOS7DHA4"]}}'

Combine a filtering search with a sort in one CatalogQuery:

SELECT Id, Name FROM CatalogItems WHERE Query = '{"prefix_query":{"attribute_name":"name","attribute_prefix":"Test"},"sorted_attribute_query":{"attribute_name":"name","sort_order":"DESC"}}'

The filters can be combined in a single WHERE clause. The driver merges them into one request, with SearchText folded into the CatalogQuery next to whatever the Query column supplies.

SELECT Id, Name FROM CatalogItems WHERE SearchText = 'Test' AND Query = '{"sorted_attribute_query":{"attribute_name":"name","sort_order":"ASC"}}' AND IncludeDeletedObjects = true

Insert

To create an item, you will need to specify the Name and Variations columns. All the fields that are not read-only can be specified. You can supply a value to the Variations by passing aggregates or using #TEMP tables.

INSERT INTO CatalogItemVariations#TEMP (Name, PricingType) VALUES ('variationname1', 'VARIABLE_PRICING');
INSERT INTO CatalogItemVariations#TEMP (Name, PricingType) VALUES ('variationname2', 'VARIABLE_PRICING');
INSERT INTO CatalogItemVariations#TEMP (Name, PricingType) VALUES ('variationname3', 'VARIABLE_PRICING');
INSERT INTO CatalogItems (Name, DescriptionHtml, Variations) VALUES ('newname2', 'item desc', CatalogItemVariations#TEMP);

Creating an item, by specifying aggregate columns. You must supply the TemporaryItemId pseudo-column with the value of the 'item_id' field of the variations.

INSERT INTO CatalogItems (Name, DescriptionHtml, TemporaryItemId, Variations) VALUES ('newname', 'item desc', '#temporaryId', '[   {     "type": "ITEM_VARIATION",     "is_deleted": false,     "catalog_v1_ids": [       {         "catalog_v1_id": "2255feb6-157b-4d8c-b8da-79a30523c67c",         "location_id": "9J0M984DXGA7W"       }     ],     "present_at_all_locations": false,     "present_at_location_ids": [       "9J0M984DXGA7W"     ],  "id": "#red",   "item_variation_data": {        "item_id": "#temporaryId",       "name": "Small 55",       "sku": "1",       "ordinal": 1,       "pricing_type": "FIXED_PRICING",       "price_money": {         "amount": 200,         "currency": "USD"       },       "channels": [         "CH_0RauN49qbI5pcbqhVJDJi5H7qfkJuvBygSTD3QlQuYC"       ]     }   } ]');

Update

Modify the details of an existing catalog by providing the Id of the Items object:

UPDATE CatalogItems SET DescriptionHtml = 'TestDesc1', Name = 'TestName1', Abbreviation = 'TestAbbreviation1', AvailableForPickup = true WHERE ID = 'KAT75OWHFBAMA3GYS2LU32L5';

Updating the variation of an item:

INSERT INTO CatalogItemVariations#TEMP (Id, PricingType, PriceMoneyAmount, PriceMoneyCurrency, Version) VALUES ('QXSMA2IIPLOU4L5LNVSWCLD6', 'FIXED_PRICING', 10, 'USD', 1690797325339);
UPDATE CatalogItems SET Variations = 'CatalogItemVariations#TEMP', DescriptionHtml = 'TestDesc1', Name = 'TestName1', Abbreviation = 'TestAbbreviation1', AvailableForPickup = true WHERE ID = 'KAT75OWHFBAMA3GYS2LU32L5';

Delete

Delete an item by providing its Id:

DELETE FROM CatalogItems WHERE Id = '5XZRRMRESJ6N6CGQQ6BFA7AG'

Bulk Delete

The maximum BatchSize supported by CatalogItems for a delete is 200. Note that this is lower than the 1000 Ids a batch retrieve accepts, because Square caps its catalog batch delete separately.

Stage the Ids of the items to remove in a TEMP table, then delete them in a single request. Only the Id is needed on each staged row, and a row that supplies no Id is rejected before the request is sent.

INSERT INTO CatalogItems#TEMP (Id) VALUES ('D7SQKLAOZB6626JJNN4OV3OS')

INSERT INTO CatalogItems#TEMP (Id) VALUES ('7BCOHGOQ3EVMTSE4TMBY3TOH')

DELETE FROM CatalogItems WHERE EXISTS SELECT Id FROM CatalogItems#TEMP

A catalog batch delete succeeds even when only some of the Ids could be removed, and Square answers with the Ids it actually deleted. The driver reports the outcome of every staged Id against that list, so query LastResultInfo#TEMP after the delete to see which rows were removed and which were not.

SELECT * FROM LastResultInfo#TEMP

Note: Deleting a catalog object also removes the objects that belong to it, such as the variations of an item or the modifiers of a modifier list. An Id staged alongside its parent can therefore report that it was not deleted, because the parent's removal had already taken it.

Note: The staged form shown above is required to reach the bulk delete endpoint. A DELETE FROM CatalogItems WHERE Id = '...' statement removes the same item but issues one request per Id.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	False	The item's Id.
Name	String	False	The item's name.
UpdatedAt	Datetime	True	Date and time when the item was last modified.
IsDeleted	Boolean	False	If true, the object has been deleted from the database. When deleted, the UpdatedAt field will equal the deletion time.
DescriptionHtml	String	False	The item's description as expressed in valid HTML elements.
DescriptionPlaintext	String	True	A server-generated plaintext version of the description_html field, without formatting tags.
Abbreviation	String	False	The text of the item's display label in the Square Point of Sale app. Only up to the first five characters of the string are used.
LabelColor	String	False	The color of the item's display label in the Square Point of Sale app.
Categories	String	False	The list of categories.
TaxIds	String	False	A set of tax identifiers indicating the CatalogTaxes that are enabled for this item.
ModifierListInfo	String	False	Info about the modifier lists that apply to this item.
ProductType	String	False	The product type of the item. May not be changed once an item has been created.
SkipModifierScreen	Boolean	False	If true, the Square Point of Sale app will immediately add the item to the cart with the pre-selected modifiers, and merchants can edit modifiers by drilling down onto the item's details.
PresentAtAllLocations	Boolean	False	If true, this object is present at all locations (including future locations), except where specified in the AbsentAtLocationIds field. If false, this object is not present at any locations (including future locations), except where specified in the PresentAtLocationIds field. If not specified, defaults to true.
PresentAtLocationIds	String	False	A list of locations where the object is present, even if PresentAtAllLocations is false.
AbsentAtLocationIds	String	False	A list of locations where the object is not present, even if PresentAtAllLocations is true.
Version	Int64	False	The version of the object. When updating an object, the version supplied must match the version in the database, otherwise the write will be rejected as conflicting.
ImageIds	String	False	A list of image object identifiers that are linked to the item.
Variations	String	False	It represents a product for sale in the Catalog object model. While updating, provide the whole Variations aggregate object otherwise it will override with the new value.
CreatedAt	Datetime	True	Date and time when the item was first created in the catalog.
CatalogV1Ids	String	True	The Connect v1 IDs for this object at each location where it is present, where they differ from the object's Connect V2 ID.
Channels	String	True	A list of identifiers representing channels, such as a Square Online site, where the item can be made visible or available.
EcomAvailable	Boolean	False	True if the item can be sold through Square’s eCommerce integrations.
EcomImageURIs	String	True	List of image URIs associated with the item for Square Online listings.
EcomVisibility	String	True	Specifies whether the item is visible to customers in the online store (for example, public or hidden).
IsTaxable	Boolean	False	Indicates whether the item is taxable (true) or non-taxable (false).
ItemOptions	String	False	List of item options identifiers for this item. Used to manage and group item variations in a specified order.
SortName	String	False	A name to sort the item by. If this name is unspecified, namely, the sort_name field is absent, the regular name field is used for sorting. Its value must not be empty.
IsArchived	Boolean	False	Indicates whether this item is archived (true) or not (false).
EcomSeoDataPageTitle	String	False	The SEO title used for the Square Online store.
EcomSeoDataPageDescription	String	False	The SEO title description for the Square Online store.
EcomSeoDataPagePermalink	String	False	The SEO title permalink for the Square Online store.
FoodAndBeverageDetailsCalorieCount	Integer	False	The calorie count (in the unit of kcal) for the FOOD_AND_BEV type of items.
FoodAndBeverageDetailsDietaryPreferences	String	False	The dietary preferences for the FOOD_AND_BEV item.
FoodAndBeverageDetailsIngredients	String	False	The ingredients for the FOOD_AND_BEV type item.
ReportingCategoryId	String	False	The Id of the object's reporting category.
ReportingCategoryOrdinal	Int64	False	The order of the object within the context of the reporting category.
BuyerFacingName	String	False	An override for the product name that is displayed to customers when purchasing the item.
KitchenName	String	False	A name to identify this item when displayed in customer-facing interfaces, such as a kitchen display. For example, a customer might order Breakfast Combo but the kitchen sees eggs + bacon. (Beta)
IsAlcoholic	Boolean	False	Indicates whether the item is alcoholic (true) or not (false).

Pseudocolumns

Pseudo column fields are used in the WHERE clause of SELECT statements (unless stated otherwise) and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
TemporaryItemId	String	When inserting variations using aggregates, supply it with the value of the 'item_id' of the variations.
IncludeDeletedObjects	Boolean	If true, deleted objects are included in results.
SearchText	String	A keyword to search for across the searchable attributes of a catalog object, such as name, description, and SKU. Matching is case-insensitive, and keywords of fewer than three alphanumeric characters are ignored. Use IN to supply up to three keywords, which are combined with AND in any order; Square rejects a fourth.
Query	String	A Square CatalogQuery object, supplied as JSON, giving full access to every search Square offers. Any of exact_query, prefix_query, range_query, sorted_attribute_query, and text_query may be set together. Each of set_query, items_for_tax_query, items_for_modifier_list_query, items_for_item_options_query, item_variations_for_item_option_values_query, and modifiers_for_child_list_query must be the only search set. This table returns only ITEM objects, so a search that matches other catalog types returns no rows. Do not set text_query here; this table builds it from the SearchText column.

JDBC Driver for Square