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

The CatalogCode field is the only field supported in catalog queries.

Category Fields

The following fields can be used in category queries:

Field Name	Description	Data Type	Localized
CatalogCode	Catalog code	String	No
CategoryCode	Category code	String	No
CategoryName	Category name	String	Yes

CmUser Fields

The following fields can be used in CmUser queries:

Field Name	Description	Data Type	Localized
Role	CM User’s assigned roles	String	No

PriceListAssignment Fields

The following fields can be used in PriceListAssignment queries:

Field Name	Description	Data Type	Localized
CatalogCode	Catalog code	String	No

PriceList Fields

The following fields can be used in PriceList queries:

Field Name	Description	Data Type	Localized
PriceListName	The name of the price list	String	No

Product Fields

The following fields can be used in product queries:

Field Name	Description	Data Type	Localized
AttributeName	Product attribute name	String	No
BrandCode	Brand code	String	No
BrandName	Brand name	String	Yes
CatalogCode	Catalog code	String	No
CategoryCode	Category code	String	No
CategoryName	Category name	String	Yes
Price	Price of the product	Float	No
ProductEndDate	Date when the product becomes unavailable	Datetime	No
ProductStartDate	Product availability start date	Datetime	No
LastModifiedDate	Product last modified date	Datetime	No
ProductActive	Product active	String	No
ProductCode	Product code	String	No
ProductName	Product name	String	Yes
SkuAttributeName	SKU attribute name	String	No
SkuCode	Product SKU code	String	No
StoreCode	Store code	String	No

Promotion Fields

The following fields can be used in promotion queries:

Field Name	Description	Data Type	Localized
StoreCode	Store code	String	No
PromotionName	Promotion name	String	No
PromotionType	Promotion type (ShoppingCart or Catalog)	String	No
State	The state of the promotion (ACTIVE, EXPIRED, or DISABLED)	Constant	No

Store Field

The following fields can be used in store queries:

Field Name	Description	Data Type	Localized
StoreCode	Catalog code	String	No

System Configuration Setting Fields

The following fields can be used in setting queries:

Field Name	Description	Data Type	Localized
Namespace	Setting path	String	No
Context	Context value	String	No

TaxJurisdiction Fields

The following fields can be used in TaxJurisdiction queries:

Field Name	Description	Data Type	Localized
TaxJurisdictionCode	Name of the tax code. For example, Shipping and Goods.	String	No
TaxJurisdictionRegion	Name of the tax region, which is usually the province/state two letter code. For example British Columbia is "BC".	String	No

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 apiRefreshStrategy metadata key.

FIND Configuration WHERE MetadataKey{apiRefreshStrategy} = *

The following query produces the same results:

FIND Configuration WHERE MetadataKey{apiRefreshStrategy} != ''

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