I’ve been working with Logic Apps for the past two years and every time a client comes with a request is an opportunity to learn something new that I haven’t done before. In my latest assignment, I client requested to me to create a Logic Apps that would call a database structure and I needed to transform it to a canonical JSON to be sent as an API response. In this blog post, I’ll show how to transform JSON in Logic Apps using Liquid templates.
The database response and the API response
The information that I will receive from the database is represented in the JSON below. As you see it represents a straightforward table with several columns.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
"body": { "OutputParameters": {}, "ResultSets": { "Table1": [ { "ItemCode": "I1234", "ItemName": "Product 1", "ItemAmount": 2800, "ItemCurrency": "USD", "SerialNumber": "S1234", "StartDate": "2015-03-03T01:54:52.5", "EndDate": "2017-03-03T01:54:52.5", "InWarranty": "false", "OptionType": "REP", "ReplaceItemCode": "151-10010-001", "ReplaceItemName": "Product 2", "ReplaceItemAmount": 433.16, "ReplaceItemCurrency": "USD", "InWarrantyAmount": 0, "InServiceAmount": 216.58, "MinorRepairAmount": 108.29, "ExtendedWarrantyAmount": 0, "InWarrantyPercentual": 0, "InServicePercentual": 50, "MinorRepairPercentual": 25, "ExtendedWarrantyPercentual": 5, "ExtendedWarrantyMonthsExtension": 0 } ] } } |
The API response that I need to return from the Logic App execution is not as straightforward as the table above, and I’ll have to break it down in several parts with specific loops to have the replacement and upgrades section in the final JSON. Below is how the response should look like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
{ "item": { "description": "Product 1", "itemNo": "I1234" }, "replacement": { "availableInventory": null, "extendedWarrantyPrice": { "amount": 0, "currency": "USD", "maxMonths": "0" }, "inServicePrice": { "amount": 216.58, "currency": "USD" }, "itemNo": "I5678", "listPrice": { "amount": 433.16, "currency": "USD" }, "name": "Product 2", "option": "REP", "warrantyPrice": { "amount": 0, "currency": "USD" } }, "upgrades": [ { "availableInventory": null, "extendedWarrantyPrice": { "amount": 7.291666666666666, "currency": "USD", "maxMonths": 36 }, "inServicePrice": { "amount": 875, "currency": "USD" }, "itemNo": "I9012", "listPrice": { "amount": 1750, "currency": "USD" }, "name": "Product 3", "option": "UP1", "warrantyPrice": { "amount": 437.5, "currency": "USD" } }, { "availableInventory": null, "extendedWarrantyPrice": { "amount": 18.745833333333334, "currency": "USD", "maxMonths": 36 }, "inServicePrice": { "amount": 2249.5, "currency": "USD" }, "itemNo": "I3456", "listPrice": { "amount": 4499, "currency": "USD" }, "name": "Product 4", "option": "UP2", "warrantyPrice": { "amount": 1124.75, "currency": "USD" } } ], "warrantyDetails": { "endDate": "2017-03-03T01:54:52.5", "inWarranty": false, "invoiceNo": null, "startDate": "2015-03-03T01:54:52.5" } } |
The Logic App
Let’s start with the structure of the Logic Apps and the initial solution that I came up with using Compose and Filter actions and why I decided to move to use the Liquid template instead.
The Logic App is composed basically with an HTTP request connector, a SQL connector, and an HTTP response. To create the JSON response in the format the client needed, I used as helpers, an array variable, filters and compose actions as you can see in the picture below:
In general I don’t mind using the compose action to create the JSON’s that I need to use in the Logic Apps that I design, but in this case, due to the complexity of the JSON response that I needed to return, I ended up with 6 extra actions, plus the for each to handle an array section inside the JSON. You can see the code of this version of the Logic App here: LiquidLogicApp/WithFiltersAndComposes.json
So, I went to look for a better solution and right off the bat, the idea was to use a Liquid template with the Integration Account, which will make my Logic App to call only one extra action instead of 6 and the design will look much cleaner.
In this proposed new design, my Logic Apps will resemble like the picture below:
Much better right? You can find the ARM Template for this Logic App here: LiquidLogicApp/WithLiquid.json
But now I need to move all the Logic that I have in the filters and composes into the Liquid template. Let’s see how that will work it out.
Liquid Template
The first thing we need to do is choose an editor for our Liquid template and I decided to use Visual Studio Code for this. Visual Studio Code has plugins we can add to help us validate the syntax of the Liquid template as well as snippets that will help us with the coding. I used the Liquid Languages Support plugin.
Now that we have an editor we can learn about the syntax in this link: https://shopify.github.io/liquid/
The language provides us with operators, types, and flow control that will enable us to do more complex maps in an easier way than using Filter and Compose as I did in the first version of my Logic Apps.
The final implementation of the Liquid template that I did is as shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
{ "item": { "description": "{{ content.first.ItemName }}", "itemNo": "{{ content.first.ItemCode }}" }, {% for item in content %} {% if item.OptionType == 'REP' %} "replacement": { "availableInventory": null, "extendedWarrantyPrice": { "amount": {{ item.ExtendedWarrantyAmount }}, "currency": "{{ item.ReplaceItemCurrency }}", "maxMonths": "{{ item.ExtendedWarrantyMonthsExtension }}" }, "inServicePrice": { "amount": {{ item.InServiceAmount }}, "currency": "{{ item.ReplaceItemCurrency }}" }, "itemNo": "", "listPrice": { "amount": {{ item.ReplaceItemAmount }}, "currency": "{{ item.ReplaceItemCurrency }}" }, "name": "{{ item.ReplaceItemName }}", "option": "{{ item.OptionType }}", "warrantyPrice": { "amount": {{ item.InWarrantyAmount }}, "currency": "{{ item.ReplaceItemCurrency }}" } }, {% endif %} {% endfor %} "upgrades": [ {% for item in content %} {% if item.OptionType != 'REP' %} { "availableInventory": null, "extendedWarrantyPrice": { "amount": {{ item.ExtendedWarrantyAmount }}, "currency": "{{ item.ReplaceItemCurrency }}", "maxMonths": "{{ item.ExtendedWarrantyMonthsExtension }}" }, "inServicePrice": { "amount": {{ item.InServiceAmount }}, "currency": "{{ item.ReplaceItemCurrency }}" }, "itemNo": "", "listPrice": { "amount": {{ item.ReplaceItemAmount }}, "currency": "{{ item.ReplaceItemCurrency }}" }, "name": "{{ item.ReplaceItemName }}", "option": "{{ item.OptionType }}", "warrantyPrice": { "amount": {{ item.InWarrantyAmount }}, "currency": "{{ item.ReplaceItemCurrency }}" } } {% if forloop.last == false%},{% endif %} {% endif %} {% endfor %} ], "warrantyDetails": { "endDate": "{{ content.first.EndDate }}", "inWarranty": {{ content.first.InWarranty }}, "invoiceNo": null, "startDate": "{{ content.first.StartDate }}" } } |
Note that at the beginning of my Liquid template I used the element content. This is the way that the Logic App connector encapsulated my JSON database payload, so you will have to use it to access all other data you are passing.
Also, note that in the Liquid template there’s a validation to check if the loop execution is the last one so I could add or not a comma to separate the elements in my JSON array response. Got this for @ToonVanhoute in his blog post Liquid in API Management.
Summary
Even though I had to type manually the map (Liquid template), it was not so hard to understand the syntax to achieve my goals in the JSON response. And overall my solution got much less complex and much more maintainable than the one that I did with a lot of actions in the Logic App.
However, from someone that has a strong background in creating maps for BizTalk with the BizTalk mapper, I’m missing a lot to have a good tool to create the maps in an easier way, but VS Code and the Liquid plugins cover a little bit of that and hopefully Microsoft is planning to have better tools in the future for the creation of the Liquid Templates.
