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:
ProductConfigurationPromotionPriceListPromotionPriceListAssignmentTaxJurisdictionTaxCodePaymentGatewayWarehouseStoreCustomerCustomerProfileAttributeCmUserUserRoleCatalogCategoryGiftCertificateShippingRegionShippingServiceLevelSavedConditionCmImportJob
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, andAttributeName - 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