Mapping and Transformation 301 – The Power of JSONata

This page looks at more advanced mapping and transforming data within IBM App Connect

The built-in GUI functions are powerful and easy to use

but there are times you need a bit more – this is where JSONata comes in!



Superhero Transformation

What is JSONata?

An open-source query and transformation language

Lightweight with built-in transform functions

High Powered with minimal syntax

Built in to IBM App Connect

Home Page here jsonata.org



Try it out now!

There’s an on-line JSONata exerciser sandbox

All of our examples are based on this sample data

Watch the video or

Try it out directly at try.jsonata.org

We’ll use the included data structures at try.jsonata.org for our examples

We’ll use the ‘Invoice’ and ‘Address’ structures



JSONata Sample Invoice
JSONata sample address

Example 1: Select all fields with the same name

Select all of the SKUs (Product Codes – Stock Keeping Units) from Invoice

There are four, in two separate orders

JSONata Select SKU

JSONata Expression

Account.Order.Product

try.jsonata.org link

http://try.jsonata.org/H1r5l_5SH

Result

[
  "0406654608",
  "0406634348",
  "040657863",
  "0406654603"
]

Example 2: Select all of the Product Names

(note use of backticks where `field name` has spaces)

However we have duplicates . . “Bowler Hat”

JSONata select product names

JSONata Expression

Account.Order.Product.`Product Name`

try.jsonata.org link

http://try.jsonata.org/HJo5Tt9BS

Result

[
  "Bowler Hat",
  "Trilby hat",
  "Bowler Hat",
  "Cloak"
]

Example 3: Select unique Product Names

(There are three unique names in four products)

Part 1: Create an object with fields as the product names

JSONata select product names

JSONata Expression

Account.Order.Product{`Product Name`:''}

Result

{
  "Bowler Hat": "",
  "Trilby hat": "",
  "Cloak": ""
}

