Writing Mapping Expressions in Reactor

Mapping Expression Basics

There are multiple types of mapping expressions used in Reactor:

1. A hard-coded value is used when all records should have the same value in an output field
   1. Hard-coded values can be specified by wrapping the desired value in double-quotes (i.e. ")
   2. Example: all Shopify order records should have "order" in the Sound_Order.order_type semantic label so that label is mapped as "order"
2. 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:
   1. 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 the order_number field in the order_id label in the Sound_Order semantic model so that label is mapped as source.order_number
   2. When mapping one or more semantic labels to a derived semantic label: prepend merged. to the field name
      Example: a field called total_shipping in the Sound_Order semantic model can be calculated by subtracting total_shipping_discount from total_shipping_price(both of which are labels in the Sound_Order semantic model), so that label is mapped as merged.total_shipping_price - merged.total_shipping_discount
   3. 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 the order_id label in the Sound_Order semantic model mapped to the order_history destination field order_id so that field is mapped as Sound_Order.order_id
3. An array expression is used when mapping an attribute contained within an array or object. This
   1. 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 like customer.first_name, customer.last_name, customer.email, etc.)
   2. An array is an object that can be repeated multiple times (for example, the Sound_Ordersemantic model contains a line_items array that includes sub-fields like line_items.sku, line_items.quantity, etc. for each product included in an order).
   3. 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:
      1. The Sound_Order semantic model contains a repeatable object called line_items, which itself is comprised of sub-fields like sku, price, quantity, etc.
      2. 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):
      3. 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:
         
4. 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:
   1. event_timestamp: Timestamp when the record was ingested to Reactor
   2. input_event_scid: A unique identifier of the source connector instance deployed in Reactor
   3. input_scid: The unique identifier assigned to each record in Reactor (this is always mapped to the scid field in SoundCommerce destination tables)
   4. sc_generated_uuid: The UUID generated by Reactor according to the source’s Data Standard:
      1. Sources with a Globally Unique identifier defined in the Data Standard: This value will be the value of the Globally Unique field
      2. 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 with source.
         Example: event_timestamp is used to populate a field in many SoundCommerce schemas called parsed_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 as source.event_timestamp.
5. An Operator is a character used in mathematical and logical expressions. A full list of supported operators is below:
   1. ,: Separates two terms in a list, function, or pair
   2. ||: Logical "or"
   3. &&: Logical "and"
   4. == and !=: Equality and inequality operators
   5. <, >, <=, >=: Comparison operators
   6. +: Add
   7. -: Subtract
   8. %: Modulo
   9. ^: Power (exponent)
   10. /: Divide
   11. *: Multiply
   12. !: Negate
   13. ( ): Grouping
6. 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.
   1. 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
   2. Multiple mapping functions can be nested and used in conjunction with each other, for example:
      1. 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)
      2. If records from another sales channel are inserted to your Shopify store, you can use an IF function to determine the value of the Sound_Order.channel semantic label for a given record.
         In this example, Amazon.com marketplace orders are inserted to Shopify, and can be identified by the email 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 whose email 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 a REGEXMATCH function nested within an IF function, the Sound_Order.channel semantic label can be mapped as IF ( REGEXMATCH ( source.email, ".@marketplace\.amazon\.com") , 'Amazon', 'Shopify' )
         Records whose email matches the [alphanumeric string]@marketplace.amazon.com format will have Amazon in the channellabel. All other records will have Shopify in the channel label

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.