StediDOCS

JSONata Cheatsheet

All mappings expressions are based on JSONata language - this page showcases its most important features useful when creating a mapping.

JSON object source document

The source for each example is derived from the following JSON:

{
  "senderName": "STEDI",
  "customerID": "997321",
  "shipmentID": 3312412,
  "shipment type": "ASAP",
  "address": {
    "street": "1234 Main St.",
    "city": "Los Angeles",
    "state": "CA",
    "country name": "USA",
    "is-europe": false
  },
  "orders": [
    {
      "orderDate": "2021/03/17",
      "productID": "DEG32",
      "quantity": 3,
      "pricePerUnit": 5,
      "volume": "10"
    },
    {
      "orderDate": "2021/10/12",
      "productID": "OIU98",
      "quantity": 100,
      "pricePerUnit": 1,
      "volume": "15"
    },
    {
      "orderDate": "2021/01/07",
      "productID": "PWE47",
      "quantity": 45,
      "pricePerUnit": 500,
      "volume": "35"
    }
  ]
}

Retrieving data

ExampleJSONata ExpressionSourceResult
Retrieve a root-level fieldsenderName{ "senderName": "STEDI" }"STEDI"
Retrieve a city field inside of addressaddress.city{ "address": { "city": "Los Angeles" }}"Los Angeles"
Retrieve a is-europe field inside of addressaddress."is-europe" (note the ""){ "address": { "is-europe": false}}false
Retrieve productID of a second item in ordersorders[1].productID{ "orders": [{ "productID": "DEG32" }, { "productID": "OIU98" }]}"OIU98"
Get an array of all orderDatesorders.orderDate{ "orders": [{ "orderDate": "2021/03/17" }, { "orderDate": "2021/10/12" }, { "orderDate": "2021/01/07" }]}["2021/03/17", "2021/10/12", "2021/01/07"]
Retrieve a root-level "shipment type" field with a space in its key`shipment type` (note the ` character){ "shipment type": "ASAP" }"ASAP"
Retrieve a country name field inside of addressaddress.`country name` (note the ` character) or address."country name"{ "address": { "country name": "USA" }}"USA"

Operating on data

ExampleJSONata ExpressionSourceResult
Concatenate street and city, separated by a spaceaddress.street & " " & address.city{ "address": { "city": "Los Angeles", "street": "1234 Main St." }}"1234 Main St. Los Angeles"
Multiply last order's quantity by pricePerUnitorders[-1].quantity * orders[-1].pricePerUnit ([-1] retrieves the last item in an array){ "orders": [{...}, {...}, { "quantity": 45, "pricePerUnit": 500}]}22500
Convert shipmentID to a string$string(shipmentID) ($string is a JSONata function){ "shipmentID": 3312412}"3312412"
Convert customerID to a number$number(customerID){ "customerID": "997321" }"997321"

String manipulation

ExampleJSONata ExpressionSourceResult
Convert senderName to a lowercase string$lowercase(senderName){ "senderName": "STEDI" }"stedi"
Convert address.street to an uppercase string$uppercase(address.street){ "address": { "street": "1234 Main St." }}"1234 MAIN ST."
Get first 4 characters of address.street string$substring(address.street, 0, 4){ "address": { "street": "1234 Main St." }}"1234"
Check if address.street contains "Main" string$contains(address.street, "Main"){ "address": { "street": "1234 Main St." }}true
Get all characters in address.street after "1234" string$substringAfter(address.street, "1234 ") (note the space after "1234"){ "address": { "street": "1234 Main St." }}"Main St."
Replace all /s with - in first orderDate$join($split(orders[0].orderDate, "/"), '-') (note that $split function returns an array){ "orders": [{ "orderDate": "2021/03/17" }]}"2021-03-17"
Replace all /s with - in first orderDate (using ~>)$split(orders[0].orderDate, "/") ~> $join('-'){ "orders": [{ "orderDate": "2021/03/17" }]}"2021-03-17"

Conditionals

ExampleJSONata ExpressionSourceResult
Return "EUR" if is-europe is true, otherwise return "USD"address."is-europe" ? "EUR" : "USD"{ "address": { "is-europe": false}}"USD"
Return "LARGE" if there are more than three orders, otherwise return "SMALL"$count(orders) > 3 ? "LARGE" : "SMALL"{ "orders": [{...}, {...}, {...}]}"SMALL"

Filtering data