(note this is an Object (with {}) not an array (with [])

Example 3: Select unique Product Names

(There are three unique names in four products)

Part 2: Get the keys as an array

JSONata select product names

JSONata Expression

$keys(Account.Order.Product{`Product Name`:''})

try.jsonata.org link

http://try.jsonata.org/SJws0F9BB

Result

[
  "Bowler Hat",
  "Trilby hat",
  "Cloak"
]

(note this now an array (with [])

Example 4: Totals: Get the total price for the whole account.

Part 1: Get the list of prices multiplied by the quantity

JSONAta totals

JSONata Expression

Account.Order.Product.(Price*Quantity)

Result

[
  68.9,
  21.67,
  137.8,
  107.99
]

Example 4: Totals: Get the total price for the whole account.

Part 2: Total the Order using $Sum()

JSONAta totals

JSONata Expression

$sum(Account.Order.Product.(Price*Quantity))

try.jsonata.org link

http://try.jsonata.org/rkSQx9crr

Result

336.36

Example 5: Max and Min .

Calculate the shipping carton size with the

max of Width, Depth, Height of all the items:

JSONata Expression

Account.Order.Product.Description{
"Max Height":$max(Height),
"Max Width":$max(Width),"Max Depth":$max(Depth)}

try.jsonata.org link

http://try.jsonata.org/HJuSI5qBS

Result

{
  "Max Height": 200,
  "Max Width": 300,
  "Max Depth": 210
}

Example 6: List Each Order Number with the products in that order

JSONAta Orders by lines

JSONata Expression

Account.Order.Product.Description{
Account.Order.{"OrderID":OrderID,
"Product Name":Product.`Product Name`}

try.jsonata.org link

http://try.jsonata.org/HJRXP9qHH

Result

[
  {
    "OrderID": "order103",
    "Product Name": [
      "Bowler Hat",
      "Trilby hat"
    ]
  },
  {
    "OrderID": "order104",
    "Product Name": [
      "Bowler Hat",
      "Cloak"
    ]
  }
]

Example 7: Grouped Totals

Totals: Get the total price for each order

JSONata Expression

Account.Order.{"OrderID":OrderID,
"Total Price":$sum(Product.(Price*Quantity))}

try.jsonata.org link

http://try.jsonata.org/HJRXP9qHH

Result

[
  {
    "OrderID": "order103",
    "Total Price": 90.57
  },
  {
    "OrderID": "order104",
    "Total Price": 245.79
  }
]

Example 8: Filters and Queries

Get all the Purple items

JSONata Expression

Account.Order.Product[Description.Colour='Purple']

try.jsonata.org link

http://try.jsonata.org/r1nE599rB

Result

[
  {
    "Product Name": "Bowler Hat",
    "ProductID": 858383,
    "SKU": "0406654608",
    "Description": {
      "Colour": "Purple",
      "Width": 300,
      "Height": 200,
      "Depth": 210,
      "Weight": 0.75
    },
    "Price": 34.45,
    "Quantity": 2
  },
  {
    "Product Name": "Bowler Hat",
    "ProductID": 858383,
    "SKU": "040657863",
    "Description": {
      "Colour": "Purple",
      "Width": 300,
      "Height": 200,
      "Depth": 210,
      "Weight": 0.75
    },
    "Price": 34.45,
    "Quantity": 4
  }
]

Example 9a: Select Items Conditionally

If there is a mobile number, use that; else use the home number

Part 1: There is a mobile number in the data

JSONata Expression

$exists(Phone[type="mobile"])
?Phone[type="mobile"]:Phone[type="home"]

try.jsonata.org link

http://try.jsonata.org/r1nE599rB

Result

{
  "type": "mobile",
  "number": "077 7700 1234"
}

Example 9b: Select Items Conditionally

If there is a mobile number, use that; else use the home number

Part 2: Part 2: There is no mobile number (changed field to ”not-mobile”)

JSONata Expression

$exists(Phone[type="mobile"])
?Phone[type="mobile"]:Phone[type="home"]

try.jsonata.org link

http://try.jsonata.org/r1nE599rB

Result

{
  "type": "home",
  "number": "0203 544 1234"
}

Example 10: Updating the structure

Adding in a new “total” field

(“total”=Price*Quantity)

JSONata Expression

Account.Order ~>|Product|{"Total": (Price*Quantity)}|

try.jsonata.org link

http://try.jsonata.org/rJLea9cHS

Result

[
  {
    "OrderID": "order103",
    "Product": [
      {
        "Product Name": "Bowler Hat",
        "ProductID": 858383,
        "SKU": "0406654608",
        "Description": {
          "Colour": "Purple",
          "Width": 300,
          "Height": 200,
          "Depth": 210,
          "Weight": 0.75
        },
        "Price": 34.45,
        "Quantity": 2,
        "Total": 68.9
      },
      {
        "Product Name": "Trilby hat",
        "ProductID": 858236,
        "SKU": "0406634348",
        "Description": {
          "Colour": "Orange",
          "Width": 300,
          "Height": 200,
          "Depth": 210,
          "Weight": 0.6
        },
        "Price": 21.67,
        "Quantity": 1,
        "Total": 21.67
      }
    ]
  },
  {
    "OrderID": "order104",
    "Product": [
      {
        "Product Name": "Bowler Hat",
        "ProductID": 858383,
        "SKU": "040657863",
        "Description": {
          "Colour": "Purple",
          "Width": 300,
          "Height": 200,
          "Depth": 210,
          "Weight": 0.75
        },
        "Price": 34.45,
        "Quantity": 4,
        "Total": 137.8
      },
      {
        "ProductID": 345664,
        "SKU": "0406654603",
        "Product Name": "Cloak",
        "Description": {
          "Colour": "Black",
          "Width": 30,
          "Height": 20,
          "Depth": 210,
          "Weight": 2
        },
        "Price": 107.99,
        "Quantity": 1,
        "Total": 107.99
      }
    ]
  }
]

Example 11: Replacing fields:

(Replace WxHxD by “volume”)

JSONata Expression

Account.Order ~>|Product.Description|{"Volume": (Width*Height*Depth)},["Width","Height","Depth"]|

try.jsonata.org link

http://try.jsonata.org/rJIQejqHB

Result

[
  {
    "OrderID": "order103",
    "Product": [
      {
        "Product Name": "Bowler Hat",
        "ProductID": 858383,
        "SKU": "0406654608",
        "Description": {
          "Colour": "Purple",
          "Weight": 0.75,
          "Volume": 12600000
        },
        "Price": 34.45,
        "Quantity": 2
      },
      {
        "Product Name": "Trilby hat",
        "ProductID": 858236,
        "SKU": "0406634348",
        "Description": {
          "Colour": "Orange",
          "Weight": 0.6,
          "Volume": 12600000
        },
        "Price": 21.67,
        "Quantity": 1
      }
    ]
  },
  {
    "OrderID": "order104",
    "Product": [
      {
        "Product Name": "Bowler Hat",
        "ProductID": 858383,
        "SKU": "040657863",
        "Description": {
          "Colour": "Purple",
          "Weight": 0.75,
          "Volume": 12600000
        },
        "Price": 34.45,
        "Quantity": 4
      },
      {
        "ProductID": 345664,
        "SKU": "0406654603",
        "Product Name": "Cloak",
        "Description": {
          "Colour": "Black",
          "Weight": 2,
          "Volume": 126000
        },
        "Price": 107.99,
        "Quantity": 1
      }
    ]
  }
]

Example 12: Whole-Object wildcards::

(Multiply all prices by 10)

JSONata Expression

$~>|**|{"Price":Price*10}|

try.jsonata.org link

http://try.jsonata.org/rJLea9cHS

Result

{
  "Account": {
    "Account Name": "Firefly",
    "Order": [
      {
        "OrderID": "order103",
        "Product": [
          {
            "Product Name": "Bowler Hat",
            "ProductID": 858383,
            "SKU": "0406654608",
            "Description": {
              "Colour": "Purple",
              "Width": 300,
              "Height": 200,
              "Depth": 210,
              "Weight": 0.75
            },
            "Price": 344.5,
            "Quantity": 2
          },
          {
            "Product Name": "Trilby hat",
            "ProductID": 858236,
            "SKU": "0406634348",
            "Description": {
              "Colour": "Orange",
              "Width": 300,
              "Height": 200,
              "Depth": 210,
              "Weight": 0.6
            },
            "Price": 216.7,
            "Quantity": 1
          }
        ]
      },
      {
        "OrderID": "order104",
        "Product": [
          {
            "Product Name": "Bowler Hat",
            "ProductID": 858383,
            "SKU": "040657863",
            "Description": {
              "Colour": "Purple",
              "Width": 300,
              "Height": 200,
              "Depth": 210,
              "Weight": 0.75
            },
            "Price": 344.5,
            "Quantity": 4
          },
          {
            "ProductID": 345664,
            "SKU": "0406654603",
            "Product Name": "Cloak",
            "Description": {
              "Colour": "Black",
              "Width": 30,
              "Height": 20,
              "Depth": 210,
              "Weight": 2
            },
            "Price": 1079.9,
            "Quantity": 1
          }
        ]
      }
    ]
  }
}

Example 13: Redact sensitive data

e.g. by removing price

(could use for password or token)

JSONata Expression

Account.Order ~>|Product|{},["Price"]|

try.jsonata.org link

http://try.jsonata.org/rJ1m-jcrB

Result

[
  {
    "OrderID": "order103",
    "Product": [
      {
        "Product Name": "Bowler Hat",
        "ProductID": 858383,
        "SKU": "0406654608",
        "Description": {
          "Colour": "Purple",
          "Width": 300,
          "Height": 200,
          "Depth": 210,
          "Weight": 0.75
        },
        "Quantity": 2
      },
      {
        "Product Name": "Trilby hat",
        "ProductID": 858236,
        "SKU": "0406634348",
        "Description": {
          "Colour": "Orange",
          "Width": 300,
          "Height": 200,
          "Depth": 210,
          "Weight": 0.6
        },
        "Quantity": 1
      }
    ]
  },
  {
    "OrderID": "order104",
    "Product": [
      {
        "Product Name": "Bowler Hat",
        "ProductID": 858383,
        "SKU": "040657863",
        "Description": {
          "Colour": "Purple",
          "Width": 300,
          "Height": 200,
          "Depth": 210,
          "Weight": 0.75
        },
        "Quantity": 4
      },
      {
        "ProductID": 345664,
        "SKU": "0406654603",
        "Product Name": "Cloak",
        "Description": {
          "Colour": "Black",
          "Width": 30,
          "Height": 20,
          "Depth": 210,
          "Weight": 2
        },
        "Quantity": 1
      }
    ]
  }
]

Example 14: Extensible: Define your own functions

(Example: Reduce “Price” by discount)

JSONata Expression

($applyDiscount:=function($x, $n)
{$x~>|**|{"Price":Price-$n}|};
$applyDiscount(Account.Order[OrderID="order103"],5))

try.jsonata.org link

http://try.jsonata.org/BkpXMoqrH

Result

[{
  "OrderID": "order103",
  "Product": [
    {
      "Product Name": "Bowler Hat",
      "ProductID": 858383,
      "SKU": "0406654608",
      "Description": {
        "Colour": "Purple",
        "Width": 300,
        "Height": 200,
        "Depth": 210,
        "Weight": 0.75
      },
      "Price": 29.45,
      "Quantity": 2
    },
    {
      "Product Name": "Trilby hat",
      "ProductID": 858236,
      "SKU": "0406634348",
      "Description": {
        "Colour": "Orange",
        "Width": 300,
        "Height": 200,
        "Depth": 210,
        "Weight": 0.6
      },
      "Price": 16.67,
      "Quantity": 1
    }
  ]
}

Join The Discussion

Your email address will not be published. Required fields are marked *