Via a liquid template in Azure APIM I want to transform below json to a different output, taking only certain values from the 'data' element, e.g. the 'GROUP(tranid)':
Sample JSON
{
"result_error": false,
"result_errorDescription": "",
"saved_search": "customsearch_int_package_info",
"page_size": 1000,
"page_index": "0",
"total_pages": 1,
"columns": [
"numbertext"
],
"joins": "",
"operators": [
"haskeywords"
],
"filters": [
"IF123820"
],
"data": [
{
"values": {
"GROUP(tranid)": "IF123820",
"GROUP(createdFrom.tranid)": "SOAU100577",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_box_number)": "- None -",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_tracking_number)": "SHYIZ00048446001",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_volume)": ".0016",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_weight)": "1",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_length)": "40",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_width)": "20",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_height)": "2",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.externalid)": [
{
"value": "__RANDOM_ID__455307",
"text": "- None -"
}
]
}
}
]
}
I have written the following template for APIM; it is working fine except for the records listed under value. How can I fetch specific values from that section, e.g. 'IF123820' from 'GROUP(tranid)'?
This is my liquid template (with some of the calls I tried out so far):
{
"saved_search": "{{body.saved_search}}",
"page_size": "{{body.page_size}}",
"Status": "Success",
"Message": "PackageDetails successfully found",
"Filters": "{{body.filters}}",
" Test1 " : {{body.data[0].values.GROUP(tranid)}},
" body.data ": {{body.data[0]}} ,
"Package":
{% for item in body.data %}
{% for item2 in item.values %}
"Test2": "{{item[0]}}",
"Test3": "{{item.GROUP(tranid)}}",
"Test4": "{{item2}}",
"Test5": {{item[0].GROUP(tranid)}},
"Test6": {{item[0]values[GROUP(tranid)]}},
}
{% endfor %}
{% endfor %}
}
It seems that it is only possible to retrieve all records from the 'data.values' section or to retrieve the single values via the second loop; but that does not solve my problem to retrieve a specific record only.
Here is part of the result I get via the template above:
"...
{
"saved_search": "customsearch_int_package_info",
"page_size": "1000",
"Status": "Success",
"Message": "PackageDetails successfully found",
"Filters": "IF123820",
" Test1 ": ,
" body.data ": "values": {
"GROUP(tranid)": "IF123820",
"GROUP(createdFrom.tranid)": "SOAU100577",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_box_number)": "- None -",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_tracking_number)": "SHYIZ00048446001",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_volume)": ".0016",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_weight)": "1",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_length)": "40",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_width)": "20",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_height)": "2",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.externalid)": [{
"value": "__RANDOM_ID__455307",
"text": "- None -"
}]
},
"Package":
"Test2": "{
"GROUP(tranid)": "IF123820",
"GROUP(createdFrom.tranid)": "SOAU100577",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_box_number)": "- None -",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_tracking_number)": "SHYIZ00048446001",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_volume)": ".0016",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_weight)": "1",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_length)": "40",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_width)": "20",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_height)": "2",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.externalid)": [{
"value": "__RANDOM_ID__455307",
"text": "- None -"
}]
}
",
"Test3": "",
"Test4": "IF123820",
"Test5": ,
"Test6": ,
}
"Test2": "{
"GROUP(tranid)": "IF123820",
"GROUP(createdFrom.tranid)": "SOAU100577",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_box_number)": "- None -",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_tracking_number)": "SHYIZ00048446001",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_volume)": ".0016",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_weight)": "1",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_length)": "40",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_width)": "20",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_height)": "2",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.externalid)": [{
"value": "__RANDOM_ID__455307",
"text": "- None -"
}]
}
",
"Test3": "",
"Test4": "SOAU100577",
"Test5": ,
"Test6": ,
}
"Test2": "{
"GROUP(tranid)": "IF123820",
"GROUP(createdFrom.tranid)": "SOAU100577",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_box_number)": "- None -",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_tracking_number)": "SHYIZ00048446001",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_volume)": ".0016",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_weight)": "1",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_length)": "40",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_width)": "20",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_height)": "2",
"GROUP(CUSTRECORD_BGB_TNX_NUMBER.externalid)": [{
"value": "__RANDOM_ID__455307",
"text": "- None -"
}]
}
",
"Test3": "",
"Test4": "- None -",
"Test5": ,
"Test6": ,
}
..."
Any suggestions to solve this are welcome :-)
Last but not least I found the answer. This liquid code worked to retrieve the values :
"Package": {
{% for item in body.data %}
"OrderNumber" : "{{item.values["GROUP(createdFrom.tranid)"]}}",
"BoxNumber" : "{{item.values["GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_box_number)"]}}",
"TrackingNumber" : "{{item.values["GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_tracking_number)"]}}",
"PackageWeight" : "{{item.values["GROUP(CUSTRECORD_BGB_TNX_NUMBER.custrecord_bgb_package_weight)"]}}"
{% endfor %}
}