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
| Example | JSONata Expression | Source | Result |
|---|---|---|---|
| Retrieve a root-level field | senderName | { "senderName": "STEDI" } | "STEDI" |
Retrieve a city field inside of address | address.city | { "address": { "city": "Los Angeles" }} | "Los Angeles" |
Retrieve a is-europe field inside of address | address."is-europe" (note the "") | { "address": { "is-europe": false}} | false |
Retrieve productID of a second item in orders | orders[1].productID | { "orders": [{ "productID": "DEG32" }, { "productID": "OIU98" }]} | "OIU98" |
Get an array of all orderDates | orders.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 address | address.`country name` (note the ` character) or address."country name" | { "address": { "country name": "USA" }} | "USA" |
Operating on data
| Example | JSONata Expression | Source | Result |
|---|---|---|---|
Concatenate street and city, separated by a space | address.street & " " & address.city | { "address": { "city": "Los Angeles", "street": "1234 Main St." }} | "1234 Main St. Los Angeles" |
Multiply last order's quantity by pricePerUnit | orders[-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
| Example | JSONata Expression | Source | Result |
|---|---|---|---|
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
| Example | JSONata Expression | Source | Result |
|---|---|---|---|
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
| Example | JSONata Expression | Source | Result |
|---|---|---|---|
Get an array of all orders with a quantity greater than 10 | orders[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 200 | orders[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
| Example | JSONata Expression | Source | Result |
|---|---|---|---|
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 ~> operator | orders[$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 10 | orders[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
| Example | JSONata Expression | Source | Result |
|---|---|---|---|
| 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
| Example | JSONata Expression | Source | Result |
|---|---|---|---|
Retrieve the first orderDate field | $$[0].orderDate | [{ "orderDate": "2021/03/17" }] | "2021/03/17" |
Retrieve an array of orderDate fields | orderDate | [{ "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.