Writing Mapping Expressions in Reactor
Mapping Expression Basics
There are multiple types of mapping expressions used in Reactor:
- A hard-coded value is used when all records should have the same value in an output field
- Hard-coded values can be specified by wrapping the desired value in double-quotes (i.e.
"
) - Example: all Shopify order records should have “
order
” in theSound_Order.order_type
semantic label so that label is mapped as"order"
- Hard-coded values can be specified by wrapping the desired value in double-quotes (i.e.
- A field mapping is used when all records should have an input field’s value set as the output field value. There are three types of field mappings:
- When mapping a source field to a semantic label: prepend
source.
to the field name
Example: all Shopify order records should have the value of theorder_number
field in theorder_id
label in theSound_Order
semantic model so that label is mapped assource.order_number
- When mapping one or more semantic labels to a derived semantic label: prepend
merged.
to the field name
Example: a field calledtotal_shipping
in theSound_Order
semantic model can be calculated by subtractingtotal_shipping_discount
fromtotal_shipping_price
(both of which are labels in theSound_Order
semantic model), so that label is mapped asmerged.total_shipping_price - merged.total_shipping_discount
- When mapping one or more semantic labels to a destination field: prepend the name of the semantic model containing the label
Example: All records should have theorder_id
label in theSound_Order
semantic model mapped to theorder_history
destination fieldorder_id
so that field is mapped asSound_Order.order_id
- When mapping a source field to a semantic label: prepend
- An array expression is used when mapping an attribute contained within an array or object. This
- An object is an attribute with one or more sub-fields (for example, the Sound_Order semantic model contains a
customer
object that includes sub-fields likecustomer.first_name
,customer.last_name
,customer.email
, etc.) - An array is an object that can be repeated multiple times (for example, the
Sound_Order
semantic model contains aline_items
array that includes sub-fields likeline_items.sku
,line_items.quantity
, etc. for each product included in an order). - To write an array expression, it is necessary to call out the field within the object or array that is being mapped, followed by an equal sign, . This may seem redundant, but it is necessary, we promise.
The result looks like this:source.{array or object name}.{subfield name} = {field mapped to this label}
For example:- The
Sound_Order
semantic model contains a repeatable object calledline_items
, which itself is comprised of sub-fields likesku
,price
,quantity
, etc. - Ecommerce systems like Shopify can return multiple line items per order, structured like this (we have simplified Shopify’s JSON output for the purposes of this example):
- In order to ensure that attributes at the line-item level are mapped properly, it is necessary to create an Array Expression for each label as follows:
- The
- An object is an attribute with one or more sub-fields (for example, the Sound_Order semantic model contains a
- A metadata mapping is used when all records should have a metadata field mapped to a label or field. The following metadata fields are available:
event_timestamp
: Timestamp when the record was ingested to Reactorinput_event_scid
: A unique identifier of the source connector instance deployed in Reactorinput_scid
: The unique identifier assigned to each record in Reactor (this is always mapped to thescid
field in SoundCommerce destination tables)sc_generated_uuid
: The UUID generated by Reactor according to the source’s Data Standard:- Sources with a Globally Unique identifier defined in the Data Standard: This value will be the value of the Globally Unique field
- Sources with two or more Together Globally Unique identifiers defined in the Data Standard: This value will be a hash of all Together Globally Unique fields concatenated
As with field mappings, metadata mappings consist of the metadata field be prepended withsource.
Example:event_timestamp
is used to populate a field in many SoundCommerce schemas calledparsed_timestamp
. This field is used in the data warehouse to help de-duplicate records that have been updated over time (in the case of an order record sourced from an ecommerce system; a source API will output an updated record for an order after each of the following events occur: the order is placed, payment for the order is processed, the order is shipped to the customer, the order is delivered to the customer). Logging a timestamp when a record was ingested to Reactor enables SQL users downstream of Reactor to select the most recent version of a record in the data warehouse. In Reactor, this is mapped assource.event_timestamp
.
- An Operator is a character used in mathematical and logical expressions. A full list of supported operators is below:
,
: Separates two terms in a list, function, or pair||
: Logical “or”&&
: Logical “and”==
and!=
: Equality and inequality operators<
,>
,<=
,>=
: Comparison operators+
: Add-
: Subtract%
: Modulo^
: Power (exponent)/
: Divide*
: Multiply!
: Negate( )
: Grouping
- A mapping function is used data from one or more input fields and/or hard-coded values is transformed into an output field. For a full list of supported mapping functions, see Function Library, Overview, and Glossary.
- Mapping functions may utilize hard-coded values, field mappings, and/or metadata mappings. When referencing a hard-coded value, field mapping, or metadata mapping in a mapping function, any of the relevant rules above apply
- Multiple mapping functions can be nested and used in conjunction with each other, for example:
- To convert two string fields to numbers and calculate the difference of the resulting numbers, use the following expression:
SUBTRACT(DECIMAL(source.adjustment_principal,source.adjustment_discount)
- If records from another sales channel are inserted to your Shopify store, you can use an
IF
function to determine the value of theSound_Order.channel
semantic label for a given record.
In this example, Amazon.com marketplace orders are inserted to Shopify, and can be identified by theemail
field (Amazon does not share customers’ actual email addresses; instead Amazon provides a masked email address formatted like[alphanumeric string]@marketplace.amazon.com
). Order records whoseemail
value does not match the[alphanumeric string]@marketplace.amazon.com
format are not Amazon orders; in this case that means they are Shopify orders (because there are no other sales channels in this example).
Using aREGEXMATCH
function nested within anIF
function, theSound_Order.channel
semantic label can be mapped asIF ( REGEXMATCH ( source.email, ".@marketplace\.amazon\.com") , 'Amazon', 'Shopify' )
Records whose email matches the[alphanumeric string]@marketplace.amazon.com
format will haveAmazon
in thechannel
label. All other records will haveShopify
in thechannel
label
- To convert two string fields to numbers and calculate the difference of the resulting numbers, use the following expression:
Writing Mapping Expressions in Python
In addition to the mapping functions and expressions documented on this page, users can write mapping expressions in Python. If you are a Python wizard and would rather use Python for all mapping expressions, you have that option. Simply prepend the pound symbol (#
) to the mapping expression, the mapping service processes any expressions that start with a pound as standard Python expressions.