Suppose that I am working on an application implementing single-table design in DynamoDB. The table holds organisations, and users resulting in something like this:

Using the AWS SDK, I am able to issue a QueryCommand against my table and retrieve all information and related records to my organisation (the red box in the image above) by running something like this:

const params = {
  TableName: 'dynamo-table-name',
  KeyConditionExpression: '#PK = :PK',
  ExpressionAttributeNames: {
    '#PK': 'PK',
  },
  ExpressionAttributeValues: {
    ':PK': 'ORG#MICROSOFT',
  },
};

const result = await client.send(new QueryCommand(params));

Since I am using @aws-sdk/lib-dynamodb the result comes back as an array of JS objects which is good, but I am running into the following problem. Once I have that data, I would like to do the following:

Convert this:

[
  {
    "PK": "ORG#MICROSOFT",
    "SK": "METADATA#MICROSOFT",
    "OrgName": "Microsoft",
    "PlanType": "Enterprise",
  },
  {
    "PK": "ORG#MICROSOFT",
    "SK": "USER#BILLGATES",
    "UserName": "Bill Gates",
    "UserType": "Member"
  },
  {
    "PK": "ORG#MICROSOFT",
    "SK": "USER#SATYANADELLA",
    "UserName": "Satya Nadella",
    "UserType": "Admin"
  }
]

To something like this:

{
    "result": [
        {
            "OrgName": "Microsoft",
            "PlanType": "Enterprise",
            "Users": [
                {
                    "UserName": "Bill Gates",
                    "UserType": "Member"
                },
                {
                    "UserName": "Satya Nadella",
                    "UserType": "Admin"
                }
                
            ]
        }
    ]
}

I have struggled with finding an elegant solution. My attempts up to this point made use of JavaScript's reduce function but they never end up feeling robust enough to be something I would really consider using. Most of my problems come from the fact that I would like it to also work if I was to not only have Users for an Organisation, but also something else like maybe Locations. I am also looking for something that, in the case where I search for all organizations, it could get them in the desired format. Does anyone have any suggestions on how I could do this?