StediDOCS

Mappings

Mappings is a tool that maps JSON documents from one structure to another. For example, if you have a document that looks like this…

{
  "product": {
    "id": "QL-5490S",
    "price": "USD 500"
  }
}

…but you need it to look like this…

{
  "product_number": "QL-5490S",
  "price": {
    "currency": "USD",
    "amount": 500
  }
}

…then Mappings can help you. Once you've created the proper mapping expressions, Mappings can turn any document with the first structure into a document with the second structure. There are many situations where this is helpful, including:

  • You receive data from a trading partner, but it's not in the structure that your own software system can handle.
  • You need to call an API and it expects data in a structure that's different than what your own software system uses.
  • You want to map the data on a purchase order directly onto an invoice.

Creating a mapping

When you create a mapping, you need to provide two JSON samples: a source and a target. The source should be an example of the data you have, and the target should be an example of the data as you want it to be. So, if you receive data from a trading partner and you need it in a format that your own software understands, then the data from the trading partner is your source, and the format as your software understands it is the target.

Note: You don't specify the source schema and target schema directly. Instead, you give Mappings two concrete JSON examples and it will derive the schemas from those.

Mappings expects you to select target keys. If you want to end up with output that looks exactly like the sample you provided, then you should select all keys. If you only need a subset of the fields that are in the sample, then you only select the keys you're interested in.

Working with EDI

Mappings works with JSON. That doesn't seem particularly helpful if you're dealing with EDI, but don't worry: we've got you covered. EDI Translate can translate your EDI to JEDI, which is a JSON representation of EDI. Once you have your JEDI, you can map it with Mappings. You can also use EDI Translate to translate the resulting JSON back into EDI.

Mapping expressions

Basic mapping expressions

For every field in your target, you need to write an expression that tells Mappings where to find the data in the source. The simplest expression just points to a single source field.

Source

{
  "telephone": "+(1)(303) 555-0100"
}

Target

{
  "phone_number": "+(1)(303) 555-0100"
}
Basic mapping rules 1

The name of the target is on the right, so the output will contain the key phone_number. On the left is the mapping expression. In this case, it's simply the name of a field in the source.

Note: You start with a target field and write a mapping expression to select the corresponding source field.

If the field in the source is nested, you can use a path to select it.

Source

{
  "business": {
    "contact": {
      "telephone": "+(1)(303) 555-0100"
    }
  }
}

Target

{
  "phone_number": "+(1)(303) 555-0100"
}
Basic mapping rules 2

A path contains the key names at every level of a field, separated by dots.

It can be tedious to write out the path for every mapping expression, especially if you have a source with a deeply nested structure. Fortunately, you can just click on the field in the source and it will be copied to your clipboard.

Lists

To map a list in the source to a list in the target, you need to take two steps.

  1. Write a mapping expression to specify which list you need from the source.
  2. Write a mapping expression for each field inside the list of your target.

Source

{
  "transaction": {
    "order": {
      "products": [
        {
          "id": "QL-5490S",
          "amount": 17,
          "price": {
            "unit": 840,
            "total": 14280
          }
        },
        {
          "id": "LV-69200",
          "amount": 91,
          "price": {
            "unit": 15,
            "total": 1365
          }
        },
        {
          "id": "RD-0392P",
          "amount": 1,
          "price": {
            "unit": 930,
            "total": 930
          }
        }
      ]
    }
  }
}

Target

{
  "orders": [
    {
      "product_number": "FF08CD",
      "quantity": 1,
      "unit_price": 20
    }
  ]
}

In the target, the list is called orders. The field will be marked with the word array. In the source, the list that contains the relevant data has the path transaction.order.products, so that's the mapping expression you need for orders.

Lists 1

Info: Array is another word for list.

Next, you can specify a mapping expression for each field in orders. For example, the field product_number is called id in the source.

Lists 2

The mapping expressions for the fields in the list don't include transaction.order.products, because Mappings knows that those fields are relative to the context of the list. For that reason, the mapping expression for a list is referred to as a looping context.

Info: Loop is another word for array.

Note: If products is a single-item array, by default a transaction.order.products.id expression will return a single ID (not wrapped in an array). If you'd like to ensure that you'll always end up with an array - add [] to the end of an expression to always wrap the result in an array (regardless of amount of items returned).

Example: | input | expression | output | | ---------------------------------------------------------------------- | --------------------------------- | ----------------------------------- | | {"transaction": {"order": {"products": [{"id": "1"}, {"id": "2"}]}}} | transaction.order.products.id | ["1", "2"] | | {"transaction": {"order": {"products": [{"id": "1"}]}}} | transaction.order.products.id | "1" (not wrapped in an array) | | {"transaction": {"order": {"products": [{"id": "1"}, {"id": "2"}]}}} | transaction.order.products.id[] | ["1", "2"] | | {"transaction": {"order": {"products": [{"id": "1"}]}}} | transaction.order.products.id[] | ["1"] | | | | |

Advanced mapping expressions

Mappings allows you to do more advanced things than selecting fields. Unless you're an experienced programmer, writing complex mapping expressions will take some getting used to. We'll provide some common patterns here. If you're looking for more, check out our mapping expressions cheatsheet.

Tip: Advanced mapping expressions can get quite long. Click on the green icon next to the mapping expression you're editing to open the Transformation Editor. This will give you more space to work with.

Text to number

Sometimes, your source will have quotes around a number. When that happens, Mappings thinks it's dealing with text. You can convert the text to a number by using the $number function.

Source

{
  "quantity": "8"
}

Target

