After flattening and un/pivoting sublists how to get proper JSON format?

Question

Successfully converting input JSON file with dynamic custom fields in key/value pairs, but struggling on getting proper JSON output from data flow.

[
    {
        "items": [
                        {
                "id": 70927,
                "assignedResourceID": 29682937,
                "assignedResourceRoleID": 29683467,
                "userDefinedFields": [
                    {
                        "name": "Action Item",
                        "value": "Follow up with business team re: UAT approval"
                    },
                    {
                        "name": "Department Reporting",
                        "value": "4W"
                    }
                ]
            },
            {
                "id": 70928,
                "assignedResourceID": 29682894,
                "assignedResourceRoleID": 29683467,
                "userDefinedFields": [
                    {
                        "name": "Action Item",
                        "value": "Follow-up on deployment readiness"
                    },
                    {
                        "name": "Department Reporting",
                        "value": "4W"
                    }
                ]
            }
        ],
        "pageDetails": {
            "count": 13,
            "requestCount": 500,
            "prevPageUrl": "someurl",
            "nextPageUrl": null
        }
    }
]

The pagination above runs across 5 pages, and user defined fields are key/value pair added dynamically when data exists.
Using the structure below to make a simple table structure for use in Excel/PowerBI.

I'm getting a really nicely flattened output, but it's not proper JSON format and Excel hates it.
Pagination gone, converted the key/values to columns.. but it's now missing wrapper brackets [ ] and comma separation between lines }, {

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

Question is how can I get the proper formatting in there?

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

Additionally, can I pull this into an 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 query here.

As per my understanding you are trying to transform data using mapping dataflow where you want to convert the lines of json into item of array .

You can add an aggregate transformation , go to aggregate tab to create a new column named 'items' and use the below expression:

collect(@(assignedResourceID=assignedResourceID,assignedResourceRoleID=assignedResourceRoleID,Action Item=Action Item,Department Reporting=Department Reporting))

Kindly refer to the following thread for similar scenario : How to add a comma between JSON sections in Azure Data Factory

Please let us know in case you are facing any issue during the implementation.

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou