Rearrange existing array

Question

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|Gary@example.net",
            "DisplayName": "Gary",
            "Email": "Gary@example.net",
            "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|Martin@example.net",
            "DisplayName": "Martin@example",
            "Email": "Martin@example.net",
            "Department": "Maintenance",
            "JobTitle": "Maintenance Manager"
        }
    },
    {
        "Ref": "B0050",
        "Desc": "Estimating Tool",
        "User": {
            "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            "Claims": "i:0#.f|membership|Toni@example.net",
            "DisplayName": "Toni",
            "Email": "Toni@example.net",
            "Department": "Construction",
            "JobTitle": "Construction Manager"
        }
    },
    {
        "Ref": "B0050",
        "Desc": "Estimating Tool",
        "User": {
            "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            "Claims": "i:0#.f|membership|Rob@example.net",
            "DisplayName": "Rob",
            "Email": "Rob@example.net",
            "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|Gary@example.net",
            "DisplayName": "Gary",
            "Email": "Gary@example.net",
            "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": "Gary@example.net"
        "Project": {
            "Ref": "B0048",
            "Desc": "Electricity Management"
        },
        {
            "Ref": "B0051",
            "Desc": "New Software Business Case"
        }
    },
    {
        "DisplayName": "Martin",
        "Email": "Martin@example.net"
        "Project": {
            "Ref": "B0048",
            "Desc": "Electricity Management"
        }
    },
    {
        "DisplayName": "Toni",
        "Email": "Toni@example.net"
        "Project": {
            "Ref": "B0050",
            "Desc": "Estimating Tool"
        }
    },
    {
        "DisplayName": "Rob",
        "Email": "Rob@example.net"
        "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!

Answer 1

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

[
  {
    "DisplayName": "Gary",
    "Email": "Gary@example.net",
    "ProjectRef": [
      "B0048",
      "B0051"
    ],
    "ProjectDesc": [
      "Electricity Management",
      "New Software Business Case"
    ]
  },
  {
    "DisplayName": "Martin@example",
    "Email": "Martin@example.net",
    "ProjectRef": [
      "B0049"
    ],
    "ProjectDesc": [
      "New Hardware Business Case"
    ]
  },
  {
    "DisplayName": "Toni",
    "Email": "Toni@example.net",
    "ProjectRef": [
      "B0050"
    ],
    "ProjectDesc": [
      "Estimating Tool"
    ]
  },
  {
    "DisplayName": "Rob",
    "Email": "Rob@example.net",
    "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.