Handling parsing of DynamoDB query result

Question

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?

Why is the result attribute in the desired object an array? Will you also query for multiple organizations (thus necessitating a filter/reduction on PK)? — jarmod
– jarmod, Commented Mar 2, 2022 at 18:34
Yes, I would also like to be able to query multiple organizations and get them in the desired format. I will add this to the description — nicks6853
– nicks6853, Commented Mar 2, 2022 at 18:38
I've added an answer that, while not performance optimized, is at least simple to maintain and enhance. Hope it's helpful. No doubt there is a one-pass solution that's more lines of code and less FP. — jarmod
– jarmod, Commented Mar 3, 2022 at 13:40

jarmod · Accepted Answer · 2022-03-02 19:48:19Z

1

Here's an idea for how to solve this. Not sure if it substantially improves over what you already have tried. Embed the resulting result array as needed e.g. {result}.

This code uses repeated filters over the same data so, while easy to understand and modify, it's not optimal for a large dataset.

const records = [
  {
    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",
  },
  {
    PK: "ORG#APPLE",
    SK: "METADATA#APPLE",
    OrgName: "Apple",
    PlanType: "Enterprise",
  },
  {
    PK: "ORG#APPLE",
    SK: "USER#TIMCOOK",
    UserName: "Tim Cook",
    UserType: "Admin",
  },
];

const orgs = records.filter((rec) => rec.OrgName);
const users = records.filter((rec) => rec.UserName);

const result = orgs.map((org) => ({
  OrgName: org.OrgName,
  PlanType: org.PlanType,
  Users: users
    .filter((user) => user.PK === org.PK)
    .map(({ UserName, UserType }) => ({ UserName, UserType })),
}));
console.log(result);

edited Mar 2, 2022 at 19:48

answered Mar 2, 2022 at 19:37

jarmod

79.8k18 gold badges132 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nicks6853 Over a year ago

This is pretty much what I was looking for. I think I was moving towards something like this, but I wasn't sure if it made sense. Knowing that someone else came to a similar solution at least reassures me that it makes sense. Thanks!

Collectives™ on Stack Overflow

Handling parsing of DynamoDB query result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related