Ingest Product index pipeline

The complete data mappings from specification, database and schema are shown for the product category.

Product data specification

Data Field​ Name Data Type Data Value
/uniqueId long ​The Commerce catalog entry internal identifier.
/identifier string The external catalog entry identifier.
​/type ​string ​The catalog entry type - ProductBean, ItemBean, PackageBean, BundleBean.
​/manufacturerIdentifier ​string The manufacturer name.
​/buyable ​boolean Indicates whether this catalog entry can be purchased individually. ​Defaults to false.
​/availabilityDate ​date The start date when this catalog entry becomes available.​
​/prices/value ​string ​Value of one of the prices.
​/prices/currency​ ​string ​Currency of this price.
​/prices/usage ​string​ List price or offer price.
​​/items/uniqueId ​long ​The Commerce catalog entry internal identifier of its children.
​/items/identifier ​string ​The external catalog entry identifier of this child entry.
​/items/sequence ​float ​The sequence of this child entry listed within its parent.
/​components/uniqueId ​long ​The Commerce catalog entry internal identifier of its sub-components.
​/components/identifier ​string ​The external catalog entry identifier of this sub-component.
​/components/type ​string ​One of the catalog entry types: Product, Item, Package, Bundle.
​/components/quantity ​float ​A quantity that can be associated with the relationship.
​/parentCategories/parentCategoryId ​long ​The Commerce category internal identifier of the parent category of this catalog entry.
​/parentCategories/categoryIdentifier ​string ​The external category identifier of this parent category.
​/parentCategories/catalogId ​long ​The Commerce catalog internal identifier. If none is specified, the store default catalog will be used.
/parentCategories/​catalogIdentifier ​string ​The external catalog identifier for this parent category.
​/parentCategories/sequence ​float ​The sequence number used to determine the display order within this parent category.
​/descriptions/languageId ​integer The Commerce language internal identifier. If not specified, “locale” will be used. If “locale” is not specified, then the store default language will be used.​
​/descriptions/locale ​string ​The locale identifier.
​/descriptions/name ​string ​The language dependent name of this catalog entry.
​/descriptions/description ​string ​The short description of this catalog entry.
​/descriptions/longDescription ​string ​The long description of this catalog entry.
​/descriptions/keywords ​string ​A list of keywords used for searching.
​/descriptions/thumbnail ​string ​The thumbnail image path of this catalog entry.
​/descriptions/fullImage ​string ​The full image path of this catalog entry.
​/descriptions/published ​boolean Indicates whether this catalog entry should be displayed for the current language. ​Defaults to false.
​/descriptions/sequence ​float The order of ​this document in the current category.
​/attributes/uniqueId ​long ​The Commerce attribute internal identifier.
​/attributes/identifier ​string ​The external identifier of this attribute.
​/attributes/usage ​string
​/attributes/sequence ​float ​The display order of attributes in an attribute group or in the root of the attribute dictionary.
​/attributes/descriptions/languageId​ ​integer ​The internal language identifier for this attribute description.
​/attributes/descriptions/locale ​string ​The locale of this attribute description.
​/attributes/descriptions/name ​string ​The language dependent name of this attribute.
​/attributes/descriptions/description ​string ​The language dependent short description of this attribute.
​/attributes/descriptions/thumbnail ​string ​The path of the thumbnail image of this attribute.
​/attributes/values/uniqueId ​string ​The Commerce internal identifier for this attribute value.
​/attributes/values/identifier ​string ​The external identifier for this attribute value.
​/attributes/values/unitId ​string ​The Commerce internal identifier for the units in which this attribute value is measured.
​/attributes/values/descriptions/languageId ​integer ​The internal language identifier for this attribute value description.
​/attributes/values/descriptions/locale ​string ​The locale of this attribute value description.
​/attributes/values/descriptions/value ​string ​The value of this attribute.
​/attributes/values/descriptions/thumbnail ​string ​The path of the thumbnail image of this attribute value.
​/attributes/values/descriptions/sequence ​float ​A number that determines the display order of a list of allowable attribute values for a given attribute.

Product index field mapping from data specification

The following diagrams illustrate the Product indexing pipeline implemented in Apache NiFi. The flow consists of mainly four stages:
  1. Loading product document
  2. Pushing down product level properties to its items
  3. Rolling up item level properties to its parent
  4. Building catalog hierarchy and navigation paths for each sales category
Note: ​​​Definition for field type aliases (in it​​​alics) is described in Index Field Type Aliase​​​s and Usages page.​​
Stage 1​​​: Loading product document
​​​This stage describes how product data can be transformed and loaded into the Product index using the CreateProductDocument Groovy script.
Important: Groovy is only supported for prototyping. For production environments, only the Java ETL is supported.
The following mapping​ table defines how data from the Product data specification can be mapped into the Product index schema in Elasticsearch:​
Index Field​ Name​ ​Index Field Type ​​How Value Can Be Assigned
​_id ​internal identifier/specification + id/store + id/language + id/catalog + id/catentry
​__meta/version/min ​integer ​Set by InjectMetaData processor
​__meta/version/max ​integer Same as above
​​__meta/created ​date ​Same as above
__meta/modified​ ​date Same as above
​id/store id_integer ​Set by ​InjectMetaData processor
id/​language id_integer ​Assigned directly from data field "languageId"
id/​catalog id_long ​Set by ​InjectMetaData processor
id/​catentry id_long ​Assigned directly from data field "uniqueId"
​id/member ​​id_long
​identifier/specification id_string Always set to "​product"
​identifier/​store id_string ​Set by ​InjectMetaData processor
identifier/​language id_string ​Extracted from only the language part of the data field "locale"
​identifier/catalog id_string Set by ​InjectMetaData processor
​identifier/sku/raw raw Assigned directly from data field "identifier"
​identifier/sku/normalized normalilzed Same as above​
​name/raw raw ​Assigned directly from data field "name"
​​name/normalized normalized ​Same as above
​name/text ​text ​Same as above
​​description/raw raw Assigned directly from data field "description"​
​description/text ​text ​Same as above
​description/long raw ​Assigned directly from data field "longDescription"
​keyword/text ​text Assigned directly from data field "keyword"​
​manufacturer/raw raw ​Assigned directly from data field "manufacturer"
manufacturer/normalized normalized ​Same as above
​type id_string ​product, item, package, bundle
​buyable ​boolean ​Assigned directly from data field "buyable"
​displayable ​boolean ​Assigned directly from data field "published" of current language
​start date​ ​Assigned directly from data field "startDate"
​end ​date ​Assigned directly from data field "endDate"
url/​thumbnail ​raw ​Assigned directly from data field "thumbnail"
url/​image ​raw ​Assigned directly from data field "fullimage"
url/​​seo ​raw ​TBD
kit/​components ​keyword ​TBD
kit/​preconfigured ​integer ​TBD
kit/model ​keyword ​TBD
​kit/configurable ​integer ​TBD
kit/​parent/model ​keyword ​TBD
kit/parent/configurable ​integer ​TBD
kit/​parent/id ​long ​TBD
kit/URL keyword ​TBD
kit/default_configuration ​keyword ​TBD
kit/​​pdks ​keyword ​TBD
​correction ​text​ ​Use term suggester on this field at query time
completion ​completion
​suggestion​ ​search_as_you_type copied from name, ​​​​​shortDescription, unstructured, partNumber​​​​​​
​defaultSearch ​text
​prices/offer_<currency>[_<contract>] ​float ​Dynamic field assigned directly from data field "prices" with "Offer" usage
prices/list_<currency>[_<contract>] ​float ​Dynamic field assigned directly from data field "prices" with "Display" usage
​sequences/cmc_<parentCategoryId> ​float ​Assigned from data field "sequence" in parentCategories, with field name ends with the identifier of the parent category
​​attributes/<identifier>/number ​float ​Dynamic field assigned directly from its parent's descriptive attributes populated in Stage 1
​​attributes/<identifier>/date ​date Same as above
​attributes/<identifier>/raw ​raw Same as above
​attributes/<identifier>/text ​text ​Same as above
relationship/name join ​One way relationship. Supported values: product, item, package, bundle, variant​
​​relationship/parent ​id_long Same as below
​​relationship/sequence ​float ​Assigned from data field "sequence" in "items"; only applies to items
​relationship/child​​/id ​id_long Assigned from data field "uniqueId" in "items"
​​relationship/child/sequence ​float ​Assigned from data field "sequence" in "items"; only applies to items
​category/name ​text ​Name of the parent category, looked up from category index
​category/catalog ​​id_long ​Assigned from data field "catalogId" in "parentCategories"
​​category/parent ​id_long ​Assigned from data field "parentCategoryId" in "parentCategories"​
category/sequence​​ ​float ​Assigned from data field "sequence" in "parentCategories"
Stage 2​​​: Pushing down product level properties to its items
This stage describes how properties and attributes of a product entry can be pushed down to all its child items in the Product index.
It starts with running the following Elasticsearch query agsint the Product index:​

	  {
		"stored_fields": [
		  "id.*",
		  "relationship.*",
		  "attributes.*"
		],
		"size": "10000",
		"_source": false,
		"query": {
		  "bool": {
			"must": {
			  "match_all": {}
			},
			"filter": [
			  {
				"term": {
				  "relationship.name": "product"
				}
			  },
			  {
				"term": {
				  "id.store": "${param.storeId}"
				}
			  },
			  {
				"term": {
				  "id.catalog": "${param.catalogId}"
				}
			  },
			  {
				"term": {
				  "id.language": "${param.langId}"
				}
			  }
			]
		  }
		}
	  }
Next, the result set is passed to the PushDownParentProperties Groovy script for transformation:
{ ​"script" : {
    "source" : "ctx._source.path = params.value",
    "params" : {
      "value" : "/1"
    },
    "lang" : "painless"
  },
  "query" : {
    "term" : {
      "category.parent" : "1"
    }
  }
}​​
Stage 3​​​: Rolling up item level properties to its parent
This stage describes how properties and attributes of a product entry can be pushed up to its parent product in the Product index.
You begin the process by running this Elasticsearch query agsint the Product index:​
  {
	"stored_fields": [
	  "id.*",
	  "attributes.def_*",
	  "relationship.*"
	],
	"size": "10000",
	"_source": false,
	"query": {
	  "bool": {
		"must": {
		  "match_all": {}
		},
		"filter": [
		  {
			"term": {
			  "relationship.name": "item"
			}
		  },
		  {
			"term": {
			  "id.store": "${param.storeId}"
			}
		  },
		  {
			"term": {
			  "id.catalog": "${param.catalogId}"
			}
		  },
		  {
			"term": {
			  "id.language": "${param.langId}"
			}
		  }
		]
	  }
	}
  }
Next, the result set is passed to the RollUpChildProperties Groovy script for transformation.
Note: The initial result set from the above search query will return all Defining attributes at the item level. These attributes will then be copied into their respective parent.​
Stage 4​​​: Building catalog hierarchy for navigation
This stage describes how the Navigation data can be generated and loaded into the Product index.
It starts with running the following Elasticsearch query agsint the Category index:​
  {
	"stored_fields": [
	  "id.*",
	  "path*"
	],
	"size": "10000",
	"_source": false,
	"query": {
	  "bool": {
		"must": {
		  "match_all": {}
		},
		"filter": [
		  {
			"term": {
			  "id.store": "${param.storeId}"
			}
		  },
		  {
			"term": {
			  "id.catalog": "${param.catalogId}"
			}
		  },
		  {
			"term": {
			  "id.language": "${param.langId}"
			}
		  }
		]
	  }
	}
  }​
Next, the result set is passed to the BuildCatalogHierarchyForProduct Groovy script for transformation and use the following logics to generate the navigation tree.
Note: The initial result set from the above search query will return all categories of the given sales catalog. Assuming there can only be one navigation path to each sales category, the following painless script will be generated to assign the current category's navigation path to all its child products and items:
{
  "script" : {
    "source" : "ctx._source.path = params.value",
    "params" : {
      "value" : "/1"
    },
    "lang" : "painless"
  },
  "query" : {
    "term" : {
      "category.parent" : "1"
    }
  }
}​
Index Field​ Name​ ​Index Field Type ​​​Description
​path ​​text ​Tokenized field for the full navigation path to its parent category node in the sales catalog
path/tree hierarchy ​e.g. /apparel/women/dresses, /apparel/women, /apparel
​path/reversed hierarchy_reversed ​e.g. /dresses, /women/dresses, /apparel/women/dresses

Product index field mapping from database

​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​The following sequence of steps ​illustrates the Product indexing pipeline implemented in Apache NiFi. The flow consists of mainly twelve stages:
  1. Creating a Product document
  2. Applying Site level override
  3. Associating subscriptions
  4. Associating parent and child product relationship
  5. Applying product set relationship for contract entitlement
  6. Generating merchandising association
  7. Associating product attachments and angle images
  8. Building catalog hierarchy for navigation
  9. Associating attributes
  10. Rolling up item level attributes to their parents
  11. Pushing down product level properties to their items
  12. Associating child descriptive attributes
  13. Associating child attachments

Stage 1: Creating Product document

This stage describes how the Product data can be transformed and loaded into the Product index.

