Power Apps how to create a Nested Collection from multiple SharePoint Lists then create a nested JSON Collection

I have 3 SharePoint Lists

Service List Server List Disk List

Which I have put into collection in a canvass Power App

colService colServer colDisk

colService has a unique ID (serviceID) colServer has the serviceID and a unique ID (serverID) colDisk has the serviceID has the serverID and a unique ID (diskID)

What I want to do is create a new nested collection that I can convert to JSON.

For example the final JSON would look something like this

{
    "Service": "New Service",
    "ServiceOwner": "me@example.com",
    "Servers": [
        {
            "ServerName": "testsv1",
            "OS": "Windows Server 2022 Core",
            "CPU": "4",
            "RAM": "16",
            "Disks": [
                {
                    "MP": "C",
                    "Size": "50"
                },
                {
                    "MP": "D",
                    "Size": "80"
                }
            ]
        },
        {
            "ServerName": "testsv2",
            "OS": "Windows Server 2022 Minimal",
            "CPU": "8",
            "RAM": "32",
            "Disks": [
                {
                    "MP": "C",
                    "Size": "90"
                },
                {
                    "MP": "D",
                    "Size": "45"
                }
            ]
        }
    ]
}

Thanks

Solution

You can "join" the three collections using something similar to the expression below:

ClearCollect(
    allServices,
    AddColumns(
        colService As theService,
        Servers,
        AddColumns(
            Filter(colServer As theServer, theServer.ServiceId = theService.ServiceId) As serversForService,
            Disks,
            Filter(colDisks As theDisk, theDisk.ServiceId = theService.ServiceId And theDisk.ServerId = serversForService.ServerId))))

If you don't want the id columns to be present in the final collection, you can drop them while creating the collection:

ClearCollect(
    allServices,
    DropColumns(
        AddColumns(
            colService As theService,
            Servers,
            DropColumns(
                AddColumns(
                    Filter(colServer As theServer, theServer.ServiceId = theService.ServiceId) As serversForService,
                    Disks,
                    DropColumns(
                        Filter(colDisks As theDisk, theDisk.ServiceId = theService.ServiceId And theDisk.ServerId = serversForService.ServerId),
                        DiskId, ServerId, ServiceId)),
                ServerId, ServiceId)),
        ServiceId))

You can test this by creating sample tables using the expressions below:

ClearCollect(
  colService,
  { ServiceId: 1, Service: "New Service", ServiceOwner: "me@example.com" });

ClearCollect(
  colServer,
  { ServiceId: 1, ServerId: 1, ServerName: "testsv1", OS: "Windows Server 2022 Core", CPU: "4", RAM: "16" },
  { ServiceId: 1, ServerId: 2, ServerName: "testsv2", OS: "Windows Server 2022 Minimal", CPU: "8", RAM: "32" });

ClearCollect(
  colDisks,
  { ServiceId: 1, ServerId: 1, DiskId: 1, MP: "C", Size: "50" },
  { ServiceId: 1, ServerId: 1, DiskId: 2, MP: "D", Size: "80" },
  { ServiceId: 1, ServerId: 2, DiskId: 1, MP: "C", Size: "90" },
  { ServiceId: 1, ServerId: 2, DiskId: 1, MP: "D", Size: "45" })