Rearrange existing array

I have a dataset that looks somewhat like the example below:

[
    {
        "Ref": "B0048",
        "Desc": "Electricity Management",
        "User": {
            "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            "Claims": "i:0#.f|membership|[email protected]",
            "DisplayName": "Gary",
            "Email": "[email protected]",
            "Department": "N/A",
            "JobTitle": "Big Boss"
        }
    },
    {
        "Ref": "B0049",
        "Desc": "New Hardware Business Case",
        "User": {
            "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            "Claims": "i:0#.f|membership|[email protected]",
            "DisplayName": "Martin@example",
            "Email": "[email protected]",
            "Department": "Maintenance",
            "JobTitle": "Maintenance Manager"
        }
    },
    {
        "Ref": "B0050",
        "Desc": "Estimating Tool",
        "User": {
            "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            "Claims": "i:0#.f|membership|[email protected]",
            "DisplayName": "Toni",
            "Email": "[email protected]",
            "Department": "Construction",
            "JobTitle": "Construction Manager"
        }
    },
    {
        "Ref": "B0050",
        "Desc": "Estimating Tool",
        "User": {
            "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            "Claims": "i:0#.f|membership|[email protected]",
            "DisplayName": "Rob",
            "Email": "[email protected]",
            "Department": "Construction",
            "JobTitle": "Construction Coordinator"
        }
    },
    {
        "Ref": "B0051",
        "Desc": "New Software Business Case",
        "User": {
            "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            "Claims": "i:0#.f|membership|[email protected]",
            "DisplayName": "Gary",
            "Email": "[email protected]",
            "Department": "N/A",
            "JobTitle": "Big Boss"
        }
    }
]

Of note is that in some cases there are multiple Projects to one person, and in others there are multiple people to one project. What I'm looking to do is use Power Automate to rearrange the components to produce the following output so I can generate some targeted communications...

[
    {
        "DisplayName": "Gary",
        "Email": "[email protected]"
        "Project": {
            "Ref": "B0048",
            "Desc": "Electricity Management"
        },
        {
            "Ref": "B0051",
            "Desc": "New Software Business Case"
        }
    },
    {
        "DisplayName": "Martin",
        "Email": "[email protected]"
        "Project": {
            "Ref": "B0048",
            "Desc": "Electricity Management"
        }
    },
    {
        "DisplayName": "Toni",
        "Email": "[email protected]"
        "Project": {
            "Ref": "B0050",
            "Desc": "Estimating Tool"
        }
    },
    {
        "DisplayName": "Rob",
        "Email": "[email protected]"
        "Project": {
            "Ref": "B0050",
            "Desc": "Estimating Tool"
        }
    }
]

I've tried many combinations of Select, Filter Array, Append to Array using Apply to Each loops. So far I either get an error message or a successful flow producing useless output (or none at all).

I've been fumbling around in the dark for too long trying to make it work and am not really getting anywhere, so I figured it would be a much more productive use of time to ask the brains trust for some direction!

Solution

The best I could come up with is the following format without using loops:

[
  {
    "DisplayName": "Gary",
    "Email": "[email protected]",
    "ProjectRef": [
      "B0048",
      "B0051"
    ],
    "ProjectDesc": [
      "Electricity Management",
      "New Software Business Case"
    ]
  },
  {
    "DisplayName": "Martin@example",
    "Email": "[email protected]",
    "ProjectRef": [
      "B0049"
    ],
    "ProjectDesc": [
      "New Hardware Business Case"
    ]
  },
  {
    "DisplayName": "Toni",
    "Email": "[email protected]",
    "ProjectRef": [
      "B0050"
    ],
    "ProjectDesc": [
      "Estimating Tool"
    ]
  },
  {
    "DisplayName": "Rob",
    "Email": "[email protected]",
    "ProjectRef": [
      "B0050"
    ],
    "ProjectDesc": [
      "Estimating Tool"
    ]
  }
]

If that is close enough then here are the steps:

Details:

Step: JsonInput
Action: Compose
Inputs: Your starting Json above.


Step: toXML
Action: Compose
Inputs:
  xml(json(concat('{"payload":{"projects":', string(outputs('JsonInput')), '}}')))


Step: Select
Action: Select
From:
  union(xpath(outputs('toXML'), '//User/Email/text()'), json('[]'))
Map:
  DisplyName:
    xpath(
      outputs('toXML'), 
      concat('(//User/Email[text()="', item(), '"])[1]/../DisplayName/text()')
    )?[0]

  Email:
    item()

  ProjectRef:
    xpath(
      outputs('toXML'),
      concat('//User/Email[text()="', item(), '"]/../../Ref/text()')
    )

  ProjectDesc:
    xpath(
      outputs('toXML'),
      concat('//User/Email[text()="', item(), '"]/../../Desc/text()')
    )

Notes:

toXML - convert Json to XML, but as it is an array, we will need to nest it under two nodes (hence "payload", and "project").
Select - the union here removes duplicates on the email address.