The above dataflow describes how the Product data can be transformed and loaded into the Product index. It starts with running the following SQL to retrieve Product data from the Commerce database:
​SELECT C.CATENTRY_ID, C.MEMBER_ID, C.CATENTTYPE_ID, C.PARTNUMBER, C.MARKFORDELETE, C.BUYABLE, C.STATE,
	   COALESCE(D.LANGUAGE_ID, L.LANGUAGE_ID) LANGUAGE_ID,
           COALESCE(C.MFPARTNUMBER, VP.MFPARTNUMBER) MFPARTNUMBER,
           COALESCE(C.MFNAME,VP.MFNAME) MFNAME,
           COALESCE(C.STARTDATE,VP.STARTDATE) STARTDATE,
           COALESCE(C.ENDDATE,VP.ENDDATE) ENDDATE,
           COALESCE(D.NAME, PD.NAME) NAME,
           COALESCE(D.SHORTDESCRIPTION, PD.SHORTDESCRIPTION) SHORTDESCRIPTION,
           COALESCE(D.KEYWORD, PD.KEYWORD) KEYWORD,
           D.THUMBNAIL, D.FULLIMAGE, D.PUBLISHED, 
	   R.CATALOG_ID, L.LOCALENAME, S.STOREENT_ID, PD.THUMBNAIL PARENT_THUMBNAIL, PD.FULLIMAGE PARENT_FULLIMAGE
     FROM CATGPENREL R, LANGUAGE L, STORECAT S, CATENTRY C
     ${TI_DELTA_JOIN_QUERY}
     LEFT OUTER JOIN CATENTDESC D ON (D.CATENTRY_ID=C.CATENTRY_ID AND D.LANGUAGE_ID = ${param.langId})
     LEFT OUTER JOIN CATENTREL PV ON (C.CATENTRY_ID = PV.CATENTRY_ID_CHILD AND PV.CATRELTYPE_ID IN ('PRODUCT_VARIANT', 'PRODUCT_ITEM')
                                                                                  AND PV.CATENTRY_ID_CHILD <> PV.CATENTRY_ID_PARENT)
     LEFT OUTER JOIN CATENTRY VP ON (VP.CATENTRY_ID = PV.CATENTRY_ID_PARENT)
     LEFT OUTER JOIN CATENTDESC PD ON (PV.CATENTRY_ID_PARENT=PD.CATENTRY_ID AND PD.LANGUAGE_ID = ${param.langId})
     WHERE R.CATALOG_ID = ${param.catalogId}
           AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
               (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
           AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0
           AND S.CATALOG_ID = R.CATALOG_ID AND L.LANGUAGE_ID = ${param.langId} ${extCatentryAndSQL}
         ORDER BY CATENTRY_ID
            OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY      
Next, the result set is passed to the CreateProductDocumentFromDatabase processor for transformation, using the following table to ​map the database field returned from the SQL above to an index field in the Product index:​​
​​Index Field​ Name​ ​Index Field Type ​​Description
​​Document Identifier​​
​id/store id_string Internal id of the owning store; mapped to table STORECENT
id/​language id_string The identifier of the language​; mapped to CATENTDESC​.LANGUAGE_ID
id/​catalog id_string ​Catentry's parent catalog; mapped to table CATGPENREL
id/​catentry id_string ​Catentry's basic attributes; mapped to CATENTRY.CATENTRY_ID
​id/member ​​id_string The reference number that identifies the owner of the catalog entry; mapped to CATENTRY​​​.MEMBER_ID
​​identifier/specification id_string Set to "​product"
identifier/​store id_string ​A string that uniquely identifies the owning store; mapped to table STOREENT​
identifier/​language id_string The language locale of this catalog entry; mapped from CATENTDESC.LANGUAGE_ID
​​identifier/catalog id_string ​The external identifier of the catalog; mapped to CATGPENREL
​identifier/sku/raw raw The SKU of the catalog entry​​; mapped to CATENTRY.PARTNUMBER
​identifier/sku/normalized normalilzed Same as above​
​identifier/pn/raw raw The reference number that identifies the part number of the catalog entry​​; mapped to CATENTRY.PARTNUMBER
​identifier/pn/normalized normalilzed Same as above​
​identifier/mpn/raw raw The reference number that identifies the manufacturer part number of the catalog entry​​; mapped to CATENTRY.MFPARTNUMBER
​identifier/mpn/normalized normalilzed Same as above​
Language Sensitive Data​​​​​
​name/raw raw The language-dependent name of this catalog entry; mapped to CATENTDESC​​.NAME
​​name/normalized normalized ​Same as above
​name/text ​text ​Same as above
​keyword/text ​text A keyword used for searching​; mapped to CATENTDESC​​​​.KEYWORD
url/​thumbnail ​raw The thumbnail image path of this catalog entry​; mapped to CATENTDESC​.THUMBNAIL
url/​image ​raw The full image path of this catalog entry​; mapped to CATENTDESC​​.FULLIMAGE
url/​​seo ​raw
Properties​​​
​manufacturer/raw raw The name of the manufacturer of this catalog entry; mapped to ​CATENTRY​.MFNAME
manufacturer/normalized normalized ​Same as above
manufacturer/text text​ ​Same as above
​type id_string ​product, item, package, bundle, variant; mapped to CATENTRY.CATENTTYPE_ID
​buyable ​boolean Indicates whether this catalog entry can be purchased individually​; mapped to ​CATENTRY​​​.BUYABLE
​displayable ​boolean ​Indicates whether this catalog entry should be displayed for the language ; mapped to CATENTDESC.PUBLISHED
​start date​ ​The date when this catalog entry is introduced; mapped to ​CATENTRY​​.STARTDATE
​end ​date The date when this catalog entry is withdrawn; mapped to ​CATENTRY​​.ENDDATE
​state ​boolean ​The state of a catentry; mapped to CATENTRY.STATE
​Natural Language​​​​​
​natural/nouns/raw​ ​raw ​This field contains nouns extracted from indexed fields in this current document for use with Natural Language search
​natural/nouns/normalized ​normalized Same as above
​natural/adjectives/raw raw ​This field contains all non-numeric values from searchable atttributes of the current document
​​natural/adjectives/normalized normalized Same as above
​natural/names/raw raw ​This field contains names extracted from the current document using the Stanford name-entity-extractor
​natural/names/normalized normalized Same as above
​natural/categories/raw ​raw ​This field contains a list of normalized category names copied from category/name field
​natural/categories/normalized ​normalized Same as above
​natural/locations​/raw ​raw ​This field contains any location specific name extracted by the Stanford part-of-speech tagger
​natural/locations​/normalized ​normalized Same as above
​​natural/intents/raw ​raw ​​This field contains potential intents that can be used for matching with shopper's intent at query time
​​natural/intents/normalized ​normalized Same as above
​Navigational Data​​​​
relationship/name keyword ​One way relationship. Supported values: product, item, package, bundle, variant​
​relationship/product/id ​id_string ​Catentry's parent product; mapped​ to table CATENTREL
​relationship​​/product/sequence ​float ​​Catentry's display sequence; mapped to table: CATENTREL
​relationship/variant/id ​id_string ​Variant's parent product; mapped to table: CATENTREL
​relationship​​/variant/sequence ​float Variant's display sequence; mapped to table: CATENTREL
​​relationship/item/id ​id_string Catentry's child catentry; mapped to table: CATENTREL
​relationship​​/item/sequence ​float Catentry's display sequence; mapped to table: CATENTREL​
Auto-Completion and Spell Correction
default/​correction ​text​ ​Use term suggester on this field at query time to provide term based spell correction function. Reason of having a separate dedicated field for term suggestion is to allow different terms to be suggested. Re-using the default search field cannot provide this level of fine grain control. By default, only name, description, and keyword are copied into this field.
default/completion ​completion By feeding a list of ​​​all possible comp​​letions, and the completion suggester builds them into a finite state transducer, an optimized data structure that resembles a big graph. To search for suggestions, Elasticsearch starts at the beginning of the graph and moves character by character along the matching path. Once it has run out of user input, it looks at all possible endings of the current path to produce a list of suggestions. This data structure lives in memory and makes prefix lookups extremely fast, much faster than any term-based query could be​. It is an excellent match for autocompletion of names and brands, whose words are usually organized in a common order. When word order is less predictable, edge n-grams (search_as_you_type) can be a better solution than the completion suggester. By default, only name, description, and keyword are copied into this field.
default/​suggestion ​search_as_you_type ​The search_as_you_type field type is a text-like field that is optimized to provide queries that serve an as-you-type completion use case. It creates a series of (n-gram) subfields that are analyzed to index terms that can be efficiently matched by a query that partially matches the entire indexed text value. Both prefix completion (i.e matching terms starting at the beginning of the input) and infix completion ​​(i.e. matching terms at any position within the input) are supported. By default, only name, description, and keyword are copied into this field​.
Default​​
default/​search/text ​text A convenient field for performing text search with the standard analyzer. Data is pre-configured to be copied into this field: name, description, keyword, category name, and certain searchable attribute valuesNote that manufacturer and sku cannot be copied into this field as the brand name and part number will be tokenized and generate unnecessary false hits
default/​search/normalized normalized Same as the above default search field, except the data type here is normalized. Data pre-configured is also the same as search.text, plus manufacturer and sku​

Stage 2: Applying Site level override

This stage describes how the Product Override related data can be transformed and loaded into the Product index. It starts with running the following SQL to retrieve Product​ data from the Commerce database:
SELECT C.CATENTRY_ID, O.LANGUAGE_ID, O.NAME, O.SHORTDESCRIPTION, O.KEYWORD, O.FULLIMAGE, O.THUMBNAIL, L.LOCALENAME
           FROM CATENTRY C ${TI_DELTA_JOIN_QUERY}, CATENTDESCOVR O, LANGUAGE L
           WHERE C.CATENTRY_ID = O.CATENTRY_ID AND O.LANGUAGE_ID = ${param.langId} AND L.LANGUAGE_ID = O.LANGUAGE_ID AND C.MARKFORDELETE = 0
                   AND C.CATENTRY_ID IN 
                  (SELECT R.CATENTRY_ID FROM CATGPENREL R
    	              WHERE R.CATALOG_ID = ${param.catalogId} AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
                       (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))) ${extCatentryAndSQL}	 
Next, the result set is passed to the FindOverridesFromDatabase processor for transformation, using the following table to ​map the database field returned from the SQL above to an index field in the Product index:​​​
​​Index Field​ Name​ ​Index Field Type ​​Description
​​Language Sensitive Data​​​
​name/override/raw ​raw The store level override for name of this catalog entiry; mapped to ​CATENTDESCOVR​.NAME
name/override/normalized ​normalized Same as above
name/override/text text Same as above
​keyword/override/text​ ​raw The store level override for keyword of this catalog entiry; mapped to ​CATENTDESCOVR​.KEYWORD​
​description/override/raw ​raw The store level override for short description of this catalog entiry; mapped to ​CATENTDESCOVR​.SHORTDESCRIPTION
description/override/text text Same as above
url/override/thumbnail ​raw ​The store level override for thumbnail image path of this catalog entry​; mapped to CATENTDESCOVR​​.THUMBNAIL
​​url/override/image ​raw ​The store level override for​ full image path of this catalog entry​; mapped to CATENTDESCOVR​​.FULLIMAGE​

Stage 3: Associating subscriptions

This stage describes how subscriptions can be built and loaded into the Product index. It starts with running the following SQL to retrieve Product​ data from the Commerce database:
SELECT C.CATENTRY_ID, S.DISALLOW_REC_ORDER, T.SUBSCPTYPE_ID
		FROM CATENTRY C ${TI_DELTA_JOIN_QUERY}, CATENTSUBS S, SUBSCPTYPE T
		WHERE S.SUBSCPTYPE_ID = T.SUBSCPTYPE_ID AND C.CATENTRY_ID = S.CATENTRY_ID AND C.MARKFORDELETE = 0
		    AND C.CATENTRY_ID IN 
		  (SELECT R.CATENTRY_ID FROM CATGPENREL R
		   WHERE R.CATALOG_ID = ${param.catalogId} AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
		         (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))) ${extCatentryAndSQL}​     
Next, the result set is passed to the FindSubscriptionsFromDatabase​ processor for transformation, using the following table to ​map the database field returned from the SQL above to an index field in the Product index:​​​
​​Index Field​ Name​ ​Index Field Type ​​Description
​subscription/type ​raw ​The type of the subscription this catalog entry can be subscribed to
​​subscription/recurring ​boolean ​Indicates whether this catalog entry is prohibited from being purchased through recurring order. Default value is true.

Stage 4: Associating parent and child product relationship

