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:

Loading product document
Pushing down product level properties to its items
Rolling up item level properties to its parent
Building catalog hierarchy and navigation paths for each sales category

Note: Definition for field type aliases (in italics) is described in Index Field Type Aliases 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.

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:

Creating a Product document
Applying Site level override
Associating subscriptions
Associating parent and child product relationship
Applying product set relationship for contract entitlement
Generating merchandising association
Associating product attachments and angle images
Building catalog hierarchy for navigation
Associating attributes
Rolling up item level attributes to their parents
Pushing down product level properties to their items
Associating child descriptive attributes
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 completions, 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	The 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	The 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, milliliterAttribute 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, yardAttribute 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 FindAttributesFromDatabase 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"
	}
}

Ingest Product index pipeline

Product data specification

Product index field mapping from data specification

Product index field mapping from database

Stage 1: Creating Product document

Stage 2: Applying Site level override

Stage 3: Associating subscriptions

Stage 4: Associating parent and child product relationship

Stage 5: Applying product set relationship for contract entitlement

Stage 6: Generating merchandising associations

Stage 7: Associating product attachments and angle images

Stage 8: Building catalog hierarchy for navigation​​

Stage 9: Associating attributes

Stage 10: Rolling up item level attributes to their parents

Stage 11: Pushing down product level properties to its items

Stage 12: Associating child descriptive attributes

Stage 13: Associating child attachments

Database mapping samples:

Stage 1 samples

Stage 2 samples

Stage 3 samples

Stage 4 samples

Stage 5 samples

Stage 6 samples

Stage 7 samples

Stage 8 samples

Stage 9 samples

Stage 10 samples

Stage 11 samples

Stage 12 samples

Stage 8: Building catalog hierarchy for navigation