How to parse a complex JSON with LogicApps and Key/Value pairs
I have a fairly complex JSON coming from Microsoft D365 Events where I need to derive some fields from to save it into a SQL Server. I want to use Logic Apps for that. Since I'm not sure about schema drifts, I don't want to use Indexes to address the fields, I would like to leverage the Keys in the JSON. This is a (very much shortened) JSON sample:
[
{
"key": "Target",
"value": {
"__type": "Entity:http://schemas.microsoft.com/xrm/2011/Contracts",
"Id": "00000000-0000-0000-000000000000",
"KeyAttributes": [],
"LogicalName": "mserp_custcustomerv2entity",
"RowVersion": null,
"Attributes": [
{
"key": "mserp_customeraccount",
"value": "FA-001"
},
{
"key": "mserp_partytype",
"value": "Organization"
},
{
"key": "mserp_organizationname",
"value": "Fabric US"
},
{
"key": "mserp_namealias",
"value": "Fabric US"
},
{
"key": "mserp_knownas",
"value": ""
},
{
"key": "mserp_customergroupid",
"value": "30"
},
{
"key": "mserp_organizationnumber",
"value": ""
},
{
"key": "mserp_organizationnumberofemployees",
"value": 0
},
{
"key": "mserp_organizationabccode",
"value": {
"__type": "OptionSetValue:http://schemas.microsoft.com/xrm/2011/Contracts",
"Value": 200000000
}
},
{
"key": "mserp_custcustomerv2entityid",
"value": "00000000-0000-0000-000000000000"
}
],
"EntityState": null,
"FormattedValues": [
{
"key": "mserp_dataareaid_id",
"value": "USMF"
}
],
"RelatedEntities": []
}
},
{
"key": "PreImage",
"value": {
"__type": "Entity:http://schemas.microsoft.com/xrm/2011/Contracts",
"Id": "00000000-0000-0000-000000000000",
"KeyAttributes": [],
"LogicalName": "mserp_custcustomerv2entity",
"RowVersion": null,
"Attributes": [
{
"key": "mserp_customeraccount",
"value": "FA-001"
},
{
"key": "mserp_partytype",
"value": "Organization"
},
{
"key": "mserp_organizationname",
"value": "Fabric US"
},
{
"key": "mserp_namealias",
"value": "Fabric US"
},
{
"key": "mserp_knownas",
"value": ""
},
{
"key": "mserp_customergroupid",
"value": "30"
},
{
"key": "mserp_organizationnumber",
"value": ""
},
{
"key": "mserp_organizationnumberofemployees",
"value": 0
},
{
"key": "mserp_organizationabccode",
"value": {
"__type": "OptionSetValue:http://schemas.microsoft.com/xrm/2011/Contracts",
"Value": 200000000
}
},
{
"key": "mserp_custcustomerv2entityid",
"value": "00000000-0000-0000-000000000000"
}
],
"EntityState": null,
"FormattedValues": [
{
"key": "mserp_dataareaid_id",
"value": "USMF"
}
],
"RelatedEntities": []
}
},
{
"key": "ChangedFields",
"value": []
}
]
I found some references using looping and filtering out the different fields but that would be a quite complex thing to manage.
I would like to have some "path" based on the keys I can somehow use in Logic Apps, preferred with a build-in action to have the different fields from the JSON easily in variables so that I can use them easily to bring it i.e. into a SQL Server with the standard SQL action.
You said ...
preferred with a build-in action ...
... and bottom line, there's nothing simple when it comes to converting all of that key value pair data to an array of objects.
I have two approaches for you though.
Select Using XPath
You can use a select and XPath expressions to extract the data. It's quick but verbose AND it won't work dynamically. If a new attribute is added, it won't do the conversion for you and include that attribute automatically.
This is the code view for the Select step ...
{
"inputs": {
"from": "@outputs('Compose')",
"select": {
"mserp_customeraccount": "@first(xpath(xml(json(concat('{ \"root\": { \"item\": ', item(), '}}'))), '//Attributes[key=\"mserp_customeraccount\"]/value[1]/text()'))",
"mserp_customergroupid": "@first(xpath(xml(json(concat('{ \"root\": { \"item\": ', item(), '}}'))), '//Attributes[key=\"mserp_customergroupid\"]/value[1]/text()'))",
"mserp_knownas": "@first(xpath(xml(json(concat('{ \"root\": { \"item\": ', item(), '}}'))), '//Attributes[key=\"mserp_knownas\"]/value[1]/text()'))",
"mserp_namealias": "@first(xpath(xml(json(concat('{ \"root\": { \"item\": ', item(), '}}'))), '//Attributes[key=\"mserp_namealias\"]/value[1]/text()'))",
"mserp_organizationname": "@first(xpath(xml(json(concat('{ \"root\": { \"item\": ', item(), '}}'))), '//Attributes[key=\"mserp_organizationname\"]/value[1]/text()'))",
"mserp_organizationnumber": "@first(xpath(xml(json(concat('{ \"root\": { \"item\": ', item(), '}}'))), '//Attributes[key=\"mserp_organizationnumber\"]/value[1]/text()'))",
"mserp_organizationnumberofemployees": "@first(xpath(xml(json(concat('{ \"root\": { \"item\": ', item(), '}}'))), '//Attributes[key=\"mserp_organizationnumberofemployees\"]/value[1]/text()'))",
"mserp_partytype": "@first(xpath(xml(json(concat('{ \"root\": { \"item\": ', item(), '}}'))), '//Attributes[key=\"mserp_partytype\"]/value[1]/text()'))"
}
}
}
Execute Javascript Code
You can use the in-line Javascript operation to run some code over your data to get the desired outcome. This will work dynamically but requires an integration account. That's not the biggest deal, just something to note.
This is the code ...
var jsonData = workflowContext.actions.Compose.inputs;
// Initialize an empty object to store the processed data
const processedDataArray = [];
// Loop through the JSON data
jsonData.forEach((item) => {
if (item.key === "Target" || item.key === "PreImage") {
// Find the "Attributes" section
const attributes = item.value.Attributes;
// Initialize an empty object to store the processed attributes
const processedAttributes = {};
// Loop through the attributes and convert them into key-value pairs in the processedAttributes object
attributes.forEach((attribute) => {
processedAttributes[attribute.key] = attribute.value;
});
// Push the processedAttributes object to the processedDataArray
processedDataArray.push(processedAttributes);
}
});
return processedDataArray;
This is the output, which you may need to clean up depending on the type of value that each property holds ...
[
{
"mserp_customeraccount": "FA-001",
"mserp_partytype": "Organization",
"mserp_organizationname": "Fabric US",
"mserp_namealias": "Fabric US",
"mserp_knownas": "",
"mserp_customergroupid": "30",
"mserp_organizationnumber": "",
"mserp_organizationnumberofemployees": 0,
"mserp_organizationabccode": {
"Value": 200000000,
"__type": "OptionSetValue:http://schemas.microsoft.com/xrm/2011/Contracts"
},
"mserp_custcustomerv2entityid": "00000000-0000-0000-000000000000"
},
{
"mserp_customeraccount": "FA-001",
"mserp_partytype": "Organization",
"mserp_organizationname": "Fabric US",
"mserp_namealias": "Fabric US",
"mserp_knownas": "",
"mserp_customergroupid": "30",
"mserp_organizationnumber": "",
"mserp_organizationnumberofemployees": 0,
"mserp_organizationabccode": {
"Value": 200000000,
"__type": "OptionSetValue:http://schemas.microsoft.com/xrm/2011/Contracts"
},
"mserp_custcustomerv2entityid": "00000000-0000-0000-000000000000"
}
]