ExampleJSONata ExpressionSourceResult
Get an array of all orders with a quantity greater than 10orders[quantity > 10]{ "orders": [{ "orderDate": "2021/03/17", "productID": "DEG32", "quantity": 3, "pricePerUnit": 5 }, { "orderDate": "2021/10/12", "productID": "OIU98", "quantity": 100, "pricePerUnit": 1 },{ "orderDate": "2021/01/07", "productID": "PWE47", "quantity": 45, "pricePerUnit": 500}]}[{ "orderDate": "2021/10/12", "productID": "OIU98", "quantity": 100, "pricePerUnit": 1 },{ "orderDate": "2021/01/07", "productID": "PWE47", "quantity": 45, "pricePerUnit": 500}]
Get orderDate of an order with a productID of "PWE47"orders[productID = "PWE47"].orderDate{ "orders": [{...}, {...}, { "productID": "PWE47", "orderDate": "2021/01/07" }]}"2021/01/07"
Get an array of productIDs of all products where quantity multiplied by pricePerUnit is greater than 200orders[quantity * pricePerUnit > 200].productID[] (note the [] at the end of an expression){ "orders": [{ "orders": [{ "quantity": 3, "pricePerUnit": 5 }, { "quantity": 100, "pricePerUnit": 1 },{ "quantity": 45, "pricePerUnit": 500}]}["PWE47"]

Counting data

ExampleJSONata ExpressionSourceResult
Count all orders with a quantity greater than 50$count(orders[quantity > 50]){ "orders": [{ "orders": [{ "quantity": 3 }, { "quantity": 100 },{ "quantity": 45 }]}1
Count all orders with an orderDate starting with 2021$count(orders[$substring(orderDate, 0, 4) = "2021"]) (note: $substring(orderDate, 0, 4) returns first four characters of a string){ "orders": [{ "orderDate": "2021/03/17" }, { "orderDate": "2021/10/12" }, { "orderDate": "2021/01/07" }]}3
Count all orders with an orderDate starting with 2021 using ~> operatororders[$substring(orderDate, 0, 4) = "2021"] ~> $count{ "orders": [{ "orderDate": "2021/03/17" }, { "orderDate": "2021/10/12" }, { "orderDate": "2021/01/07" }]}3
Add all quantities$sum(orders.quantity){ "orders": [{ "quantity": 3 }, { "quantity": 100 }, { "quantity": 45 }]}148
Add quantities of all products where quantity is bigger than 10orders[quantity > 10].quantity ~> $sum{ "orders": [{ "quantity": 3 }, { "quantity": 100 }, { "quantity": 45 }]}145
Add volume of all products$map(orders.volume, $number) ~> $sum (note that we need to convert all items in orders.volume array to a $number){ "orders": [{ "volume": "10" }, { "volume": "15" }, { "volume": "35" }]}60

Dates

ExampleJSONata ExpressionSourceResult
Get the current date & time in ISO 8601 format$now()N/A"2021-11-04T11:24:41.090Z"
Get the current date & time in 6-character EDI date format$convertDateTime($now(), $dateTime.RFC3339Millis, $dateTime.EDIDate)N/A"211104"
Get the current date & time in 8-character EDI date format$convertDateTime($now(), $dateTime.RFC3339Millis, $dateTime.EDIDateLong)N/A"20211104"
Convert the first orderDate from YYYY/MM/DD format to DD/MM/YYYY$convertDateTime(orders[0].orderDate, "yyyy/MM/dd", "dd/MM/yyyy"){ "orders": [{ "orderDate": "2021/03/17" }, { "orderDate": "2021/10/12" }, { "orderDate": "2021/01/07" }]}"17/03/2021"
Get the month of second orderDate$convertDateTime(orders[1].orderDate, "yyyy/MM/dd", "MM"){ "orders": [{ "orderDate": "2021/03/17" }, { "orderDate": "2021/10/12" }, { "orderDate": "2021/01/07" }]}"10"
Convert the last orderDate from YYYY/MM/DD format to DD-MM-YYYY$convertDateTime(orders[-1].orderDate, "yyyy/MM/dd", "dd-MM-yyyy"){ "orders": [{ "orderDate": "2021/03/17" }, { "orderDate": "2021/10/12" }, { "orderDate": "2021/01/07" }]}"07-01-2021"

JSON array-of-objects source document

The source for each example is derived from the following JSON:

[
  {
    "orderDate": "2021/10/12",
    "productID": "OIU98",
    "quantity": 100,
    "pricePerUnit": 1,
    "address": {
      "street": "1234 Main St.",
      "city": "Los Angeles",
      "state": "CA",
      "country name": "USA",
      "is-europe": false
    }
  },
  {
    "orderDate": "2021/01/07",
    "productID": "PWE47",
    "quantity": 45,
    "pricePerUnit": 500,
    "address": {
      "street": "La Rambla",
      "city": "Barcelona",
      "state": "Barcelona",
      "country name": "Spain",
      "is-europe": true
    }
  }
]

Retrieving data

ExampleJSONata ExpressionSourceResult
Retrieve the first orderDate field$$[0].orderDate[{ "orderDate": "2021/03/17" }]"2021/03/17"
Retrieve an array of orderDate fieldsorderDate[{ "orderDate": "2021/03/17" }, { "orderDate": "2021/10/12" }]["2021/03/17", "2021/10/12"]
Retrieve a is-europe of a second item in address$$[1].address."is-europe" (note the "")[{ "address": { "is-europe": false }}, { "address": {"is-europe": true"}}]true
Retrieve a "country name" of a first item in address with a space in its key$$[0].address.`country name` (note the ` character) or $$[0].address."country name"[{ "address": {"country name": "USA"} }]"USA"

Operating on data

The data operations expressions operate on the same basis as for the JSON object source documents.

String manipulation

The string manipulation expressions operate on the same basis as for the JSON object source documents.

Conditionals

The conditionals expressions operate on the same basis as for the JSON object source documents.

Filtering data

The data filtering expressions operate on the same basis as for the JSON object source documents.

Counting data

The data counting expressions operate on the same basis as for the JSON object source documents.

Dates

The dates expressions operate on the same basis as for the JSON object source documents.