Convert nested json values to array

[KBoeykens]

Hi,

I'm trying to create an array of tasks from a nested JSON input.

My json input looks like this:

	[{
		"desc":"temp1",
		"id":"422",
		"priority":"LOW",
		"occurrences": [
			{"idx":0,"status":"CLOSED","fireTime":"2022-07-19T10:30:00"}
		]
	},
	{
		"desc":"temp2",
		"id":"423",
		"priority":"MEDIUM",
		"occurrences": [
			{"idx":0,"status":"CLOSED","fireTime":"2022-07-19T10:30:00"},
			{"idx":1,"status":"CLOSED","fireTime":"2022-08-19T10:30:00"},
			{"idx":2,"status":"CLOSED","fireTime":"2022-09-19T14:30:00"},
			{"idx":3,"status":"CLOSED","fireTime":"2022-10-19T14:30:00"}
		]
	},
	{
		"desc":"temp3",
		"id":"424",
		"priority":"HIGH",
		"occurrences": [
			{"idx":0,"status":"CLOSED","fireTime":"2022-07-21T10:30:00"},
			{"idx":1,"status":"CLOSED","fireTime":"2022-08-21T18:48:00"},
			{"idx":2,"status":"CLOSED","fireTime":"2022-09-18T18:48:00"},
			{"idx":3,"status":"CLOSED","fireTime":"2022-10-16T18:48:00"},
			{"idx":4,"status":"CLOSED","fireTime":"2022-11-13T18:48:00"},
			{"idx":5,"status":"CLOSED","fireTime":"2022-11-14T11:00:00"}
		]
	}]

The result should like like this:

Desc	Id	Priority	FireTime
temp1	422	LOW	2022-07-19T10:30:00
temp2	423	MEDIUM	2022-07-19T10:30:00
temp2	423	MEDIUM	2022-08-19T10:30:00
temp2	423	MEDIUM	2022-09-19T14:30:00
temp2	423	MEDIUM	2022-10-19T14:30:00
temp3	424	HIGH	2022-07-21T10:30:00
temp3	424	HIGH	2022-08-21T18:48:00
temp3	424	HIGH	2022-09-18T18:48:00
temp3	424	HIGH	2022-10-16T18:48:00
temp3	424	HIGH	2022-11-13T18:48:00
temp3	424	HIGH	2022-11-14T11:00:00

I can get the list of tasks with the first fireTime, but not a record for each fireTime.

My current implementation:

The current result is:

[
  {
    "Id": "423",
    "Description": "temp1",
    "Priority": "LOW",
    "FireTime": "2022-07-19T10:30:00"
  },
  {
    "Id": "423",
    "Description": "temp2",
    "Priority": "MEDIUM",
    "FireTime": "2022-07-19T10:30:00"
  },
  {
    "Id": "424",
    "Description": "temp3",
    "Priority": "HIGH",
    "FireTime": "2022-07-21T10:30:00"
  }
]

 I tried an "Apply for each" loop but I'm not getting onto the level of the occurrences>firetime.

Could someone help me getting this?

The output should be returned to my PowerApp.

[grantjenkins]

This is potentially how I would do it. Note that I've just taken your data provided and added to a Compose.

See full flow below. I'll go into each of the actions.

Data is a Compose that contains the data you provided. You would get this from your HTTP request.

Initialize variable creates an array variable called data and sets it to an empty array. This will eventually contain all the data.

Apply to each iterates over each of the objects in the array (output from Data).

Select uses the following expressions to build up the objects for each task.

//From (input)
items('Apply_to_each')?['occurrences']

//Id
items('Apply_to_each')?['id']

//Description
items('Apply_to_each')?['desc']

//Priority
items('Apply_to_each')?['priority']

//FireTime
item()?['fireTime']

Union is a Compose that uses the union expression to join the output from the Select with the existing objects in the data array. The expression used is:

union(variables('data'), body('Select'))

Set variable takes the combined data (output from Union) and sets it as the new value for our data variable.

And that's it. The data array should now contain all the objects in a single array. I've added a Compose after the Apply to each just to show the output of our data array.

After running the flow, we would see the following output.

[
  {
    "Id": "422",
    "Description": "temp1",
    "Priority": "LOW",
    "FireTime": "2022-07-19T10:30:00"
  },
  {
    "Id": "423",
    "Description": "temp2",
    "Priority": "MEDIUM",
    "FireTime": "2022-07-19T10:30:00"
  },
  {
    "Id": "423",
    "Description": "temp2",
    "Priority": "MEDIUM",
    "FireTime": "2022-08-19T10:30:00"
  },
  {
    "Id": "423",
    "Description": "temp2",
    "Priority": "MEDIUM",
    "FireTime": "2022-09-19T14:30:00"
  },
  {
    "Id": "423",
    "Description": "temp2",
    "Priority": "MEDIUM",
    "FireTime": "2022-10-19T14:30:00"
  },
  {
    "Id": "424",
    "Description": "temp3",
    "Priority": "HIGH",
    "FireTime": "2022-07-21T10:30:00"
  },
  {
    "Id": "424",
    "Description": "temp3",
    "Priority": "HIGH",
    "FireTime": "2022-08-21T18:48:00"
  },
  {
    "Id": "424",
    "Description": "temp3",
    "Priority": "HIGH",
    "FireTime": "2022-09-18T18:48:00"
  },
  {
    "Id": "424",
    "Description": "temp3",
    "Priority": "HIGH",
    "FireTime": "2022-10-16T18:48:00"
  },
  {
    "Id": "424",
    "Description": "temp3",
    "Priority": "HIGH",
    "FireTime": "2022-11-13T18:48:00"
  },
  {
    "Id": "424",
    "Description": "temp3",
    "Priority": "HIGH",
    "FireTime": "2022-11-14T11:00:00"
  }
]

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[KBoeykens]

Thanks for the answer.

I have implemented it as you proposed, but it seems the flow is runnning now for a long time:

Meanwhile, it's already running for >10 min. I think I will cancel the flow.

Is it due to the amount of items?

[grantjenkins · Accepted solution]

If you've got over 1000 tasks, and within each task a number of occurrences, then it will take a little while to complete.

IGNORE THE POST BELOW - I TESTED AND IT WON'T WORK IN THIS CASE

One way to speed it up quite a bit is to turn on Concurrency for the Apply to each. To do this, go into Settings, then turn on Concurrency Control and set the Degree of Parallelism to around 20.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[KBoeykens]

Thanks for this great tip! Parallelism did the trick. Within 2 min all my tasks are processed. For me it made a big difference!

[grantjenkins]

@KBoeykens I initially thought of using the concurrency, but for this it won't work properly due to us merging the original array. It will complete, but the final array won't contain all the items. I'll try to think of an alternative solution that's a bit quicker.