Create index on nested array value with dynamodb

Question

I have the following data stored in a DynamoDB table called elo-history.

{
  "gameId": "chess",
  "guildId": "abc123",
  "id": "c3c640e2d8b76b034605d8835a03bef8",
  "recordedAt": 1621095861673,
  "results": [
    {
      "oldEloRating": null,
      "newEloRating": 2010,
      "place": 1,
      "playerIds": [
        "abc1"
      ]
    },
    {
      "oldEloRating": null,
      "newEloRating": 1990,
      "place": 2,
      "playerIds": [
        "abc2"
      ]
    }
  ],
  "versus": "1v1"
}

I have 2 indexes, guildId-recordedAt-index and gameId-recordedAt-index. Theses allow me to query on those fields.

I am trying to add another index for results[].playerIds[]. I want to be able to do a query for records with playerId=abc1 and have those sorted just like guildId and gameId. Does DynamoDB support something like? Do I need to restructure the data or save it in two different formats to support this type of query?

Something like this. New table called player-elo-history in addition to the elo-history table. This would store the list of games by playerId

{
  "id": "abc1",
  "gameId": "chess",
  "guildId": "abc123",
  "recordedAt": 1621095861673,
  "results": [
    [
      {
        "oldEloRating": null,
        "newEloRating": 2010,
        "place": 1,
        "playerIds": [
          "abc1"
        ]
      },
      {
        "oldEloRating": null,
        "newEloRating": 1990,
        "place": 2,
        "playerIds": [
          "abc2"
        ]
      }
    ]
  ]
}

{
  "id": "abc2",
  "gameId": "chess",
  "guildId": "abc123",
  "recordedAt": 1621095861673,
  "results": [
    [
      {
        "oldEloRating": null,
        "newEloRating": 2010,
        "place": 1,
        "playerIds": [
          "abc1"
        ]
      },
      {
        "oldEloRating": null,
        "newEloRating": 1990,
        "place": 2,
        "playerIds": [
          "abc2"
        ]
      }
    ]
  ]
}

Answer 1

It looks like you're modeling the one-to-many relationship between Games and Results using a complex attribute (e.g. a list or objects) on the Game item. This is a completely valid approach to modeling one-to-many relationships and is best used when 1) the results data doesn't change (or change often) and 2) you don't have any access patterns around Results.

Since it sounds like you do have access patterns around Results, you'd be better off storing your Results in their own items.

For example, you might consider modeling results in the user partition with a PK=USER#user_id SK=RESULT#game_id. This would allow you to fetch results by User ID (QUERY where PK=USER#user_id SK begins_with RESULT). Alternatively, you could model results with a PK=RESULT#game_id SK=USER#user_id and create a GSI that swaps the PK/SK's which will allow you to group results by User.

I don't know the specifics around your access patterns, but can say that you'll need to move results into their own items if you want to support access patterns around game results.