Adding formatting to flattened JSON

Question

I've got a pretty complex data flow doing some key/value pair transforms to columns...

But Excel hates my JSON output:

{
    "assignedResourceID": 29682886,
    "assignedResourceRoleID": 29683466,
    "Action Item": "COMPLETE!",
    "Department Reporting": "4W"
}
{
    "assignedResourceID": 29682894,
    "assignedResourceRoleID": 29683458,
    "Action Item": "Done!",
    "Department Reporting": "xZ"
}

The "Array of Documents" gets stripped during DF processing.

Is it possible to format to proper JSON (adding outer brackets and commas):

[{
    "assignedResourceID": 29682886,
    "assignedResourceRoleID": 29683466,
    "Action Item": "COMPLETE!",
    "Department Reporting": "4W"
},
{
    "assignedResourceID": 29682894,
    "assignedResourceRoleID": 29683458,
    "Action Item": "Done!",
    "Department Reporting": "xZ"

Additionally how to pull into a named array?

[{
	"items": [
		{
			"assignedResourceID": 29682886,
			"assignedResourceRoleID": 29683466,
		    "Action Item": "COMPLETE!",
			"Department Reporting": "4W"
		},
		{
    		"assignedResourceID": 29682894,
    		"assignedResourceRoleID": 29683458,
    		"Action Item": "Done!",
    		"Department Reporting": "xZ"

	]
}]

Answer 1

Hi @Brian Newbold

Welcome to Microsoft Q&A platform and thanks for posting your question.

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Ask: How to format the JSON output to include outer brackets and commas, transforming the individual JSON objects into a proper JSON array. How to pull the data into a named array in the JSON format.

Solution:

I've added map drift explicit mapping, which is then used in an aggregate function.

The aggregate does the heavy lift and lets me tuck all the data rows into a named array.

And in Aggregates of aggregate1, I'm using collect to create the array and naming the array column Items

The Map Drifted was used to pull the dynamic key/value pairs into named fields for the collect expression.

And voila! the proper format!

{
    "Items": [
        { ... },
		{ ... }
	]
}

Thanks to: https://learn.microsoft.com/en-us/answers/questions/601502/convert-json-objets-to-array-using-azure-data-flow

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.