{
  "quantity": 8
}
Text to number

Number to text

If your target contains a number in quotes, then to Mappings, that's text instead of a number. You can convert the number to text by using the $string function.

Source

{
  "quantity": 8
}

Target

{
  "quantity": "8"
}
Number to text

Info: String is another word for text.

Calculations

You can do calculations on numbers using *, /, +, and -.

Source

{
  "price": 500,
  "quantity": 8,
  "discount": 150
}

Target

{
  "total": 3850
}
Calculations 1

If the numbers in the source are surrounded by quotes, you need to convert them first using the $number function.

Source

{
  "subtotal": "500",
  "vat": "10"
}

Target

{
  "total": 510
}
Calculations 2

Sum and average

You can calculate the sum and average of a list of numbers using $sum and $average.

Source

{
  "prices": [14280, 1365, 930]
}

Target

{
  "sum": 16575,
  "average": 5525
}
Sum and average 1

Often, the numbers you're interested in are part of a more complex structure.

Source

{
  "orders": [
    {
      "product_id": "QL-5490S",
      "price": 14280
    },
    {
      "product_id": "LV-69200",
      "price": 1365
    },
    {
      "product_id": "RD-0392P",
      "price": 930
    }
  ]
}

Target

{
  "sum": 16575,
  "average": 5525
}

In this case, you can refer to the field you're interested in—price in this case—by its full path. Make sure to put [] after the name of the list—orders in this case—to let Mappings know that you want all prices in the list.

Sum and average 2

Putting text together

When you have to two text fields and you want to put them together, you can use &.

Source

{
  "first_name": "Alice",
  "last_name": "Mahara"
}

Target

{
  "name": "Alice Mahara"
}
Putting text together

Taking text apart

You can extract a small part out of a text by using $substring. You need to specify where the part is that you're interested in, so this only works for text that follows a predictable pattern.

Source

{
  "phone_number": "(303) 555-0100"
}

Target

{
  "area_code": "303"
}
Taking text apart 1

The two numbers let $substring know where the part begins, and how many letters you want. $substring starts counting characters at 0, so in the example above, we start at the second characters.

If the part you're interested in is at the back of the text, you can use a negative number to tell $substring to start counting from the last character.

Source

{
  "phone_number": "(303) 555-0100"
}

Target

{
  "local_number": "555-0100"
}
Taking text apart 2

Splitting text

Sometimes a text contains multiple pieces of data, separated by a character. You can turn the text into a list using $split.

Source

{
  "location": "Chicago, Illinois, United States"
}

Target

{
  "location": ["Chicago", "Illinois", "United States"]
}
Splitting text 1

You can assign each item in the list to a field by using an index, which is a number between square brackets. Items in a list are numbered starting at 0.

Source

{
  "location": "Chicago, Illinois, United States"
}

Target

{
  "city": "Chicago",
  "state": "Illinois",
  "country": "United States"
}
Splitting text 2

Lookup table

If you have a field that contains a code that you want to replace with a related value, you can build a lookup table.

Source

{
  "country": "USA"
}

Target

{
  "country": "United States"
}

The mapping expression for the lookup table is typically quite long, so it's easiest to use the transformation editor for this. You can open the transformation editor by clicking the green icon next to mapping expression.

Lookup table

Don't forget to include the ( at the beginning and the ) at the end.

More information

Mapping expressions are written in a language called JSONata, and it provides many more possibilities than just the ones described above. If you want to know more, you can check out the following sources.

  • Mapping expression cheatsheet – A collection of common patterns for mapping expressions, just like the ones above, but more of them.
  • JSONata documentation – A complete description of the JSONata language, written for people with programming experience.

Mapping types

Only mapped keys

By default, Mappings will add the target keys you selected to the output and nothing else. In the following example, only amount has a mapping expression, so currency doesn't show up in the output.

Target

{
  "currency": "USD",
  "amount": 500
}

Mapping expressions

Only mapped keys

Input

{
  "price": 250
}

Output

{
  "amount": 250
}

For mappings of type only mapped keys, if you do not provide a mapping expression for a given selected target field, the structure the field resides in will be preserved. To avoid producing empty objects or arrays when all children fields of a given key are unmapped, consider de-selecting unmapped target keys using the "Select keys" option.

Merge with target example

If you need to produce JSON based on a template, and you only need to change a handful of fields, it's easier if you copy the entire target to the output and only make those few changes. For example, you may want to use a default currency for your output.

Target

{
  "currency": "USD",
  "amount": 500
}

Mapping expression

Merge with target example

Input

{
  "price": 250
}

Output

{
  "currency": "USD",
  "amount": 250
}

In this example, the input doesn't contain a currency, so the output uses the default as provided by the target. If the input does contain a currency, then it will override the default.

Target

{
  "currency": "USD",
  "amount": 500
}

Mapping expression

Merge with target example

Input

{
  "currency": "CAD",
  "price": 250
}

Output

{
  "currency": "CAD",
  "amount": 250
}

Pass through

If your output needs to be much like your input, but with a few values changed, then you can tell Mappings to start with a copy of your input.

Target

{
  "price": 250,
  "amount": 250,
  "discount": 20
}

Mapping expression

Pass through

Input

{
  "amount": 250,
  "discount": 20
}

Output

{
  "price": 250,
  "amount": 250,
  "discount": 20
}

For mappings of type pass through, if you do not provide a mapping expression for a given selected target field, the structure the field resides in will be preserved. To avoid producing empty objects or arrays when all children fields of a given key are unmapped, consider de-selecting unmapped target keys using the "Select keys" option.

API Reference