Exporting Data - Search Query Language Reference

The Elastic Path Import-Export tool relies on a special query language to locate the products, catalogs, and categories to be exported. This appendix describes the query language, including supported search fields and operators.

Query Format

A simple query has the following form:

FIND <objectType> WHERE <expression>

Where <objectType> is the type of object you want to retrieve. Supported values are:

• Product
• Configuration
• Promotion
• PriceList
• Promotion
• PriceListAssignment
• TaxJurisdiction
• TaxCode
• PaymentProviders
• Warehouse
• Store
• Customer
• CustomerProfileAttribute
• CmUser
• UserRole
• Catalog
• Category
• GiftCertificate
• ShippingRegion
• ShippingServiceLevel
• SavedCondition
• CmImportJob

note

The object type must match the exporter type specified in the export configuration file.

<expression> specifies the criteria that determines which objects to retrieve. An expression has the following form:

<field> <operator> <value>

Where <field> is the field that contains the values you want to compare. A field represents a common characteristic of the object. For example, if you want to look for products of a specific brand, you would include the BrandCode field in your query. The supported fields are described in Supported Fields further in this appendix. Not all object types have supported fields.

<operator> is the operator you are using to perform the comparison.

<value> is the literal value you want to compare to the field values.

note

The WHERE clause is optional. If omitted, all products are matched.

Example

The following query matches the product whose code is 10030205:

FIND Product WHERE ProductCode = '10030205'

In addition to searching for field values, you can also search for attribute values on products and categories. To search for a value in an attribute, the expression has the following form:

AttributeName{<attribute_name>} <operator> <value>

Where <attribute_name> is the name of a product attribute. For example, the following query matches all products that have the Header / Model attribute set to MX:

FIND Product WHERE AttributeName{Header / Model} = 'MX'

To search for a value in a product SKU attribute, use SkuAttributeName. As an example, the following query matches all products that have the Header / Model SKU attribute set to MX:

FIND Product WHERE SkuAttributeName{Header / Model} = 'MX'

note

Data policies or customer consents do not support any query format. You cannot import or export a specific customer consent or data policy.

Case Sensitivity

The following are case-sensitive:

• Keywords such as FIND, WHERE, AND, START, and AttributeName
• Field names
• Attribute names
• Currency codes (must be in upper case letters)

The following are not case-sensitive:

• Literal string values (on the right side of the comparison)
• Language codes (when querying on localized fields)

Data Types and Supported Operators

Every field and attribute has a data type. The data type determines what kind of data the field or attribute can contain. The following sections shows the data types and the operators supported for each one:

DateTime

The DateTime format is YYYY-MM-DDThh:mm:ss.

Supported Operators:

• = (equal)
• != (not equal)
• < (less than)
• =< (less than or equal)
• > (greater than)
• >= (greater than or equal)

Example: '2008-08-30T17:19:00', '2009-12-21T07:06:00'

Double

The decimal point is optional.

Supported Operators:

• = (equal)
• != (not equal)
• < (less than)
• =< (less than or equal)
• > (greater than)
• >= (greater than or equal)

Example: 100.00, 20199

Integer

Supported Operators:

• = (equal)
• != (not equal)
• < (less than)
• =< (less than or equal)
• > (greater than)
• >= (greater than or equal)

Example: 1

String