This stage describes how product and item relationship can be built and loaded into the Product index. It starts with running the following SQL to retrieve Product​ data from the Commerce database:
 SELECT L.CATENTRY_ID_PARENT,
  	     LISTAGG(L.CATENTRY_ID_CHILD, ', ') WITHIN GROUP (ORDER BY L.CATENTRY_ID_CHILD) CATENTRY_ID_CHILD,
	     LISTAGG(TO_CHAR(L.SEQUENCE), ', ') WITHIN GROUP (ORDER BY L.CATENTRY_ID_CHILD) SEQUENCE,
	     LISTAGG(L.CATRELTYPE_ID, '###') WITHIN GROUP (ORDER BY L.CATENTRY_ID_CHILD) CATRELTYPE_ID,
	     LISTAGG(R.CATALOG_ID, '###') WITHIN GROUP (ORDER BY L.CATENTRY_ID_CHILD) CATALOG_ID,
	     NULL AS COMPONENT_ID,
	     NULL AS QUANTITY
	FROM CATENTREL L, CATGPENREL R, CATENTRY C
	${TI_DELTA_JOIN_QUERY}
   WHERE R.CATALOG_ID IN
         (SELECT CATALOG_ID FROM STORECAT
           WHERE STOREENT_ID IN
	             (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
	 AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0 AND C.CATENTRY_ID = L.CATENTRY_ID_PARENT
	 AND C.CATENTTYPE_ID NOT IN ('ItemBean') ${extCatentryAndSQL}
	 AND L.CATRELTYPE_ID NOT IN ('BUNDLE_COMPONENT', 'PACKAGE_COMPONENT')
	 AND L.CATENTRY_ID_CHILD IN (SELECT CATENTRY_ID FROM CATENTRY CE Where CE.CATENTRY_ID = L.CATENTRY_ID_CHILD AND CE.MARKFORDELETE = 0)
   GROUP BY L.CATENTRY_ID_PARENT
   UNION
  SELECT CATENTRY_ID_PARENT,
         LISTAGG(KITCOMP.CATENTRY_ID_CHILD,', ') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) CATENTRY_ID_CHILD,
         LISTAGG(TO_CHAR(KITCOMP.SEQUENCE),', ') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) SEQUENCE,
         LISTAGG(KITCOMP.CATRELTYPE_ID, '###') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) CATRELTYPE_ID,
         LISTAGG(KITCOMP.CATALOG_ID, '###') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) CATALOG_ID,
         LISTAGG(KITCOMP.COMPONENT_ID, ', ') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) COMPONENT_ID,
         LISTAGG(TO_CHAR(KITCOMP.QUANTITY), ', ') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) QUANTITY
    FROM (SELECT L.CATENTRY_ID_PARENT, L.CATENTRY_ID_CHILD, L.SEQUENCE, R.CATALOG_ID, CATENTRY_ID_CHILD COMPONENT_ID, QUANTITY, L.CATRELTYPE_ID
            FROM CATENTREL L, CATGPENREL R, CATENTRY C
			${TI_DELTA_JOIN_QUERY}
           WHERE R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT
                                   WHERE STOREENT_ID IN
                                         (SELECT RELATEDSTORE_ID FROM STOREREL
                                           WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
	         AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0 AND C.CATENTRY_ID = L.CATENTRY_ID_PARENT
	         AND L.CATENTRY_ID_CHILD IN (SELECT CATENTRY_ID FROM CATENTRY CE Where CE.CATENTRY_ID = L.CATENTRY_ID_CHILD AND CE.MARKFORDELETE = 0)
		     AND CATRELTYPE_ID in ('BUNDLE_COMPONENT','PACKAGE_COMPONENT') ${extCatentryAndSQL}) KITCOMP
   GROUP BY KITCOMP.CATENTRY_ID_PARENT
  OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY             
Next, the result set is passed to the FindChildItemsFromDatabase​ processor for transformation, using the following table to ​map the database field returned from the SQL above to an index field in the Product index:​​​
​​Index Field​ Name​ ​Index Field Type ​​Description
​Navigational Data​​​​
​​relationship/item/id ​​id_string ​​Catentry's child catentry; mappedfrom CATENTREL table
​relationship​​/item/sequence ​float ​​Catentry's display sequence; mapped to table: CATENTREL
​​relationship/component/id id_​string A list of identifiers for components of bundle's and packages​; mapped to table CATENTREL
​relationship/component/quantity float The quantity of each of the components above

Stage 5: Applying product set relationship for contract entitlement

This stage describes how product level entitlement can be loaded into the Product index. It starts with running the following SQL to retrieve Product​ data from the Commerce database:
SELECT C.CATENTRY_ID, R.PRODUCTSET_ID
	  FROM CATENTRY C, PRSETCEREL R
	 WHERE C.CATENTRY_ID = R.CATENTRY_ID AND C.MARKFORDELETE = 0
	   AND C.CATENTRY_ID IN
	       (SELECT G.CATENTRY_ID FROM CATGPENREL G
	         WHERE G.CATALOG_ID = ${param.catalogId} AND G.CATALOG_ID IN
	               (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
	               ​(SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))) 
	 ORDER BY R.CATENTRY_ID	
Next, the result set is passed to the FindEntitlementsFromDatabase​ Groovy script for transformation, using the following table to ​map the database field ​returned from the SQL above to an index field in the Product index:​​​
​​Index Field​ Name​ ​Index Field Type ​​Description
​Navigational Data​​​​
​relationship/set id_​string A list of product sets that this catentry has participated in; mapped to PRSETCEREL​.PRODUCTSET_ID

Stage 6: Generating merchandising associations

This stage describes how offer and list prices can be loaded into the Product index. It starts with running the following SQL to retrieve Product​ data from the Commerce database:
SELECT M.CATENTRY_ID_FROM,
		   LISTAGG(M.MASSOCTYPE_ID, '###') WITHIN GROUP (ORDER BY M.RANK) TYPE,
		   LISTAGG(TO_CHAR(COALESCE(M.RANK, 0)), ', ') WITHIN GROUP (ORDER BY M.RANK) RANK,
		   LISTAGG(TO_CHAR(COALESCE(M.QUANTITY, 0)), ', ') WITHIN GROUP (ORDER BY M.RANK) QUANTITY,
		   LISTAGG(M.CATENTRY_ID_TO, ', ') WITHIN GROUP (ORDER BY M.RANK) CATENTRY_ID_TO
	  FROM MASSOCCECE M ${TI_DELTA_JOIN_QUERY_MASSOCCECE}
	 WHERE M.STORE_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}) ${extCatentryIdFromAndSQL}
	 GROUP BY M.CATENTRY_ID_FROM
Next, the result set is passed to the FindAssociationsFromDatabase​ processor for transformation, using the following table​ to ​map the database field returned from the SQL above to an index field in the Product index:​​​
​​Index Field​ Name​ ​Index Field Type ​​Description
​Merchandising ​​Associations​
​​associations/id ​​id_string ​​The catalog entry id of the target of this merchandising association; mapped to MASSOCCECE.CATENTRY_ID_FROM
​​associations/type ​​id_string ​​The identifier of the merchandising association type; mapped to MASSOCCECE.MASSOCTYPE_ID​
​​associations/rank float ​​The sequence number used for display order​; mapped to MASSOCCECE.RANK
​​associations/quantity ​​float ​​The quantity related to this association; mapped to MASSOCCECE.QUANTITY

Stage 7: Associating product attachments and angle images

