I'm trying to create an useable array that I can loop through.
Starting JSON looks like this:
"body": {
"count": 116,
"value": [
{
"id": 1,
"workItemId": 23515,
"rev": 1,
"revisedBy": {
"id": "redacted",
"name": "redacted",
"displayName": "redacted",
"url": "redacted",
"_links": {
"avatar": {
"href": "redacted"
}
},
"uniqueName": "redacted",
"imageUrl": "redacted",
"descriptor": "redacted"
},
"revisedDate": "2023-11-30T16:32:00.577Z",
"fields": {
"System.Id": {
"newValue": 23515
},
"System.AreaId": {
"newValue": 234
},
"System.NodeName": {
"newValue": "DevOps"
},
"System.AreaLevel1": {
"newValue": "Contoso"
},
"System.AreaLevel2": {
"newValue": "Shared Services"
},
"System.AreaLevel3": {
"newValue": "DevOps"
},
"System.Rev": {
"newValue": 1
},
"System.AuthorizedDate": {
"newValue": "2023-11-30T16:32:00.16Z"
},
"System.RevisedDate": {
"newValue": "2023-11-30T16:32:00.577Z"
},
"System.IterationId": {
"newValue": 203
},
"System.IterationLevel1": {
"newValue": "Contoso"
},
"System.IterationLevel2": {
"newValue": "2023Q4"
},
"System.WorkItemType": {
"newValue": "User Story"
},
"System.State": {
"newValue": "New"
},
"System.Reason": {
"newValue": "New"
},
"System.AssignedTo": {},
"System.CreatedDate": {
"newValue": "2023-11-30T16:32:00.16Z"
},
"System.CreatedBy": {
"newValue": {
"displayName": "redacted",
"url": "redacted",
"_links": {
"avatar": {
"href": "redacted"
}
},
"id": "redacted",
"uniqueName": "redacted",
"imageUrl": "redacted",
"descriptor": "redacted"
}
}
}
]
}
I'm attempting to create an array of 'fields' and loop through each key.
If I use the createarray() function to initialize a variable, I'm left with this:
"value": [
{
"System.AreaId": {
"oldValue": 233,
"newValue": 234
},
"System.AreaPath": {
"oldValue": "IPFS\\Shared Services\\Database",
"newValue": "IPFS\\Shared Services\\DevOps"
},
"System.NodeName": {
"oldValue": "Database",
"newValue": "DevOps"
},
"System.AreaLevel3": {
"oldValue": "Database",
"newValue": "DevOps"
},
"System.Rev": {
"oldValue": 113,
"newValue": 114
},
"System.AuthorizedDate": {
"oldValue": "2023-12-22T13:15:14.493Z",
"newValue": "2023-12-22T13:34:31.793Z"
},
"System.RevisedDate": {
"oldValue": "2023-12-22T13:34:31.793Z",
"newValue": "9999-01-01T00:00:00Z"
},
"System.ChangedDate": {
"oldValue": "2023-12-22T13:15:14.493Z",
"newValue": "2023-12-22T13:34:31.793Z"
},
"System.Watermark": {
"oldValue": 126007,
"newValue": 126014
},
"System.BoardLane": {
"newValue": "ADO Automation"
},
"WEF_292A6CA8129248189CA52399E366EBC5_System.ExtensionMarker": {
"oldValue": false,
"newValue": true
},
"WEF_B8D7CB8BBE4B44ABB66660A5EDCCB21E_System.ExtensionMarker": {
"oldValue": true,
"newValue": false
},
"WEF_C63F4704F90D431A8B59108A6997DBEE_System.ExtensionMarker": {
"oldValue": true,
"newValue": false
}
}
]
This creates one single index. What I'm after is this:
"value": [
"System.AreaId": {
"oldValue": 233,
"newValue": 234
},
"System.AreaPath": {
"oldValue": "IPFS\\Shared Services\\Database",
"newValue": "IPFS\\Shared Services\\DevOps"
},
"System.NodeName": {
"oldValue": "Database",
"newValue": "DevOps"
},
"System.AreaLevel3": {
"oldValue": "Database",
"newValue": "DevOps"
},
"System.Rev": {
"oldValue": 113,
"newValue": 114
},
"System.AuthorizedDate": {
"oldValue": "2023-12-22T13:15:14.493Z",
"newValue": "2023-12-22T13:34:31.793Z"
},
"System.RevisedDate": {
"oldValue": "2023-12-22T13:34:31.793Z",
"newValue": "9999-01-01T00:00:00Z"
},
"System.ChangedDate": {
"oldValue": "2023-12-22T13:15:14.493Z",
"newValue": "2023-12-22T13:34:31.793Z"
},
"System.Watermark": {
"oldValue": 126007,
"newValue": 126014
},
"System.BoardLane": {
"newValue": "ADO Automation"
},
"WEF_292A6CA8129248189CA52399E366EBC5_System.ExtensionMarker": {
"oldValue": false,
"newValue": true
},
"WEF_B8D7CB8BBE4B44ABB66660A5EDCCB21E_System.ExtensionMarker": {
"oldValue": true,
"newValue": false
},
"WEF_C63F4704F90D431A8B59108A6997DBEE_System.ExtensionMarker": {
"oldValue": true,
"newValue": false
}
]
Any ideas how to make this possible?
Here is a solution using good old string replace.
Example input:
{
"value": [
{
"System.AreaId": {
"oldValue": 233,
"newValue": 234
},
"System.AreaPath": {
"oldValue": "IPFS\\Shared Services\\Database",
"newValue": "IPFS\\Shared Services\\DevOps"
},
"System.NodeName": {
"oldValue": "Database",
"newValue": "DevOps"
},
"System.AreaLevel3": {
"oldValue": "Database",
"newValue": "DevOps"
}
}
]
}
Example output:
{
"value": [
{
"field": "System.AreaId",
"oldValue": 233,
"newValue": 234
},
{
"field": "System.AreaPath",
"oldValue": "IPFS\\Shared Services\\Database",
"newValue": "IPFS\\Shared Services\\DevOps"
},
{
"field": "System.NodeName",
"oldValue": "Database",
"newValue": "DevOps"
},
{
"field": "System.AreaLevel3",
"oldValue": "Database",
"newValue": "DevOps"
}
]
}
Power Automate expression used:
json(
replace(
replace(
replace(
replace(
string(
outputs('Compose') // replace this with your variable/action
),
'[{', '[{"field":'),
'},', '},{"field":'),
':{', ','),
'}}', '}')
)