Strings must be surrounded by single quotes ('). If a string contains a single quote, it must be preceded by a backslash.

Supported Operators:

• = (equal)
• != (not equal)

Example: 'StringValue', 'Canon - Kit d\'accessoires pour appareil photo'

Supported Fields

Catalog Fields

Field	Description	Data Type	Localized	Field Parameters	Sample Query
CatalogCode	Catalog code	String	No	N/A	FIND Catalog WHERE CatalogCode = 'mobile'

Category Fields

The following fields can be used in category queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
CatalogCode	Catalog code	String	No	N/A	FIND Category WHERE CategoryCode = 'MobileGames'
CategoryCode	Category code	String	No	N/A	FIND Category WHERE CategoryName[en] = 'Mobile Games'
CategoryName	Category name	String	Yes	Locale code	FIND Category WHERE CatalogCode = 'mobile'

CmUser Fields

The following fields can be used in CmUser queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
Role	CM User’s assigned roles	String	No	N/A	FIND CmUser WHERE Role = 'CMUSER'

Customer Fields

The following fields can be used in Customer queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
SharedID	Shared identifier	String	No	N/A	FIND Customer WHERE SharedID = 'e27ffe24-1281-4841-bc78-d9006ae7771d'
CustomerType	Customer type (SINGLE_SESSION_USER, REGISTERED_USER, ACCOUNT)	Customer type enum	No	N/A	FIND Customer WHERE CustomerType = ACCOUNT
LastModifiedDate	Last modified date	Datetime	No	N/A	FIND Customer WHERE LastModifiedDate > '2020-01-01T00:00:00-08:00'

Order Fields

The following fields can be used in Order queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
OrderNumber	Order number	String	No	N/A	FIND Order WHERE OrderNumber = '20000'
Status	Order status	Order status enum (CREATED, IN_PROGRESS, PARTIALLY_SHIPPED, ONHOLD, COMPLETED, CANCELLED, FAILED)	No	N/A	FIND Order WHERE Status = COMPLETED
ExternalOrderNumber	Extermal order number	String	No	N/A	FIND Order WHERE ExternalOrderNumber = '200'
StoreCode	Store code	String	No	N/A	FIND Order WHERE StoreCode = 'MOBEE'
CustomerGUID	Customer GUID	String	No	N/A	FIND Order WHERE CustomerGUID = '80E0B57C-EEF0-646D-105E-056DD9C1D25D'
AccountGUID	Account GUID	String	No	N/A	FIND Order WHERE AccountGUID = '80E0B57C-EEF0-646D-105E-056DD9C1D25D'
CreatedDate	Created date	DateTime	No	N/A	FIND Order WHERE CreatedDate > '2020-01-01T00:00:00-08:00'
LastModifiedDate	Last modified date	DateTime	No	N/A	FIND Order WHERE LastModifiedDate > '2020-01-01T00:00:00-08:00'

PriceListAssignment Fields

The following fields can be used in PriceListAssignment queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
CatalogCode	Catalog code	String	No	N/A	FIND PriceListAssignment WHERE CatalogCode = 'mobile'

PriceList Fields

The following fields can be used in PriceList queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
PriceListName	The name of the price list	String	No	N/A	FIND PriceList WHERE PriceListName = 'Mobee Scope Price List'

Product Fields

The following fields can be used in Product queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
AttributeName	Product attribute name	String	No	Attribute key	FIND Product WHERE AttributeName{rating} = '4'
BrandCode	Brand code	String	No	N/A	FIND Product WHERE BrandCode = 'Jawbone'
BrandName	Brand name	String	Yes	Locale code	FIND Product WHERE BrandName[en_US] = 'Jawbone'
CatalogCode	Catalog code	String	No	N/A	FIND Product WHERE CatalogCode = 'MobileCatalog
CategoryCode	Category code	String	No	N/A	FIND Product WHERE CategoryCode = 'MobileGames'
CategoryName	Category name	String	Yes	Locale code	FIND Product WHERE CategoryName[en] = 'Mobile Games'
ProductEndDate	Date when the product becomes unavailable	Datetime	No	N/A	FIND Product WHERE ProductEndDate > '2018-01-01T00:00:00-08:00'
ProductStartDate	Product availability start date	Datetime	No	N/A	FIND Product WHERE ProductStartDate < '2012-01-01T00:00:00-08:00'
LastModifiedDate	Product last modified date	Datetime	No	N/A	FIND Product WHERE LastModifiedDate > '2020-01-01T00:00:00-08:00'
ProductActive	Product active	String	No	N/A	FIND Product WHERE ProductActive = false
ProductCode	Product code	String	No	N/A	FIND Product WHERE ProductCode = 'tt0034583'
ProductName	Product name	String	Yes	Locale code	FIND Product WHERE ProductName[en] = 'Samsung Galaxy Q'
SkuAttributeName	SKU attribute name	String	No	Attribute key	FIND Product WHERE SkuAttributeName{format} = 'widescreen'
SkuCode	Product SKU code	String	No	N/A	FIND Product WHERE SkuCode = 'tt0114369_hdb'
StoreCode	Store code	String	No	N/A	FIND Product WHERE StoreCode = 'mobee'

ProductAssociation Fields

The following fields can be used in ProductAssociation queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
SourceProductCode	Source product code	String	No	N/A	FIND ProductAssociation WHERE SourceProductCode = 'multipleAssociationsSourceProduct'
TargetProductCode	Target product code	String	No	N/A	FIND ProductAssociation WHERE TargetProductCode = 'associatedProductOne'
AssociationType	Association type	Association type enum	No	N/A	FIND ProductAssociation WHERE AssociationType = CROSS_SELL
CatalogCode	Catalog code	String	No	N/A	FIND ProductAssociation WHERE CatalogCode = 'MobileCatalog

Promotion Fields

The following fields can be used in Promotion queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
CatalogCode	Catalog code	String	No	N/A	FIND Promotion WHERE CatalogCode = 'mobile'
PromotionName	Promotion name	String	No	N/A	FIND Promotion WHERE PromotionType = 'Catalog'
PromotionType	Promotion type (ShoppingCart or Catalog)	String	No	N/A	FIND Promotion WHERE PromotionName = '10 Dollars Off'
State	The state of the promotion (ACTIVE, EXPIRED, or DISABLED)	Constant	No	N/A	FIND Promotion WHERE State = ACTIVE
StoreCode	Store code	String	No	N/A	FIND Promotion WHERE StoreCode = 'mobee'

Store Fields

The following fields can be used in Store queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
StoreCode	Catalog code	String	No	N/A	FIND Store WHERE StoreCode = 'mobee'

Configuration Fields

The following fields can be used in Configuration queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
Namespace	Setting path	String	No	N/A	FIND Configuration WHERE Namespace = 'COMMERCE/SYSTEM/ASSETS/assetLocation'
Context	Context value	String	No	N/A	FIND Configuration WHERE Namespace = 'COMMERCE/STORE/listPagination' AND Context='MOBEE'

TaxJurisdiction Fields

The following fields can be used in TaxJurisdiction queries:

Field	Description	Data Type	Localized	Field Parameters	Sample Query
TaxJurisdictionCode	Name of the tax code. For example, Shipping and Goods.	String	No	N/A	FIND TaxJurisdiction WHERE TaxJurisdictionCode = 'CATaxJurisdiction'
TaxJurisdictionRegion	Name of the tax region, which is usually the province/state two letter code. For example British Columbia is "BC".	String	No	N/A	FIND TaxJurisdiction WHERE TaxJurisdictionRegion = 'BC'

Localized Fields and Attributes

Some fields contain localized values. To include a localized field in your query, you must specify the language that you want to search. For example, the following query matches the product whose French name is Canon - Kit d’accessoires pour appareil photo:

FIND Product WHERE ProductName[fr] = 'Canon - Kit d\’accessoires pour appareil photo'

The value between the square brackets indicates the language. You can use either the two-letter language code or the full language name.

note

The apostrophe in d’accessoires must be preceded by a backslash. Whenever a string contains the apostrophe character, it must be escaped with a backslash. Otherwise it will be interpreted as the end of the string and cause a parsing error when the query is validated.

Attributes may also contain localized values. For example, the following query matches all products that have the English value of the Lens System / Type attribute set to Zoom lens:

FIND Product WHERE AttributeName{Lens System / Type}[en] = 'Zoom lens'

System Configuration Setting Metadata

To query for settings with specific metadata values, you use the MetadataKey keyword followed by curly braces. Put the metadata key within the curly braces.

When querying for metadata, you can use the * wildcard to look for all settings that have a particular metadata key, regardless of the value assigned to it. For example, the following query matches all settings that have a value associated with the refreshStrategy metadata key.

FIND Configuration WHERE MetadataKey{refreshStrategy} = *

The following query produces the same results:

FIND Configuration WHERE MetadataKey{refreshStrategy} != ''

The Setting Namespace Field

System configuration settings in Elastic Path are identified by a path-like structure. For example, the store theme setting is identified by COMMERCE/STORE/theme.

The path-like structure is intended to allow hierarchical setting definitions. Note that this is a purely logical organization and does not represent how settings are physically stored; settings are stored in a single, non-hierarchical relation in the database.

To query for a setting by its path, use the Namespacefield. The following query matches the COMMERCE/STORE/theme setting:

FIND Configuration WHERE Namespace = 'COMMERCE/STORE/theme'

You can also specify partial paths. To match all settings that begin with a particular path, you must specify the path up to and including the slash character.

For example, the following query matches all settings whose path begins with COMMERCE/STORE:

FIND Configuration WHERE Namespace = 'COMMERCE/STORE/'

In this case, there are 17 settings that match this partial path.

For a more refined search, you can use the % character in the last part of the path. The following query matches all settings immediately under COMMERCE/STORE that begin with the letter "s".

FIND Configuration WHERE Namespace = 'COMMERCE/STORE/s%'

This matches the two immediate children of COMMERCE/STORE that begin with "s": COMMERCE/STORE/storefrontUrl and COMMERCE/STORE/seoEnabled.

Combining Expressions

You can use AND or OR to combine multiple expressions.

For example, the following query uses AND to match all Kodak zoom lens items (based on the value of the Lens System / Type attribute).

FIND Product WHERE AttributeName{Lens System / Type}[en] = 'Zoom lens' AND BrandName[en] = 'Kodak'

The following query uses OR to match all Pentax and Kodak products:

FIND Product WHERE BrandName[en] = 'Pentax' OR BrandName[en] = 'Kodak'

You can use parentheses (the ( and ) characters) to set the order in which expressions are evaluated. Expressions in parentheses are evaluated first. You can nest expression groups.

Limiting the Result Set Size

You can limit the number of results returned by adding LIMIT <number>, where <number> specifies the maximum number of items to include in the results. For example, to return the first ten Pentax products, execute the following:

FIND Product WHERE BrandName[en] = 'Pentax' LIMIT 10

Specifying the First Match

You can specify the position of the first match to return within the results by adding START <number>, where <number> is the position of the first match you want to return. For example, the following query returns the first ten matches starting at the twentieth match:

FIND Product WHERE BrandName[en] = 'Pentax' LIMIT 10 START 20

note

Currently, it is not possible to sort results. This is primarily used in search queries executed by the Import-Export tool to split result sets into more manageable "chunks". For example, the following query returns 274 matches for the Snap It Up master catalog:

FIND Product WHERE BrandName[en] = 'Pentax'

You can split those matches into three separate result sets by executing the following three queries:

FIND Product WHERE BrandName[en] = 'Pentax' LIMIT 100
FIND Product WHERE BrandName[en] = 'Pentax' START 101 LIMIT 100
FIND Product WHERE BrandName[en] = 'Pentax' START 201