This stage describes how product attachments can be associated with a document and loaded into the Product index. It starts with running the following SQL to retrieve Product​ data from the Commerce database:
SELECT CE.CATENTRY_ID,
	       LISTAGG(ATCHAST.ATCHAST_ID, ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) ATCHAST_ID,
	       LISTAGG(ATCHTGT.IDENTIFIER, '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) IDENTIFIER,
               LISTAGG(COALESCE(NULLIF(ATCHRELDSC.NAME,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) NAME,
               LISTAGG(COALESCE(NULLIF(ATCHRELDSC.SHORTDESCRIPTION,''), ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) SHORTDESCRIPTION,
	       LISTAGG(COALESCE(ATCHAST.ATCHASTPATH, ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) ATCHASTPATH,
               LISTAGG(COALESCE(NULLIF(ATCHAST.DIRECTORYPATH,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) DIRECTORYPATH,
               LISTAGG(COALESCE(NULLIF(ATCHAST.MIMETYPE,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) MIMETYPE,
	       LISTAGG(ATCHASTLG.LANGUAGE_ID, ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) LANGUAGE_ID,
	       LISTAGG(COALESCE(ATCHRLUS.IMAGE, ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) IMAGE,
	       LISTAGG(COALESCE(ATCHRLUS.IDENTIFIER, ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) RULENAME,
               LISTAGG(TO_CHAR(ATCHREL.SEQUENCE), ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) SEQUENCE
	  FROM CATENTRY CE
		   ${TI_DELTA_JOIN_QUERY_ATTCH}
	  	   JOIN ATCHREL ON (ATCHREL.BIGINTOBJECT_ID = CE.CATENTRY_ID)
		   JOIN ATCHOBJTYP ON (ATCHREL.ATCHOBJTYP_ID = ATCHOBJTYP.ATCHOBJTYP_ID AND ATCHOBJTYP.IDENTIFIER = 'CATENTRY')
		   LEFT JOIN ATCHTGT ON (ATCHREL.ATCHTGT_ID = ATCHTGT.ATCHTGT_ID )
		   JOIN ATCHAST ON (ATCHAST.ATCHTGT_ID = ATCHTGT.ATCHTGT_ID)
		   LEFT JOIN ATCHASTLG ON (ATCHASTLG.ATCHAST_ID = ATCHAST.ATCHAST_ID) 
		   LEFT JOIN ATCHRELDSC ON (ATCHRELDSC.ATCHREL_ID = ATCHREL.ATCHREL_ID AND ATCHRELDSC.LANGUAGE_ID= ${param.langId})
		   JOIN ATCHRLUS ON (ATCHREL.ATCHRLUS_ID = ATCHRLUS.ATCHRLUS_ID)
	 WHERE (ATCHASTLG.ATCHASTLG_ID IS NULL OR ATCHASTLG.LANGUAGE_ID=${param.langId})
	   AND CE.CATENTRY_ID IN (SELECT C.CATENTRY_ID FROM CATGPENREL R, CATENTRY C
	                           WHERE R.CATALOG_ID = ${param.catalogId} AND R.CATALOG_ID IN
	                                 (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
	                                 (SELECT RELATEDSTORE_ID FROM STOREREL
	                                   WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
	                             AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0 AND C.CATENTTYPE_ID <> 'ItemBean') ${extCatentryAndSQL1a}
	 GROUP BY CE.CATENTRY_ID	 
	       
Next, the result set is passed to the FindAttachmentsFromDatabase​ processor for transformation, using the following table to ​map the database field returned from the SQL above to an index field in the Product index:​​
​Attachments​​​
attachments/​id​ ​id_string ​The internal identifier of the attachement asset
attachments/​identifier​ id_string The identifier of the attachment object type
attachments/​name/raw​ ​raw T​he language-dependent name for the attachment relation
​attachments/​description/raw​ raw The short description for the attachment relation​
attachments/​url​ raw The attachment asset path is the relative path of the attachment asset which includes the directory path and the file name of the attachment asset
attachments/​mime​ normalized ​The MIME type encoding of the attachment asset
attachments/​sequence​ ​float The sequence number for displaying the attachment relation
attachments/​rule​ raw ​The attachment relation usage identifier that describes the usage of this attachment relation
Images
​images/sequence ​float The sequence number for displaying the angle image attachment​;mapped to ATCHREL.SEQUENCE​​​ if ATCHRLUS.IDENTIFIER​ ​equal to ​ANGLEIMAGES_FULLIMAGE or ANGLEIMAGES_THUMBNAIL
​​​images/image id_string The angle image attachment asset path is the relative path which includes the directory path and the file name of the angle image attachment;mapped to ATCHAST​.ATCHASTPATH​​​​​​​ if ATCHRLUS.IDENTIFIER​ ​equal to ​ANGLEIMAGES_FULLIMAGE​
​​images/thumbnail id_string The angle image attachment asset path is the relative path which includes the directory path and the file name of the angle image attachment; mapped to ATCHAST​.ATCHASTPATH​​​​​​​ if ATCHRLUS.IDENTIFIER​ ​equal to ​ANGLEIMAGES_THUMBNAIL​
​​images/name id_string T​he language-dependent name for the angle image attachment​; mapped to ATCHRELDSC.NAME​​ if ATCHRLUS.IDENTIFIER​ ​equal to ​ANGLEIMAGES_FULLIMAGE or ANGLEIMAGES_THUMBNAIL​
Default​​
​​default/attachment/text ​text ​Same as the default search field for text searching, except this field is only scope to name and description of ​​attachments.

Stage 8: Building catalog hierarchy for navigation​​

This stage describes how catalog hierachy can be associated with a document and loaded into the Product index. It starts with running the following SQL to retrieve Product​ data from the Commerce database:
SELECT C.CATENTRY_ID,
                   LISTAGG(R.CATGROUP_ID, '###') WITHIN GROUP (ORDER BY R.CATGROUP_ID) CATGROUP_ID,
	           LISTAGG(TO_CHAR(R.SEQUENCE), '###') WITHIN GROUP (ORDER BY R.CATGROUP_ID) SEQUENCE
          FROM CATGPENREL R, STORECAT S, CATENTRY C
			${TI_DELTA_JOIN_QUERY}
         WHERE R.CATALOG_ID = ${param.catalogId}
           AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
               (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
           AND R.CATENTRY_ID = C.CATENTRY_ID AND S.CATALOG_ID = R.CATALOG_ID ${extCatentryAndSQL}
         GROUP BY C.CATENTRY_ID ORDER BY C.CATENTRY_ID
              OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY  
			
		
Next, the result set is passed to the FindParentCategoryFromDatabase processor for transformation and and then use​ CatalogService​ controller to get data from DistributedMapCacheClient​. Catalog hierachy data for each catentry is stored in cache using below steps -
  • It starts with running the following Elasticsearch query against Category index:​
    {
      "stored_fields": [
        "id*",
        "category.*",
        "name.*",
        "path"
      ],
      "size": ${es.pageSize},
      "query": {
        "bool": {
          "must_not": {
            "exists": {
              "field": "category.parent"
            }
          },
          "filter": [
            {
              "term": {
                "id.store": "${param.storeId}"
              }
            },
            {
              "term": {
                "id.catalog": "${param.catalogId}"
              }
            },
            {
              "term": {
                "id.language": "${param.langId}"
              }
            }
          ]
        }
      }
    }​​
  • The initial result set from the above search query will return all the top level categories of the given catalog. Using all the child categories from this set of top level categories to generate another Elasticsearch query to look for the next level child categories of the current sales catalog:
    {
        "stored_fields": [
            "id*",
            "category.*",
            "name.*"​​,
             
        ],
        "size": 10000,
        "query": {
            "bool": {
                "must": {
                    "match_all": {}
                },
                "filter": [
                    {
                        "terms": {
                            "category.parent": [
                                "10020", ...
                            ]
                        }
                    },
                    {
                        "term": {
                            "id.store": "1"
                        }
                    },
                    {
                        "term": {
                            "id.catalog": "10502"
                        }
                    },
                    {
                        "term": {
                            "id.language": "-1"
                        }
                    }
                ]
            }
        }
    }​​
Note: The initial result set from the above search query will be sent back to the dataflow in NiFi for re-execution.​
  • The initial result set from the above search query will be sent back to the dataflow in NiFi for re-execution.​
​Index Field​ Name​ ​Index Field Type ​​​Description
Navigational Data
​identifier id_string catalogId + hyphen separated category path Ids leading up to the root category
category/<identifier>/name normalized The name of this parent sales category
category/<identifier>/parent​ id_string The parent category of this current document used for sequence
category/<identifier>/sequence ​float The leaf category level (shallow) sequence defined in CMC
category/<identifier>/path/id id_string A list of category internal ids leading up to the root category
category/<identifier>/path/name raw Similar list of categories leading up to the root, except this field stores the name of the category instead
​path/<catalogId> raw okenized field for the full navigation path to the current category node in the catalog. ​For example, when a "dress" category (id:10001) with path "/1/3/10001" is indexed for sales catalog 10502, this field stores the original form of the path

Stage 9: Associating attributes

This stage describes how product and item attributes can be associated with a document and loaded into the Product index. It starts with running the following SQL to retrieve Product​ data from the Commerce database:
SELECT CEA.CATENTRY_ID,
            LISTAGG(CEA.USAGE, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) USAGE,
            LISTAGG(AD.LANGUAGE_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) LANGUAGE_ID,
            LISTAGG(A.STOREENT_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREENT_ID,
            LISTAGG(A.ATTRTYPE_ID, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRTYPE_ID,
            LISTAGG(CEA.ATTR_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_ID,
            LISTAGG(TO_CHAR(COALESCE(CEA.SEQUENCE, 0)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_SEQUENCE,
            LISTAGG(A.IDENTIFIER, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_IDENTIFIER, 
            LISTAGG(COALESCE(A.DISPLAYABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) DISPLAYABLE, 
            LISTAGG(COALESCE(A.SEARCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SEARCHABLE, 
            LISTAGG(COALESCE(A.COMPARABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) COMPARABLE, 
            LISTAGG(COALESCE(A.FACETABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FACETABLE,
            LISTAGG(COALESCE(A.MERCHANDISABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) MERCHANDISABLE, 
            LISTAGG(COALESCE(A.SWATCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SWATCHABLE,
            LISTAGG(COALESCE(A.STOREDISPLAY, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREDISPLAY,
            LISTAGG(COALESCE(AD.NAME, ADF.NAME), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_NAME, 
            LISTAGG(COALESCE(COALESCE(AD.GROUPNAME, ADF.GROUPNAME), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) GROUPNAME, 
            LISTAGG(V.IDENTIFIER, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_IDENTIFIER,
            LISTAGG(COALESCE(COALESCE(VD.STRINGVALUE, VDF.STRINGVALUE), ' ' ), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STRINGVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.INTEGERVALUE, VDF.INTEGERVALUE)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) INTEGERVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.FLOATVALUE, VDF.FLOATVALUE)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FLOATVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.SEQUENCE, VDF.SEQUENCE)), '0'), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_SEQUENCE, 
            LISTAGG(COALESCE(COALESCE(VD.QTYUNIT_ID, VDF.QTYUNIT_ID), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTYUNIT_ID,
            LISTAGG(COALESCE(NULLIF(COALESCE(VD.IMAGE1, VDF.IMAGE1), ''), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE1,
	    LISTAGG(COALESCE(NULLIF(COALESCE(VD.IMAGE2, VDF.IMAGE2), ''), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE2,
            LISTAGG(CEA.ATTRVAL_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_ID,
            LISTAGG(COALESCE(COALESCE(QD.DESCRIPTION, QDF.DESCRIPTION), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTY_DESCRIPTION
       FROM (SELECT CER.CATENTRY_ID_CHILD CATENTRY_ID, CEATTR.SEQUENCE, CEATTR.ATTR_ID, CEATTR.ATTRVAL_ID, CEATTR.USAGE
               FROM CATENTRYATTR CEATTR,
                    (SELECT A.CATENTRY_ID_PARENT CATENTRY_ID_PARENT,A.CATENTRY_ID_CHILD CATENTRY_ID_CHILD 
                       FROM CATENTREL A,
                            (SELECT G.CATENTRY_ID
                               FROM CATGPENREL G,CATENTRY E 
                              WHERE G.CATENTRY_ID = E.CATENTRY_ID AND G.CATALOG_ID = ${param.catalogId} AND E.MARKFORDELETE = 0 
                                AND G.CATALOG_ID IN ( SELECT CATALOG_ID FROM STORECAT
                                                       WHERE STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId} )) 
                             ) B,
                            CATENTRY C 
                      WHERE A.CATENTRY_ID_PARENT=B.CATENTRY_ID AND C.CATENTRY_ID=A.CATENTRY_ID_CHILD AND C.MARKFORDELETE=0 AND A.CATRELTYPE_ID IN ('PRODUCT_VARIANT') 
                      UNION 
                     SELECT A.CATENTRY_ID CATENTRY_ID_PARENT,A.CATENTRY_ID CATENTRY_ID_CHILD 
                       FROM (SELECT G.CATENTRY_ID FROM CATGPENREL G,CATENTRY E 
                              WHERE G.CATENTRY_ID = E.CATENTRY_ID AND G.CATALOG_ID = ${param.catalogId} AND E.MARKFORDELETE = 0 
                                AND G.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT
                                                      WHERE STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId} )) 
                             ) A 
                    ) CER
              WHERE CEATTR.CATENTRY_ID = CER.CATENTRY_ID_PARENT AND CEATTR.ATTRVAL_ID <> 0
                AND (CER.CATENTRY_ID_PARENT = CER.CATENTRY_ID_CHILD OR 
                    (SELECT COUNT(*) FROM CATENTRYATTR WHERE CATENTRYATTR.CATENTRY_ID = CER.CATENTRY_ID_CHILD AND CEATTR.ATTR_ID = CATENTRYATTR.ATTR_ID) = 0) ) CEA
              INNER JOIN ATTR A ON (CEA.ATTR_ID = A.ATTR_ID)
              INNER JOIN ATTRVAL V ON (CEA.ATTRVAL_ID = V.ATTRVAL_ID)
			   ${TI_DELTA_JOIN_QUERY_ATTR}	
               LEFT OUTER JOIN ATTRDESC AD ON (A.ATTR_ID = AD.ATTR_ID AND AD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRDESC ADF ON (A.ATTR_ID = ADF.ATTR_ID AND ADF.LANGUAGE_ID = ${default.language})
               LEFT OUTER JOIN ATTRVALDESC VD ON (V.ATTRVAL_ID = VD.ATTRVAL_ID AND VD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRVALDESC VDF ON (V.ATTRVAL_ID = VDF.ATTRVAL_ID AND VDF.LANGUAGE_ID = ${default.language})
               LEFT OUTER JOIN QTYUNITDSC QD ON (VD.QTYUNIT_ID = QD.QTYUNIT_ID AND QD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN QTYUNITDSC QDF ON (VD.QTYUNIT_ID = QDF.QTYUNIT_ID AND QDF.LANGUAGE_ID = ${default.language}) ${extCatentryWhereSQL}
       GROUP BY CEA.CATENTRY_ID
       ORDER BY CEA.CATENTRY_ID
  OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY
     
	         
Next, the result set is passed to the FindAttributesFromDatabase​​ processor for transformation, using the following table to ​map the database field returned from the SQL above to an index field in the Product index:​​
​​Index Field​ Name​ ​Index Field Type ​​Description
​Properties​​​​
​​attributes/<identifier>/identifier ​​id_string ​​The external identifier of the attribute; mapped to ATTR.IDENTIFIER​
​attributes​​/<id>/id id_string The internal identifier of the attribute; mapped to ATTR.ATTR_ID​​
​attributes​​/<id>/key id_string The normalized attribute identifier that is used as the key for this current attribute entry​; generated from ATTR.IDENTIFIER
​​attributes/<id>/displayable ​​boolean ​​Identifies if this attribute is displayabe at the storefront; mapped to ATTR.DISPLAYABLE
​attributes​​/<id>/merchandisable boolean Specifies that the attribute is used in creating merchandising rules; mapped to ATTR.MERCHANDISING
​​attributes/<id>/searchable boolean Identifies if this attribute can be searched; mapped to ATTR.SEARCHABLE​​
​attributes​​/<id>/facetable boolean ​​Specifies that the attribute is used as a facet in the storefront for faceted navigation; mapped to ATTR.FACETABLE
​​attributes/<id>/comparable boolean Identifier if this attribute can be used for comparison; mapped to ATTR.COMPARABLE​​
​attributes​​/<id>/swatchable boolean ​​Identifies if this attribute can be displayed with swatch image; mapped to ATTR.SWATCHABLE
​attributes​​/<id>/ribbon boolean ​​Identifies if this attribute can be displayed as a ribbon; mapped to ATTR.STOREDISPLAY​​
​​attributes/<id>/image1 raw The path of the first image of this attribute value; mapped to ATTRVALDESC.IMAGE1​​
​attributes​​/<id>/image2 raw ​​The path of the second image of this attribute value; mapped to ATTRVALDESC.IMAGE2
​​attributes/<id>/sequence float The display order of attributes in an attribute group or in the root of the attribute dictionary; mapped to ATTR.SEQUENCE
​attributes​​/<id>/usage ​raw ​​Defines the usage of the attribute; mapped to ATTR.ATTRUSAGE
​attributes​​/<id>/group ​raw ​​Defines the group name of the attribute; mapped to ATTRDESC.GROUPNAME
​​attributes/<id>/name/raw raw The​ name of this attribute; mapped to ATTRDESC.NAME
​attributes​​/<id>/name/text ​text ​​Same as above
​​attributes/<id>/name/normalized ​​normalized ​​Same as above
​​attributes/<id>/value/identifier ​​id_string The external identifier for this attribute value; mapped to ATTRVAL.​​IDENTIFIER
​attributes​​/<id>/value/id​ ​id_string​ The internal unique id for this attribute value; mapped to ATTRVAL.ATTRVAL_ID​​
​​​attributes/<id>/value/raw ​​id_string ​​​The string value of this attribute value; mapped to ATTRVALDESC.STRINGVALUE​, FLOATVALUE, or INTEGERVALUE
​attributes​​/<id>/value/sequence​ ​float ​​A number that determines the display order of a list of allowable attribute values for this attribute; mapped to ATTRVALDESC.SEQUENCE
​​attributes/<id>/value/unit/identifier ​​id_string ​​The unit of measure identifier in which this attribute is measured; mapped to ATTRVALDESC.QTYUNIT_ID
​attributes​​/<id>/value/unit/name/raw ​raw ​​The description of the quantity unit; mapped to QTYUNITDSC.DESCRIPTION
​Natural Language​​​​​
natural/<attribute_type>/colors/red ​range This is the MatchMaker specific field for handling color.Attribute type can be either defining or descriptive
natural/<attribute_type>/colors/green range Same as above
natural/<attribute_type>/colors/blue range ​Same as above
​natural/<attribute_type>/measurements/<unit_of_measure> range This is the MatchMaker specific fields for handling measurements. The followng are the supported unit of measures:- Length - kilometer, meter, centimeter, millimeter, micrometer, nanometer, foot, inch, mile, yard- Weight - metric tonne, kilogram, gram, milligram, stone, pound, ounce- Time - nanosecond, microsecond, millisecond, second, minute, hour, day, week, month, year- Volume - gallon, liter, milliliter​Attribute type can be either defining or descriptive
natural/<attribute_type>/dimensions/width/<unit_of_measure> range This is the MatchMaker specific fields for handling dimension. The followng are the supported unit of measures:- kilometer, meter, centimeter, millimeter, micrometer, nanometer, foot, inch, mile, yard​Attribute type can be either defining or descriptive
natural/<attribute_type>/dimensions/length/<unit_of_measure> range Same as above
natural/<attribute_type>/dimensions/height/<unit_of_measure> range Same as above

Stage 10: Rolling up item level attributes to their parents

This stage describes how item level attributes can be rolled up to their parent document and loaded into the Product index. It starts with running the following SQL to retrieve Product​ data from the Commerce database:
         SELECT CEA.CATENTRY_ID_PARENT CATENTRY_ID,
	        LISTAGG(CEA.USAGE, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) USAGE,
	        LISTAGG(CAST(AD.LANGUAGE_ID AS VARCHAR(32000)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) LANGUAGE_ID,
	        LISTAGG(CAST(A.STOREENT_ID AS VARCHAR(32000)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREENT_ID,
	        LISTAGG(CAST(A.ATTRTYPE_ID AS VARCHAR(32000)), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRTYPE_ID,
	        LISTAGG(CAST(CEA.ATTR_ID AS VARCHAR(32000)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_ID,
	        LISTAGG(CAST(CEA.SEQUENCE AS VARCHAR(32000)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_SEQUENCE,
	        LISTAGG(CAST(A.IDENTIFIER AS VARCHAR(32000)), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_IDENTIFIER,
	        LISTAGG(COALESCE(A.DISPLAYABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) DISPLAYABLE,
	        LISTAGG(COALESCE(A.SEARCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SEARCHABLE,
	        LISTAGG(COALESCE(A.COMPARABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) COMPARABLE,
	        LISTAGG(COALESCE(A.FACETABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FACETABLE,
	        LISTAGG(COALESCE(A.MERCHANDISABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) MERCHANDISABLE,
	        LISTAGG(COALESCE(A.SWATCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SWATCHABLE,
               LISTAGG(COALESCE(A.STOREDISPLAY, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREDISPLAY,
	        LISTAGG(CAST(COALESCE(AD.NAME, ADF.NAME) AS VARCHAR(32000)), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_NAME,
	        LISTAGG(COALESCE(CAST(COALESCE(AD.GROUPNAME, ADF.GROUPNAME) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) GROUPNAME,
	        LISTAGG(CAST(V.IDENTIFIER AS VARCHAR(32000)), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_IDENTIFIER,
	        LISTAGG(COALESCE(CAST(COALESCE(VD.STRINGVALUE, VDF.STRINGVALUE) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STRINGVALUE,
	        LISTAGG(COALESCE(CAST(TO_CHAR(COALESCE(VD.INTEGERVALUE, VDF.INTEGERVALUE)) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) INTEGERVALUE,
	        LISTAGG(COALESCE(CAST(TO_CHAR(COALESCE(VD.FLOATVALUE, VDF.FLOATVALUE)) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FLOATVALUE,
	        LISTAGG(COALESCE(CAST(COALESCE(VD.SEQUENCE, VDF.SEQUENCE) AS VARCHAR(32000)), '0'),', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_SEQUENCE,
	        LISTAGG(COALESCE(CAST(COALESCE(VD.QTYUNIT_ID, VDF.QTYUNIT_ID) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTYUNIT_ID,
	        LISTAGG(COALESCE(CAST(NULLIF(COALESCE(VD.IMAGE1, VDF.IMAGE1),'') AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE1,
		LISTAGG(COALESCE(CAST(NULLIF(COALESCE(VD.IMAGE2, VDF.IMAGE2),'') AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE2,
	        LISTAGG(CAST(CEA.ATTRVAL_ID AS VARCHAR(32000)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_ID,
	        LISTAGG(COALESCE(CAST(COALESCE(QD.DESCRIPTION, QDF.DESCRIPTION) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTY_DESCRIPTION
	    FROM (SELECT DISTINCT CER.CATENTRY_ID_PARENT, CEATTR.SEQUENCE, CEATTR.ATTR_ID, CEATTR.ATTRVAL_ID, CEATTR.USAGE
	           FROM CATENTRYATTR CEATTR,
	                (SELECT A.CATENTRY_ID_PARENT, A.CATENTRY_ID_CHILD
	                   FROM CATENTREL A,
	                        (SELECT G.CATENTRY_ID
	                           FROM CATGPENREL G,CATENTRY E
	                          WHERE G.CATENTRY_ID = E.CATENTRY_ID AND G.CATALOG_ID = ${param.catalogId} AND E.MARKFORDELETE = 0
	                            AND G.CATALOG_ID IN ( SELECT CATALOG_ID FROM STORECAT
	                                                   WHERE STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId} ))
	                         ) B,
	                        CATENTRY C
	                  WHERE A.CATENTRY_ID_PARENT=B.CATENTRY_ID AND C.CATENTRY_ID=A.CATENTRY_ID_CHILD AND C.MARKFORDELETE=0 AND A.CATRELTYPE_ID IN ('PRODUCT_ITEM')
	                ) CER
	          WHERE CEATTR.CATENTRY_ID = CER.CATENTRY_ID_CHILD AND CEATTR.ATTRVAL_ID <> 0 AND CEATTR.USAGE = 1) CEA
	          INNER JOIN ATTR A ON (CEA.ATTR_ID = A.ATTR_ID)
	          INNER JOIN ATTRVAL V ON (CEA.ATTRVAL_ID = V.ATTRVAL_ID)
			   ${TI_DELTA_JOIN_QUERY_ROLLUP_ATTR}
	           LEFT OUTER JOIN ATTRDESC AD ON (A.ATTR_ID = AD.ATTR_ID AND AD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRDESC ADF ON (A.ATTR_ID = ADF.ATTR_ID AND ADF.LANGUAGE_ID = ${default.language})
	           LEFT OUTER JOIN ATTRVALDESC VD ON (V.ATTRVAL_ID = VD.ATTRVAL_ID AND VD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRVALDESC VDF ON (V.ATTRVAL_ID = VDF.ATTRVAL_ID AND VDF.LANGUAGE_ID = ${default.language})
	           LEFT OUTER JOIN QTYUNITDSC QD ON (VD.QTYUNIT_ID = QD.QTYUNIT_ID AND QD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN QTYUNITDSC QDF ON (VD.QTYUNIT_ID = QDF.QTYUNIT_ID AND QDF.LANGUAGE_ID = ${default.language}) ${extCatentryIdWhereParentOrChild}
	  GROUP BY CEA.CATENTRY_ID_PARENT
      ORDER BY CEA.CATENTRY_ID_PARENT
      LIMIT ${param.offset}, ${param.pageSize}​​

Next, the result set is passed to the FindAttributesFromDatabase​​ processor for transformation, using the table in stage 9 to ​map the database field returned from the SQL above to an index field in the Product index.

Stage 11: Pushing down product level properties to its items

This stage describes how properties and attributes of a product entry can be pushed down to all its child items in the Product index. It starts with running the following Elasticsearch query against the Product index:​
​
	  {
  "stored_fields": [
    "id.*",
    "identifier.*",
    "relationship.*",
    "attributes.*",
    "natural.descriptive.*",
    "attachments.*",
    "images.*",
    "manufacturer.*"​
​
  ],
  "size": "100",
  "_source": false,
  "query": {
    "bool": {
      "must": {
        "match_all": {}
      },
      "filter": [
        {
          "bool": {
            "should": [
              {"term": {"relationship.name": "product"}},
              {"term": {"relationship.name": "variant"}}
             ]
          }
        },
        {
          "term": {
            "id.store": "${param.storeId}"
          }
        },
        {
          "term": {
            "id.catalog": "${param.catalogId}"
          }
        },
        {
          "term": {
            "id.language": "${param.langId}"
          }
        }     
        ${extCatentryES}
	${extDataloadES}​
      ]
    }
  }
}
Next, the result set is passed to the PushDownParentProperties processor for transformation. The response field returned from the above search query is mapped into index field in the Product index.
  • Response fields - ​attribute.* and natural.descriptive.*, is mapped using above table in step 9.
  • Response fields - ​attachments.* and images.* , is mapped using above table in step 7.
  • Response field - manufacture.*, is mapped using above table in step 1.

Stage 12: Associating child descriptive attributes

This stage describes how descriptive attributes of a child entry can be overriden and loaded into the Product index. It starts with running the following the following SQL to retrieve Product​ data from the Commerce database:​
SELECT CEA.CATENTRY_ID, 
            LISTAGG(CEA.USAGE, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) USAGE,
            LISTAGG(AD.LANGUAGE_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) LANGUAGE_ID,
            LISTAGG(A.STOREENT_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREENT_ID,
            LISTAGG(A.ATTRTYPE_ID, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRTYPE_ID,
            LISTAGG(CEA.ATTR_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_ID,
            LISTAGG(TO_CHAR(COALESCE(CEA.SEQUENCE, 0)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_SEQUENCE,
            LISTAGG(A.IDENTIFIER, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_IDENTIFIER, 
            LISTAGG(COALESCE(A.DISPLAYABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) DISPLAYABLE, 
            LISTAGG(COALESCE(A.SEARCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SEARCHABLE, 
            LISTAGG(COALESCE(A.COMPARABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) COMPARABLE, 
            LISTAGG(COALESCE(A.FACETABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FACETABLE,
            LISTAGG(COALESCE(A.MERCHANDISABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) MERCHANDISABLE, 
            LISTAGG(COALESCE(A.SWATCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SWATCHABLE,
            LISTAGG(COALESCE(A.STOREDISPLAY, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREDISPLAY,
            LISTAGG(COALESCE(AD.NAME, ADF.NAME), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_NAME, 
            LISTAGG(COALESCE(COALESCE(AD.GROUPNAME, ADF.GROUPNAME), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) GROUPNAME, 
            LISTAGG(V.IDENTIFIER, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_IDENTIFIER,
            LISTAGG(COALESCE(COALESCE(VD.STRINGVALUE, VDF.STRINGVALUE), ' ' ), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STRINGVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.INTEGERVALUE, VDF.INTEGERVALUE)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) INTEGERVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.FLOATVALUE, VDF.FLOATVALUE)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FLOATVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.SEQUENCE, VDF.SEQUENCE)), '0'), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_SEQUENCE, 
            LISTAGG(COALESCE(COALESCE(VD.QTYUNIT_ID, VDF.QTYUNIT_ID), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTYUNIT_ID,
            LISTAGG(COALESCE(NULLIF(COALESCE(VD.IMAGE1, VDF.IMAGE1), ''), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE1,
	    LISTAGG(COALESCE(NULLIF(COALESCE(VD.IMAGE2, VDF.IMAGE2), ''), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE2,
            LISTAGG(CEA.ATTRVAL_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_ID,
            LISTAGG(COALESCE(COALESCE(QD.DESCRIPTION, QDF.DESCRIPTION), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTY_DESCRIPTION
         FROM (SELECT CER.CATENTRY_ID_CHILD CATENTRY_ID, CEATTR.SEQUENCE, CEATTR.ATTR_ID, CEATTR.ATTRVAL_ID, CEATTR.USAGE
               FROM CATENTRYATTR CEATTR, CATENTREL CATREL,
                    (SELECT A.CATENTRY_ID CATENTRY_ID_PARENT,A.CATENTRY_ID CATENTRY_ID_CHILD 
                       FROM (SELECT G.CATENTRY_ID FROM CATGPENREL G,CATENTRY E 
                              WHERE G.CATENTRY_ID = E.CATENTRY_ID AND G.CATALOG_ID = ${param.catalogId} AND E.MARKFORDELETE = 0 
                                AND G.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT
                                                      WHERE STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId} )) 
                             ) A 
                    ) CER
              WHERE CEATTR.CATENTRY_ID = CER.CATENTRY_ID_PARENT AND CEATTR.ATTRVAL_ID <> 0 
              AND CATREL.CATENTRY_ID_CHILD = CEATTR.CATENTRY_ID AND CATREL.CATRELTYPE_ID = 'PRODUCT_ITEM' AND CEATTR.USAGE = 2
              AND (CER.CATENTRY_ID_PARENT = CER.CATENTRY_ID_CHILD OR 
                    (SELECT COUNT(*) FROM CATENTRYATTR WHERE CATENTRYATTR.CATENTRY_ID = CER.CATENTRY_ID_CHILD AND CEATTR.ATTR_ID = CATENTRYATTR.ATTR_ID) = 0)) CEA
              INNER JOIN ATTR A ON (CEA.ATTR_ID = A.ATTR_ID)
              INNER JOIN ATTRVAL V ON (CEA.ATTRVAL_ID = V.ATTRVAL_ID)
			   ${TI_DELTA_JOIN_QUERY_ATTR}	
               LEFT OUTER JOIN ATTRDESC AD ON (A.ATTR_ID = AD.ATTR_ID AND AD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRDESC ADF ON (A.ATTR_ID = ADF.ATTR_ID AND ADF.LANGUAGE_ID = ${default.language})
               LEFT OUTER JOIN ATTRVALDESC VD ON (V.ATTRVAL_ID = VD.ATTRVAL_ID AND VD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRVALDESC VDF ON (V.ATTRVAL_ID = VDF.ATTRVAL_ID AND VDF.LANGUAGE_ID = ${default.language})
               LEFT OUTER JOIN QTYUNITDSC QD ON (VD.QTYUNIT_ID = QD.QTYUNIT_ID AND QD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN QTYUNITDSC QDF ON (VD.QTYUNIT_ID = QDF.QTYUNIT_ID AND QDF.LANGUAGE_ID = ${default.language}) ${extCatentryWhereSQL}  
       GROUP BY CEA.CATENTRY_ID
       ORDER BY CEA.CATENTRY_ID
  OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY      
​
Next, the result set is passed to the FindAttributesFro​mDatabase​​ processor for transformation, using the above table in stage 9 to ​map the database field returned from the SQL above to an index field in the Product index.​

Stage 13: Associating child attachments

This stage describes how attachments of a child entry can be overriden and loaded into the Product index. It starts with running the following SQL to retrieve Product​ data from the Commerce database:
SELECT CE.CATENTRY_ID,
		 LISTAGG(ATCHAST.ATCHAST_ID, ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) ATCHAST_ID,
	         LISTAGG(ATCHTGT.IDENTIFIER, '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) IDENTIFIER,
                 LISTAGG(COALESCE(NULLIF(ATCHRELDSC.NAME,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) NAME,
                 LISTAGG(COALESCE(NULLIF(ATCHRELDSC.SHORTDESCRIPTION,''), ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) SHORTDESCRIPTION,
		 LISTAGG(COALESCE(ATCHAST.ATCHASTPATH, ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) ATCHASTPATH,
                 LISTAGG(COALESCE(NULLIF(ATCHAST.DIRECTORYPATH,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) DIRECTORYPATH,
                 LISTAGG(COALESCE(NULLIF(ATCHAST.MIMETYPE,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) MIMETYPE,
		 LISTAGG(ATCHASTLG.LANGUAGE_ID, ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) LANGUAGE_ID,
		 LISTAGG(COALESCE(ATCHRLUS.IMAGE, ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) IMAGE,
		 LISTAGG(COALESCE(ATCHRLUS.IDENTIFIER, ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) RULENAME,
		 LISTAGG(TO_CHAR(ATCHREL.SEQUENCE), ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) SEQUENCE
	  FROM CATENTRY CE
		   ${TI_DELTA_JOIN_QUERY_ATTCH}
	  	   JOIN ATCHREL ON (ATCHREL.BIGINTOBJECT_ID = CE.CATENTRY_ID)
		   JOIN ATCHOBJTYP ON (ATCHREL.ATCHOBJTYP_ID = ATCHOBJTYP.ATCHOBJTYP_ID AND ATCHOBJTYP.IDENTIFIER = 'CATENTRY')
		   LEFT JOIN ATCHTGT ON (ATCHREL.ATCHTGT_ID = ATCHTGT.ATCHTGT_ID )
		   JOIN ATCHAST ON (ATCHAST.ATCHTGT_ID = ATCHTGT.ATCHTGT_ID)
		   LEFT JOIN ATCHASTLG ON (ATCHASTLG.ATCHAST_ID = ATCHAST.ATCHAST_ID) 
		   LEFT JOIN ATCHRELDSC ON (ATCHRELDSC.ATCHREL_ID = ATCHREL.ATCHREL_ID AND ATCHRELDSC.LANGUAGE_ID= ${param.langId})
		   JOIN ATCHRLUS ON (ATCHREL.ATCHRLUS_ID = ATCHRLUS.ATCHRLUS_ID)
	 WHERE (ATCHASTLG.ATCHASTLG_ID IS NULL OR ATCHASTLG.LANGUAGE_ID=${param.langId})
	   AND CE.CATENTRY_ID IN (SELECT C.CATENTRY_ID FROM CATGPENREL R, CATENTRY C
	                           WHERE R.CATALOG_ID = ${param.catalogId} AND R.CATALOG_ID IN
	                                 (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
	                                 (SELECT RELATEDSTORE_ID FROM STOREREL
	                                   WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
	                             AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0 AND C.CATENTTYPE_ID = 'ItemBean') ${extCatentryAndSQL1a}
	 GROUP BY CE.CATENTRY_ID

Next, the result set is passed to the ​​FindAttachmentsFromDatabase​​ processor for transformation, using the table in stage 7 to ​map the database field returned from the SQL above to an index field in the Product index.​

Database mapping samples:

Stage 1 samples

The following code is an example of the input data for the CreateProductDocumentFromDatabase processor:
{
   "CATENTRY_ID":10031,
   "MEMBER_ID":7000000000000001001,
   "CATENTTYPE_ID":"ProductBean     ",
   "PARTNUMBER":"AuroraWMDRS-33",
   "MARKFORDELETE":0,
   "BUYABLE":1,
   "STATE":"1",
   "LANGUAGE_ID":-1,
   "MFPARTNUMBER":null,
   "MFNAME":"Luigi Valenti",
   "STARTDATE":null,
   "ENDDATE":null,
   "NAME":"Luigi Valenti Mid Length Cocktail Dress                                                                                         ",
   "SHORTDESCRIPTION":"Sleeveless cocktail dress with lace work and satin strip at the waist",
   "KEYWORD":null,
   "THUMBNAIL":"images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0037_a_pink.jpg",
   "FULLIMAGE":"images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0037_a_pink.jpg",
   "PUBLISHED":1,
   "CATALOG_ID":10001,
   "CATGROUP_ID":10001,
   "SEQUENCE":31.0,
   "LOCALENAME":"en_US           ",
   "STOREENT_ID":10501
}
The CreateProductDocumentFromDatabase processor transforms the input database with the store id passed in from NiFi FlowFile class as attribute into the following output data:
{ "update": { "_id": "1--1-10001-10031", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "buyable": true,
    "identifier": {
      "specification": "product",
      "language": "en_US",
      "sku": {
        "normalized": "AuroraWMDRS-33",
        "raw": "AuroraWMDRS-33"
      },
      "pn": {
        "normalized": "AuroraWMDRS-33",
        "raw": "AuroraWMDRS-33"
      }
    },
    "displayable": true,
    "description": {
      "text_en_US": "Sleeveless cocktail dress with lace work and satin strip at the waist",
      "raw": "Sleeveless cocktail dress with lace work and satin strip at the waist",
      "text": "Sleeveless cocktail dress with lace work and satin strip at the waist"
    },
    "type": "product",
    "url": {
      "image": "images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0037_a_pink.jpg",
      "thumbnail": "images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0037_a_pink.jpg"
    },
    "__meta": {
      "created": "2020-08-04T01:16:42.012Z",
      "modified": "2020-08-04T01:16:42.034Z",
      "version": {
        "min": 0,
        "max": 0
      }
    },
    "manufacturer": {
      "normalized": "Luigi Valenti",
      "raw": "Luigi Valenti",
      "text": "Luigi Valenti"
    },
    "name": {
      "text_en_US": "Luigi Valenti Mid Length Cocktail Dress",
      "normalized": "Luigi Valenti Mid Length Cocktail Dress",
      "raw": "Luigi Valenti Mid Length Cocktail Dress",
      "text": "Luigi Valenti Mid Length Cocktail Dress"
    },
    "id": {
      "catalog": "10001",
      "member": "7000000000000001001",
      "catentry": "10031",
      "language": "-1",
      "store": [
        "10501",
        "1"
      ]
    },
    "state": true,
    "relationship": {
      "name": "product"
    }
  },
  "doc_as_upsert": true
}

Stage 2 samples

The following code is an example of the input data for the FindOverridesFromDatabase processor:

{
  "CATENTRY_ID": 10352,
  "LANGUAGE_ID": -1,
  "NAME": "Luigi Valenti Strapless Sundress - Override                                                                                         ",
  "SHORTDESCRIPTION": "Strapless sundress with smoked top and gored skirt - Override",
  "KEYWORD": null,
  "FULLIMAGE": images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_a_blue.jpg,
  "THUMBNAIL": images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_a_blue.jpg​
}
The FindOverridesFromDatabase processor transforms the input data with the store id, catalog id, language id from NiFi FlowFile class as attributes into the following output data:

{ "update": { "_id": "1--1-10001-10352", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }

{
	"doc": {
		"name": {
			"override": {
				"text_en_US": "Luigi Valenti Strapless Sundress - Override",
				"normalized": "Luigi Valenti Strapless Sundress - Override",
				"raw": "Luigi Valenti Strapless Sundress - Override",
				"text": "Luigi Valenti Strapless Sundress - Override"
			}
		},
		"description": {
			"override": {
				"text_en_US": "Strapless sundress with smoked top and gored skirt - Override",
				"raw": "Strapless sundress with smoked top and gored skirt - Override",
				"text": "Strapless sundress with smoked top and gored skirt - Override"
			}
		},
		"url": {
			"override": {
				"image": "images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_a_blue.jpg",
				"thumbnail": "images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_a_blue.jpg"
			}
		},
		"__meta": {
			"modified": "2020-08-04T01:28:42.405Z"
		}
	}
}    

Stage 3 samples

The following code is an example of the input data for the FindSubscriptionsFromDatabase processor:

{
  "CATENTRY_ID": 10378,
  "DISALLOW_REC_ORDER": 1,
  "SUBSCPTYPE_ID": "NONE                            "
}
The FindSubscriptionsFromDatabase transforms the input data with the store id, language id, and catalog id passed in as NiFi FlowFile class as attributes into the following output data:

{ "update": { "_id": "1--1-10001-10378", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "subscription": {
      "recurring": false,
      "type": "NONE"
    },
    "__meta": {
      "modified": "2020-08-04T01:38:24.368Z"
    }
  }
}

Stage 4 samples

The following code is an example of the input data for the FindChildItemsFromDatabase processor:

{
  "CATENTRY_ID_PARENT": 12361,
  "CATENTRY_ID_CHILD": "12362, 12363, 12364, 12365, 12366, 3074457345616681168, 3074457345616681169",
  "SEQUENCE": "1, 2, 3, 4, 5, 1, 2",
  "CATALOG_ID": "10001###10502###10001###10502###10001###10502###10001",
  "CATRELTYPE_ID": "PRODUCT_ITEM###PRODUCT_ITEM###PRODUCT_ITEM###PRODUCT_ITEM###PRODUCT_ITEM###PRODUCT_VARIANT###PRODUCT_VARIANT",  
  "COMPONENT_ID": null,
  "QUANTITY": null
}
The FindChildItemsFromDatabase processor transforms the input data with the store id, language id, and catalog id passed in from NiFi FlowFile class as attributes into the following output data:

{ "update": { "_id": "1--1-10001-12361", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "relationship": {
      "item": {
        "sequence": [
          1,
          2,
          3,
          4,
          5
        ],
        "id": [
          "12362",
          "12363",
          "12364",
          "12365",
          "12366"
        ]
      },
      "variant": {
        "sequence": [
          1,
          2
        ],
        "id": [
          "3074457345616681168",
          "3074457345616681169"
        ]
      }
    },
    "__meta": {
      "modified": "2020-08-04T01:45:28.668Z"
    }
  }
}

Stage 5 samples

The following code is an example of the input data for the FindEntitlementsFromDatabase processor:

{
  "CATENTRY_ID": 13040,
  "PRODUCTSET_ID": 10001
}
The FindEntitlementsFromDatacase processor transforms the input data with store id, language id, and catalog id passed from as NiFi FlowFile class as attributes into the following sample output data:

{ "update": { "_id": "1--1-10001-13040", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "relationship": {
      "set": "10001"
    },
    "__meta": {
      "modified": "2020-08-04T01:54:15.517Z"
    }
  }
}

Stage 6 samples

The following code is an example of the input data for the FindAssociationsFromDatabase processor:

{
  "CATENTRY_ID_FROM": 10001,
  "CATENTRY_ID_TO": "10003, 10009, 10011",
  "RANK": "1, 2, 3",
  "TYPE": "ACCESSORY###ACCESSORY###ACCESSORY",
  "QUANTITY": "10, 2, 5",
}
The FindAssociationsFromDatabase processor transforms the input data with store id, language id, and catalog id passed from NiFi FlowFile class as attributes into the following sample output data:

{ "update": { "_id": "1--1-10001-10001", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "associations": [
      {
        "quantity": 10,
        "rank": 1,
        "id": "10003",
        "type": "ACCESSORY"
      },
      {
        "quantity": 2,
        "rank": 2,
        "id": "10009",
        "type": "ACCESSORY"
      },
      {
        "quantity": 5,
        "rank": 3,
        "id": "10011",
        "type": "ACCESSORY"
      }
    ],
    "__meta": {
      "modified": "2020-08-04T02:03:01.436Z"
    }
  }
}

Stage 7 samples

The following code is an example of the input data for the FindAttachmentsFromDatabase processor:

{
  "CATENTRY_ID": 10350,
  "ATCHAST_ID": "2231, 2233, 2232, 2234",
  "IDENTIFIER": "Dress Angle 1 20201 thumbnail image###Dress Angle 1 20201 full size image###Dress Angle 2 20201 thumbnail image###Dress Angle 2 20201 full size image",
  "NAME": "Luigi Valenti Strapless Sundress Angle 1###Luigi Valenti Strapless Sundress Angle 1###Luigi Valenti Strapless Sundress Angle 2###Luigi Valenti Strapless Sundress Angle 2",
  "SHORTDESCRIPTION": "Luigi Valenti Strapless Sundress Angle 1###Luigi Valenti Strapless Sundress Angle 1###Luigi Valenti Strapless Sundress Angle 2###Luigi Valenti Strapless Sundress Angle 2",
  "ATCHASTPATH": "images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_a_blue.jpg###images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_a_blue.jpg###images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_b_blue.jpg###images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_b_blue.jpg",
  "DIRECTORYPATH": "images/catalog/apparel/women/###images/catalog/apparel/women/###images/catalog/apparel/women/###images/catalog/apparel/women/",
  "MIMETYPE": "image/jpg###image/jpg###image/jpg###image/jpg",
  "LANGUAGE_ID": null,
  "IMAGE": " ### ### ### ",
  "RULENAME": "ANGLEIMAGES_THUMBNAIL###ANGLEIMAGES_FULLIMAGE###ANGLEIMAGES_THUMBNAIL###ANGLEIMAGES_FULLIMAGE",
  "SEQUENCE": "1, 1, 2, 2"
}

The FindAttachmentsFromDatabase processor transforms the input data with store id, language id, and catalog id passed in from NiFi FlowFile class as attributes into the following sample output data:


{ "update": { "_id": "1--1-10001-10012", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }

{
	"doc": {
		"attachments": [
			{
				"identifier": "Dress Angle 1 20201 thumbnail image",
				"sequence": 1.0,
				"name.raw": "Luigi Valenti Strapless Sundress Angle 1",
				"description.raw": "Luigi Valenti Strapless Sundress Angle 1",
				"mime": "image/jpg",
				"rule": "ANGLEIMAGES_THUMBNAIL",
				"id": "2231",
				"url": "images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_a_blue.jpg"
			},
			{
				"identifier": "Dress Angle 2 20201 thumbnail image",
				"sequence": 2.0,
				"name.raw": "Luigi Valenti Strapless Sundress Angle 2",
				"description.raw": "Luigi Valenti Strapless Sundress Angle 2",
				"mime": "image/jpg",
				"rule": "ANGLEIMAGES_THUMBNAIL",
				"id": "2232",
				"url": "images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_b_blue.jpg"
			},
			{
				"identifier": "Dress Angle 1 20201 full size image",
				"sequence": 1.0,
				"name.raw": "Luigi Valenti Strapless Sundress Angle 1",
				"description.raw": "Luigi Valenti Strapless Sundress Angle 1",
				"mime": "image/jpg",
				"rule": "ANGLEIMAGES_FULLIMAGE",
				"id": "2233",
				"url": "images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_a_blue.jpg"
			},
			{
				"identifier": "Dress Angle 2 20201 full size image",
				"sequence": 2.0,
				"name.raw": "Luigi Valenti Strapless Sundress Angle 2",
				"description.raw": "Luigi Valenti Strapless Sundress Angle 2",
				"mime": "image/jpg",
				"rule": "ANGLEIMAGES_FULLIMAGE",
				"id": "2234",
				"url": "images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_b_blue.jpg"
			}
		],
		"images": [
			{
				"sequence": 1.0,
				"image": "images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_a_blue.jpg",
				"thumbnail": "images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_a_blue.jpg",
				"name": "Luigi Valenti Strapless Sundress Angle 1"
			},
			{
				"sequence": 2.0,
				"image": "images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_b_blue.jpg",
				"thumbnail": "images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_b_blue.jpg",
				"name": "Luigi Valenti Strapless Sundress Angle 2"
			}
		],
		"__meta": {
			"modified": "2020-08-04T02:20:04.268Z"
		}
	}
}

Stage 8 samples

The following code is an example of the input data for the FindParentCategoryFromDatabase processor:

{
  "CATENTRY_ID": 10001,
  "LANGUAGE_ID": -1,
  "NAME": "Hermitage Fit and Flare Dress-Override                                                                                          ",
  "SHORTDESCRIPTION": "Jewel-toned cocktail dress with fitted bodice and gently flared skirt-Override",
  "KEYWORD": null,
  "FULLIMAGE": null,
  "THUMBNAIL": null,
  "LOCALENAME": "en_US"
}
The FindParentCategoryFromDatabase processor transforms the input data with the store id, language id, and catalog id passed from NiFi FlowFile class as attributes into the following sample output data:

{ "update": { "_id": "1--1-10001-10001", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
	"doc": {
		"category": {
			"10502-1-3-10001": {
				"parent": "10001",
				"sequence": "1",
				"catalog": "10502",
				"path": {
					"name": [
						"Apparel",
						"Women",
						"Dresses"
					],
					"id": [
						"1",
						"3",
						"10001"
					]
				},
				"name": "Dresses"
			}
		},
		"path": {
			"10502": [
				"/1/3/10001"
			]
		}
	},
	"doc_as_upsert": true
}​

Stage 9 samples

The following code is an example of the input data for Rollup for the FindAttributesFromDatabase processor:

{
  "CATENTRY_ID" : 10579,
  "USAGE" : "1, 1",
  "LANGUAGE_ID" : "-1, -1",
  "STOREENT_ID" : "10501, 10501",
  "ATTRTYPE_ID" : "STRING          ###STRING          ",
  "ATTR_ID" : "7000000000000000001, 7000000000000000002",
  "DISPLAY_SEQUENCE" : "1, 1",
  "ATTR_IDENTIFIER" : "swatchSize###swatchcolor",
  "ATTR_SEQUENCE" : "0, 0",
  "DISPLAYABLE" : "1, 1",
  "SEARCHABLE" : "0, 0",
  "COMPARABLE" : "1, 1",
  "FACETABLE" : "0, 0",
  "MERCHANDISABLE" : "0, 0",
  "SWATCHABLE" : "0, 0",
  "ATTR_NAME" : "Available Sizes###Color",
  "GROUPNAME" : " ### ",
  "ATTRVAL_IDENTIFIER" : "S###Silver",
  "STRINGVALUE" : "S###Silver",
  "INTEGERVALUE" : " ### ",
  "FLOATVALUE" : " ### ",
  "ATTRVAL_SEQUENCE" : "1, 1",
  "QTYUNIT_ID" : "C62             ###C62             ",
  "IMAGE1" : "images/catalog/apparel/women/Swatches/swatch_size/size_s_enabled.png###images/catalog/apparel/women/Swatches/swatch_silver.png",
  "IMAGE2" : " ### ",
  "ATTRVAL_ID" : "7000000000000000001, 7000000000000000015",
  "QTY_DESCRIPTION" : "one###one"
}
The FindAttributesFromDatabase processor transforms the input data with store id, language id, and catalog id passed from NiFi FlowFile class as attributes into the following sample output data:

{ "update": { "_id": "1--1-10001-10579", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "attributes": {
      "swatchcolor": {
        "identifier": "swatchcolor",
        "usage": "Defining",
        "displayable": true,
        "merchandisable": false,
        "searchable": false,
        "sequence": 0,
        "name": {
          "normalized": "Color",
          "raw": "Color",
          "text": "Color"
        },
        "facetable": false,
        "id": "7000000000000000002",
        "comparable": true,
        "value": [
          {
            "identifier": "Silver",
            "sequence": 1,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "Silver",
            "raw": "Silver",
            "id": "7000000000000000015",
            "image1": "images/catalog/apparel/women/Swatches/swatch_silver.png"
          }
        ],
        "key": "swatchcolor",
        "swatchable": false
      },
      "swatchsize": {
        "identifier": "swatchSize",
        "usage": "Defining",
        "displayable": true,
        "merchandisable": false,
        "searchable": false,
        "sequence": 0,
        "name": {
          "normalized": "Available Sizes",
          "raw": "Available Sizes",
          "text": "Available Sizes"
        },
        "facetable": false,
        "id": "7000000000000000001",
        "comparable": true,
        "value": [
          {
            "identifier": "S",
            "sequence": 1,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "S",
            "raw": "S",
            "id": "7000000000000000001",
            "image1": "images/catalog/apparel/women/Swatches/swatch_size/size_s_enabled.png"
          }
        ],
        "key": "swatchsize",
        "swatchable": false
      }
    },
    "__meta": {
      "modified": "2020-08-04T02:30:16.091Z"
    }
  }
}

Stage 10 samples

The following code is an example of the input data for the FindAttributesFromDatabase processor:
{
  "CATENTRY_ID": 13296,
  "USAGE": "1, 1, 1, 1, 1, 1, 1",
  "LANGUAGE_ID": "-1, -1, -1, -1, -1, -1, -1",
  "STOREENT_ID": "10501, 10501, 10501, 10501, 10501, 10501, 10501",
  "ATTRTYPE_ID": "STRING          ###STRING          ###STRING          ###STRING          ###STRING          ###STRING          ###STRING          ",
  "ATTR_ID": "7000000000000000138, 7000000000000000142, 7000000000000000142, 7000000000000000142, 7000000000000000141, 7000000000000000141, 7000000000000000141",
  "DISPLAY_SEQUENCE": "0E0, 1.0E0, 1.0E0, 1.0E0, 2.0E0, 2.0E0, 2.0E0",
  "ATTR_IDENTIFIER": "hardware_fasteners_boltsThread size###hardware_fasteners_boltsMaterial###hardware_fasteners_boltsMaterial###hardware_fasteners_boltsMaterial###hardware_fasteners_boltsLength###hardware_fasteners_boltsLength###hardware_fasteners_boltsLength",
  "ATTR_SEQUENCE": "0E0, 0E0, 0E0, 0E0, 0E0, 0E0, 0E0",
  "DISPLAYABLE": "1, 1, 1, 1, 1, 1, 1",
  "SEARCHABLE": "0, 0, 0, 0, 0, 0, 0",
  "COMPARABLE": "1, 1, 1, 1, 1, 1, 1",
  "FACETABLE": "0, 0, 0, 0, 0, 0, 0",
  "MERCHANDISABLE": "0, 0, 0, 0, 0, 0, 0",
  "SWATCHABLE": "0, 0, 0, 0, 0, 0, 0",
  "ATTR_NAME": "Thread size###Material###Material###Material###Length###Length###Length",
  "GROUPNAME": " ### ### ### ### ### ### ",
  "ATTRVAL_IDENTIFIER": "3/4\"-10###Stainless steel###Alloy steel###Brass###2\"###2 1/4\"###3\"",
  "STRINGVALUE": "3/4\"-10###Stainless steel###Alloy steel###Brass###2\"###2 1/4\"###3\"",
  "INTEGERVALUE": " ### ### ### ### ### ### ",
  "FLOATVALUE": " ### ### ### ### ### ### ",
  "ATTRVAL_SEQUENCE": "0E0, 1.0E0, 1.0E0, 1.0E0, 2.0E0, 2.0E0, 2.0E0",
  "QTYUNIT_ID": "C62             ###C62             ###C62             ###C62             ###C62             ###C62             ###C62             ",
  "IMAGE1": " ### ### ### ### ### ### ",
  "IMAGE2": " ### ### ### ### ### ### ",
  "ATTRVAL_ID": "7000000000000002590, 7000000000000002612, 7000000000000002613, 7000000000000002614, 7000000000000002607, 7000000000000002608, 7000000000000002609",
  "QTY_DESCRIPTION": "one###one###one###one###one###one###one"
}   
      
The FindAttributesFromDatabase processor transforms the input data with store id, language id, catalog id passed from NiFi FlowFile class as attributes into the sample output data as the following:
{ "update": { "_id": "1--1-10001-13296", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "attributes": {
      "hardware_fasteners_boltslength": {
        "identifier": "hardware_fasteners_boltsLength",
        "usage": "Defining",
        "displayable": true,
        "merchandisable": false,
        "searchable": false,
        "sequence": 0,
        "name": {
          "normalized": "Length",
          "raw": "Length",
          "text": "Length"
        },
        "facetable": false,
        "id": "7000000000000000141",
        "comparable": true,
        "value": [
          {
            "identifier": "2\"",
            "sequence": 2,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "2\"",
            "raw": "2\"",
            "id": "7000000000000002607"
          },
          {
            "identifier": "2 1/4\"",
            "sequence": 2,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "2 1/4\"",
            "raw": "2 1/4\"",
            "id": "7000000000000002608"
          },
          {
            "identifier": "3\"",
            "sequence": 2,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "3\"",
            "raw": "3\"",
            "id": "7000000000000002609"
          }
        ],
        "key": "hardware_fasteners_boltslength",
        "swatchable": false
      },
      "hardware_fasteners_boltsthreadsize": {
        "identifier": "hardware_fasteners_boltsThread size",
        "usage": "Defining",
        "displayable": true,
        "merchandisable": false,
        "searchable": false,
        "sequence": 0,
        "name": {
          "normalized": "Thread size",
          "raw": "Thread size",
          "text": "Thread size"
        },
        "facetable": false,
        "id": "7000000000000000138",
        "comparable": true,
        "value": [
          {
            "identifier": "3/4\"-10",
            "sequence": 0,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "3/4\"-10",
            "raw": "3/4\"-10",
            "id": "7000000000000002590"
          }
        ],
        "key": "hardware_fasteners_boltsthreadsize",
        "swatchable": false
      },
      "hardware_fasteners_boltsmaterial": {
        "identifier": "hardware_fasteners_boltsMaterial",
        "usage": "Defining",
        "displayable": true,
        "merchandisable": false,
        "searchable": false,
        "sequence": 0,
        "name": {
          "normalized": "Material",
          "raw": "Material",
          "text": "Material"
        },
        "facetable": false,
        "id": "7000000000000000142",
        "comparable": true,
        "value": [
          {
            "identifier": "Stainless steel",
            "sequence": 1,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "Stainless steel",
            "raw": "Stainless steel",
            "id": "7000000000000002612"
          },
          {
            "identifier": "Alloy steel",
            "sequence": 1,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "Alloy steel",
            "raw": "Alloy steel",
            "id": "7000000000000002613"
          },
          {
            "identifier": "Brass",
            "sequence": 1,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "Brass",
            "raw": "Brass",
            "id": "7000000000000002614"
          }
        ],
        "key": "hardware_fasteners_boltsmaterial",
        "swatchable": false
      }
    },
    "__meta": {
      "modified": "2020-08-04T02:55:52.644Z"
    }
  }
}

Stage 11 samples

The following code is an example of the input data for the PushDownParentProperties processor:

{
  "_index": ".auth.1.product.202007021505",
  "_type": "_doc",
  "_id": "1--1-10001-11062",
  "_score": 1,
  "fields": {
    "id.catentry": [
      "11062"
    ],
    "attributes.shape.merchandisable": [
      false
    ],
    "attributes.material.displayable": [
      true
    ],
    "attributes.style.sequence": [
      0
    ],
    "attributes.womenskirtssize.value.unit.identifier": [
      "C62",
      "C62",
      "C62",
      "C62",
      "C62",
      "C62",
      "C62"
    ],
    "attributes.length.value.raw": [
      "Mini cut length"
    ],
    "attributes.occasion.name.normalized": [
      "occasion"
    ],
    "attributes.style.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.material.value.sequence": [
      3
    ],
    "attributes.length.value.id": [
      "7000000000000000369"
    ],
    "attributes.style.value.sequence": [
      1
    ],
    "attributes.cleaning.value.raw": [
      "Machine wash"
    ],
    "id.store": [
      "10501",
      "1"
    ],
    "attributes.womenskirtscolor.value.identifier": [
      "Green"
    ],
    "attributes.cleaning.sequence": [
      11
    ],
    "attributes.length.usage": [
      "Descriptive"
    ],
    "attributes.womenskirtssize.usage": [
      "Defining"
    ],
    "attributes.womenskirtscolor.usage": [
      "Defining"
    ],
    "attributes.womenskirtscolor.name.normalized": [
      "color"
    ],
    "attributes.womenskirtssize.value.identifier": [
      "XS",
      "S",
      "M",
      "L",
      "XL",
      "XXL",
      "XXXL"
    ],
    "attributes.style.identifier": [
      "Style"
    ],
    "attributes.occasion.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.womenskirtscolor.value.unit.identifier": [
      "C62"
    ],
    "attributes.style.merchandisable": [
      false
    ],
    "attributes.length.name.normalized": [
      "length"
    ],
    "attributes.cleaning.value.text": [
      "Machine wash"
    ],
    "attributes.length.value.sequence": [
      2
    ],
    "id.catalog": [
      "10001"
    ],
    "attributes.womenskirtssize.value.id": [
      "7000000000000000316",
      "7000000000000000317",
      "7000000000000000318",
      "7000000000000000319",
      "7000000000000000320",
      "7000000000000000321",
      "7000000000000000322"
    ],
    "attributes.length.key": [
      "length"
    ],
    "attributes.length.name.text": [
      "Length"
    ],
    "attributes.occasion.value.id": [
      "7000000000000000384"
    ],
    "attributes.material.searchable": [
      false
    ],
    "attributes.length.displayable": [
      true
    ],
    "attributes.womenskirtssize.name.text": [
      "Size"
    ],
    "attributes.shape.facetable": [
      false
    ],
    "attributes.material.usage": [
      "Descriptive"
    ],
    "attributes.shape.comparable": [
      true
    ],
    "attributes.cleaning.searchable": [
      true
    ],
    "attributes.womenskirtscolor.sequence": [
      0
    ],
    "attributes.womenskirtssize.searchable": [
      false
    ],
    "attributes.length.facetable": [
      false
    ],
    "attributes.style.value.normalized": [
      "pull on"
    ],
    "attributes.womenskirtscolor.value.normalized": [
      "green"
    ],
    "attributes.occasion.comparable": [
      true
    ],
    "id.language": [
      "-1"
    ],
    "attributes.style.comparable": [
      true
    ],
    "attributes.shape.displayable": [
      true
    ],
    "attributes.style.id": [
      "7000000000000000010"
    ],
    "attributes.shape.value.sequence": [
      0
    ],
    "attributes.length.name.raw": [
      "Length"
    ],
    "attributes.shape.identifier": [
      "Shape"
    ],
    "attributes.style.name.raw": [
      "Style"
    ],
    "attributes.cleaning.facetable": [
      true
    ],
    "attributes.womenskirtscolor.name.text": [
      "Color"
    ],
    "attributes.occasion.name.text": [
      "Occasion"
    ],
    "attributes.material.swatchable": [
      false
    ],
    "attributes.womenskirtssize.swatchable": [
      false
    ],
    "attributes.style.facetable": [
      false
    ],
    "attributes.length.value.normalized": [
      "mini cut length"
    ],
    "attributes.womenskirtssize.name.raw": [
      "Size"
    ],
    "attributes.occasion.name.raw": [
      "Occasion"
    ],
    "attributes.womenskirtscolor.value.raw": [
      "Green"
    ],
    "attributes.womenskirtssize.value.unit.name.raw": [
      "one",
      "one",
      "one",
      "one",
      "one",
      "one",
      "one"
    ],
    "attributes.length.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.womenskirtssize.merchandisable": [
      false
    ],
    "attributes.cleaning.swatchable": [
      false
    ],
    "attributes.womenskirtscolor.comparable": [
      true
    ],
    "attributes.womenskirtssize.value.raw": [
      "XS",
      "S",
      "M",
      "L",
      "XL",
      "XXL",
      "XXXL"
    ],
    "attributes.occasion.value.sequence": [
      4
    ],
    "attributes.womenskirtscolor.displayable": [
      true
    ],
    "attributes.cleaning.usage": [
      "Descriptive"
    ],
    "attributes.material.sequence": [
      0
    ],
    "attributes.shape.sequence": [
      0
    ],
    "attributes.material.facetable": [
      false
    ],
    "attributes.material.key": [
      "material"
    ],
    "attributes.length.sequence": [
      0
    ],
    "attributes.womenskirtscolor.searchable": [
      false
    ],
    "attributes.cleaning.identifier": [
      "Cleaning"
    ],
    "attributes.womenskirtssize.value.normalized": [
      "xs",
      "s",
      "m",
      "l",
      "xl",
      "xxl",
      "xxxl"
    ],
    "attributes.womenskirtssize.displayable": [
      true
    ],
    "attributes.womenskirtscolor.swatchable": [
      false
    ],
    "attributes.cleaning.name.raw": [
      "Cleaning"
    ],
    "attributes.womenskirtscolor.merchandisable": [
      false
    ],
    "attributes.shape.searchable": [
      false
    ],
    "attributes.cleaning.comparable": [
      true
    ],
    "attributes.shape.usage": [
      "Descriptive"
    ],
    "attributes.cleaning.key": [
      "cleaning"
    ],
    "attributes.cleaning.value.id": [
      "7000000000000000324"
    ],
    "attributes.womenskirtssize.facetable": [
      false
    ],
    "attributes.womenskirtssize.identifier": [
      "WomenSkirtsSize"
    ],
    "attributes.cleaning.id": [
      "7000000000000000021"
    ],
    "attributes.cleaning.displayable": [
      true
    ],
    "attributes.style.value.id": [
      "7000000000000000356"
    ],
    "attributes.style.displayable": [
      true
    ],
    "attributes.length.comparable": [
      true
    ],
    "attributes.shape.name.normalized": [
      "shape"
    ],
    "attributes.length.id": [
      "7000000000000000025"
    ],
    "attributes.material.id": [
      "7000000000000000009"
    ],
    "attributes.womenskirtssize.name.normalized": [
      "size"
    ],
    "attributes.occasion.value.raw": [
      "Casual"
    ],
    "attributes.length.identifier": [
      "Length"
    ],
    "attributes.womenskirtscolor.id": [
      "7000000000000000023"
    ],
    "attributes.occasion.identifier": [
      "Occasion"
    ],
    "attributes.occasion.sequence": [
      0
    ],
    "relationship.item.id": [
      "11063",
      "11064"
    ],
    "relationship.variant.id": [
      "3074457345616681168",
      "3074457345616681169"
    ],
    "relationship.variant.sequence": [
      "100.00",
      "200.00"
    ],    
    "attributes.shape.name.raw": [
      "Shape"
    ],
    "attributes.style.key": [
      "style"
    ],
    "attributes.shape.key": [
      "shape"
    ],
    "attributes.style.searchable": [
      false
    ],
    "attributes.womenskirtscolor.value.sequence": [
      0
    ],
    "attributes.womenskirtssize.id": [
      "7000000000000000024"
    ],
    "attributes.length.searchable": [
      false
    ],
    "attributes.cleaning.value.sequence": [
      6
    ],
    "attributes.womenskirtscolor.key": [
      "womenskirtscolor"
    ],
    "attributes.material.identifier": [
      "Material"
    ],
    "relationship.item.sequence": [
      1,
      2
    ],
    "attributes.material.name.raw": [
      "Material"
    ],
    "attributes.womenskirtssize.comparable": [
      true
    ],
    "attributes.occasion.value.normalized": [
      "casual"
    ],
    "attributes.womenskirtssize.sequence": [
      0
    ],
    "attributes.shape.value.id": [
      "7000000000000000402"
    ],
    "relationship.name": [
      "product"
    ],
    "attributes.cleaning.name.normalized": [
      "cleaning"
    ],
    "attributes.womenskirtscolor.identifier": [
      "WomenSkirtsColor"
    ],
    "attributes.material.comparable": [
      true
    ],
    "attributes.occasion.displayable": [
      true
    ],
    "attributes.style.value.raw": [
      "Pull on"
    ],
    "attributes.occasion.id": [
      "7000000000000000011"
    ],
    "attributes.womenskirtscolor.value.id": [
      "7000000000000000311"
    ],
    "attributes.shape.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.material.merchandisable": [
      false
    ],
    "attributes.style.usage": [
      "Descriptive"
    ],
    "attributes.shape.id": [
      "7000000000000000026"
    ],
    "attributes.occasion.searchable": [
      false
    ],
    "attributes.length.merchandisable": [
      false
    ],
    "attributes.occasion.merchandisable": [
      false
    ],
    "attributes.womenskirtscolor.facetable": [
      false
    ],
    "attributes.cleaning.merchandisable": [
      false
    ],
    "attributes.material.value.raw": [
      "Cotton"
    ],
    "attributes.womenskirtssize.value.sequence": [
      1,
      1,
      1,
      1,
      1,
      1,
      1
    ],
    "attributes.womenskirtscolor.value.unit.name.raw": [
      "one"
    ],
    "attributes.womenskirtscolor.name.raw": [
      "Color"
    ],
    "id.member": [
      "7000000000000001001"
    ],
    "attributes.occasion.key": [
      "occasion"
    ],
    "attributes.material.value.id": [
      "7000000000000000341"
    ],
    "attributes.style.name.normalized": [
      "style"
    ],
    "attributes.shape.swatchable": [
      false
    ],
    "attributes.shape.value.raw": [
      "Flocked"
    ],
    "attributes.womenskirtssize.key": [
      "womenskirtssize"
    ],
    "attributes.material.value.normalized": [
      "cotton"
    ],
    "attributes.material.name.text": [
      "Material"
    ],
    "attributes.occasion.usage": [
      "Descriptive"
    ],
    "attributes.cleaning.value.normalized": [
      "machine wash"
    ],
    "attributes.style.swatchable": [
      false
    ],
    "attributes.material.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.cleaning.name.text": [
      "Cleaning"
    ],
    "attributes.shape.name.text": [
      "Shape"
    ],
    "attributes.shape.value.normalized": [
      "flocked"
    ],
    "attributes.occasion.facetable": [
      false
    ],
    "attributes.occasion.swatchable": [
      false
    ],
    "attributes.material.name.normalized": [
      "material"
    ],
    "attributes.style.name.text": [
      "Style"
    ],
    "attributes.cleaning.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.length.swatchable": [
      false
    ]
  }
}
The PushDownParentProperties processor transforms the input data into the following sample output data:
{ "update": { "_id": "1--1-10001-11063", "_index": ".auth.1.product.202007021505", "retry_on_conflict": 5, "_source": false } }
{ "doc": {"attributes":{"occasion":{"identifier":"Occasion","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"occasion","raw":"Occasion","text":"Occasion"},"facetable":false,"id":"7000000000000000011","value":{"identifier":"WCL003_0303","sequence":4,"normalized":"casual","raw":"Casual","id":"7000000000000000384"},"comparable":true,"key":"occasion","swatchable":false},"cleaning":{"identifier":"Cleaning","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":true,"sequence":11,"name":{"normalized":"cleaning","raw":"Cleaning","text":"Cleaning"},"facetable":true,"id":"7000000000000000021","value":{"sequence":6,"identifier":"WCL003_0303","normalized":"machine wash","raw":"Machine wash","text":"Machine wash","id":"7000000000000000324"},"comparable":true,"key":"cleaning","swatchable":false},"shape":{"identifier":"Shape","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"shape","raw":"Shape","text":"Shape"},"facetable":false,"id":"7000000000000000026","comparable":true,"value":{"sequence":0,"identifier":"WCL003_0303","normalized":"flocked","raw":"Flocked","id":"7000000000000000402"},"key":"shape","swatchable":false},"material":{"identifier":"Material","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"material","raw":"Material","text":"Material"},"facetable":false,"id":"7000000000000000009","value":{"sequence":3,"identifier":"WCL003_0303","normalized":"cotton","raw":"Cotton","id":"7000000000000000341"},"comparable":true,"key":"material","swatchable":false},"length":{"identifier":"Length","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"length","raw":"Length","text":"Length"},"facetable":false,"id":"7000000000000000025","value":{"sequence":2,"identifier":"WCL003_0303","normalized":"mini cut length","raw":"Mini cut length","id":"7000000000000000369"},"comparable":true,"key":"length","swatchable":false},"style":{"identifier":"Style","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"style","raw":"Style","text":"Style"},"facetable":false,"id":"7000000000000000010","value":{"identifier":"WCL003_0303","sequence":1,"normalized":"pull on","raw":"Pull on","id":"7000000000000000356"},"comparable":true,"key":"style","swatchable":false}},"relationship":{"product":{"sequence":[1.0],"id":["11062"]}},"__meta":{"modified":"2020-08-04T03:08:32.521Z"}} }
{ "update": { "_id": "1--1-10001-11064", "_index": ".auth.1.product.202007021505", "retry_on_conflict": 5, "_source": false } }
{ "doc": {"attributes":{"occasion":{"identifier":"Occasion","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"occasion","raw":"Occasion","text":"Occasion"},"facetable":false,"id":"7000000000000000011","value":{"identifier":"WCL003_0303","sequence":4,"normalized":"casual","raw":"Casual","id":"7000000000000000384"},"comparable":true,"key":"occasion","swatchable":false},"cleaning":{"identifier":"Cleaning","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":true,"sequence":11,"name":{"normalized":"cleaning","raw":"Cleaning","text":"Cleaning"},"facetable":true,"id":"7000000000000000021","value":{"sequence":6,"identifier":"WCL003_0303","normalized":"machine wash","raw":"Machine wash","text":"Machine wash","id":"7000000000000000324"},"comparable":true,"key":"cleaning","swatchable":false},"shape":{"identifier":"Shape","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"shape","raw":"Shape","text":"Shape"},"facetable":false,"id":"7000000000000000026","comparable":true,"value":{"sequence":0,"identifier":"WCL003_0303","normalized":"flocked","raw":"Flocked","id":"7000000000000000402"},"key":"shape","swatchable":false},"material":{"identifier":"Material","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"material","raw":"Material","text":"Material"},"facetable":false,"id":"7000000000000000009","value":{"sequence":3,"identifier":"WCL003_0303","normalized":"cotton","raw":"Cotton","id":"7000000000000000341"},"comparable":true,"key":"material","swatchable":false},"length":{"identifier":"Length","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"length","raw":"Length","text":"Length"},"facetable":false,"id":"7000000000000000025","value":{"sequence":2,"identifier":"WCL003_0303","normalized":"mini cut length","raw":"Mini cut length","id":"7000000000000000369"},"comparable":true,"key":"length","swatchable":false},"style":{"identifier":"Style","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"style","raw":"Style","text":"Style"},"facetable":false,"id":"7000000000000000010","value":{"identifier":"WCL003_0303","sequence":1,"normalized":"pull on","raw":"Pull on","id":"7000000000000000356"},"comparable":true,"key":"style","swatchable":false}},"relationship":{"product":{"sequence":[2.0],"id":["11062"]}},"__meta":{"modified":"2020-08-04T03:08:33.071Z"}} }
{ "update": { "_id": "1--1-10001-3074457345616681168", "_index": ".auth.1.product.202007021505", "retry_on_conflict": 5, "_source": false } }
{ "doc": {"attributes":{"occasion":{"identifier":"Occasion","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"occasion","raw":"Occasion","text":"Occasion"},"facetable":false,"id":"7000000000000000011","value":{"identifier":"WCL003_0303","sequence":4,"normalized":"casual","raw":"Casual","id":"7000000000000000384"},"comparable":true,"key":"occasion","swatchable":false},"cleaning":{"identifier":"Cleaning","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":true,"sequence":11,"name":{"normalized":"cleaning","raw":"Cleaning","text":"Cleaning"},"facetable":true,"id":"7000000000000000021","value":{"sequence":6,"identifier":"WCL003_0303","normalized":"machine wash","raw":"Machine wash","text":"Machine wash","id":"7000000000000000324"},"comparable":true,"key":"cleaning","swatchable":false},"shape":{"identifier":"Shape","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"shape","raw":"Shape","text":"Shape"},"facetable":false,"id":"7000000000000000026","comparable":true,"value":{"sequence":0,"identifier":"WCL003_0303","normalized":"flocked","raw":"Flocked","id":"7000000000000000402"},"key":"shape","swatchable":false},"material":{"identifier":"Material","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"material","raw":"Material","text":"Material"},"facetable":false,"id":"7000000000000000009","value":{"sequence":3,"identifier":"WCL003_0303","normalized":"cotton","raw":"Cotton","id":"7000000000000000341"},"comparable":true,"key":"material","swatchable":false},"length":{"identifier":"Length","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"length","raw":"Length","text":"Length"},"facetable":false,"id":"7000000000000000025","value":{"sequence":2,"identifier":"WCL003_0303","normalized":"mini cut length","raw":"Mini cut length","id":"7000000000000000369"},"comparable":true,"key":"length","swatchable":false},"style":{"identifier":"Style","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"style","raw":"Style","text":"Style"},"facetable":false,"id":"7000000000000000010","value":{"identifier":"WCL003_0303","sequence":1,"normalized":"pull on","raw":"Pull on","id":"7000000000000000356"},"comparable":true,"key":"style","swatchable":false}},"relationship":{"product":{"sequence":[100.0],"id":["11062"]}},"__meta":{"modified":"2020-08-04T03:08:33.073Z"}} }

Stage 12 samples

The following code is an example of the input data for the FindAttributesFromDatabase processor:
{
  "CATENTRY_ID" : 10350,
  "USAGE" : "2",
  "LANGUAGE_ID" : "-1",
  "STOREENT_ID" : "10501",
  "ATTRTYPE_ID" : "STRING          ",
  "ATTR_ID" : "7000000000000000005",
  "DISPLAY_SEQUENCE" : "1",
  "ATTR_IDENTIFIER" : "material",
  "ATTR_SEQUENCE" : "0",
  "DISPLAYABLE" : "1",
  "SEARCHABLE" : "0",
  "STOREDISPLAY": "0",
  "COMPARABLE" : "1",
  "FACETABLE" : "0",
  "MERCHANDISABLE" : "0",
  "SWATCHABLE" : "0",
  "ATTR_NAME" : "Material",
  "GROUPNAME" : " ",
  "ATTRVAL_IDENTIFIER" : "Cotton blend",
  "STRINGVALUE" : "Cotton blend",
  "INTEGERVALUE" : " ",
  "FLOATVALUE" : " ",
  "ATTRVAL_SEQUENCE" : "23",
  "QTYUNIT_ID" : "C62             ",
  "IMAGE1" : " ",
  "IMAGE2" : " ",
  "ATTRVAL_ID" : "7000000000000000032",
  "QTY_DESCRIPTION" : "one"
}
The FindAttributesFromDatabase processor transforms the input data with store id, language id, catalog id passed from NiFi FlowFile class as attributes into the sample output data as the following:
{ "update": { "_id": "1--1-10001-10350", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }

{
	"doc": {
		"7000000000000000005": {
			"identifier": "material",
			"usage": "Descriptive",
			"displayable": true,
			"ribbon": false,
			"merchandisable": false,
			"searchable": false,
			"sequence": 1.0,
			"name": {
				"normalized": "material",
				"raw": "Material",
				"text": "Material"
			},
			"facetable": false,
			"id": "7000000000000000005",
			"value": {
				"sequence": 23.0,
				"identifier": "Cotton blend",
				"unit": {
					"identifier": "C62",
					"name": {
						"raw": "one"
					}
				},
				"normalized": "cotton blend",
				"raw": "Cotton blend",
				"id": "7000000000000000032"
			},
			"comparable": true,
			"key": "7000000000000000005",
			"swatchable": false
		}
	},
	"__meta": {
		"modified": "2020-08-04T02:55:52.644Z"
	}
}​