Match non-partitionKey fields (in nested json) in both tables and retrieve data in DynamoDB table

Question

I have 2 tables, with 1 matching data in which I want to utilize that as a matching field and retrieve some data.

First table is this:

{
      "mainFieldName": {
        "S": "someString"
      },
      "fieldA": {
        "L": [
          {
            "M": {
              "AccountId": {
                "S": "12345"
              },
              "PrincipalId": {
                "S": "randomIdString"
              },
              "PrincipalType": {
                "S": "GROUP"
              }
            }
          },
          {
            "M": {
              "AccountId": {
                "S": "12345"
              },
              "PrincipalId": {
                "S": "secondRandomString"
              },
              "PrincipalType": {
                "S": "GROUP"
              }
            }
          }
        ]
      },
      "fieldC": {
        "L": [
          {
            "M": {
              "name": {
                "S": "xxx"
              },
              "final_json": {
                "S": "some json data"
              }
            }
          }
        ]
      }
    }

Second table:

{
  "userId": {
    "S": "randomString"
  },
  "group": {
    "L": [
      {
        "M": {
          "GroupId": {
            "S": "randomGroupId"
          }
        }
      }
    ]
  }
}

I want to find the matched field for first table's fieldA.PrincipalId and second table's group.GroupId, if match, returning data is first table's fieldC.final_json

My params i tried is this, it's executed successfully but no results returned. I have confirmed there should be some matched inputs.

response = table1.scan(
                TableName=TABLE_1,
                FilterExpression="#gid.#pid = :id",
                ExpressionAttributeValues={
                    ':id': {'S': groupId}
                },
                ExpressionAttributeNames={
                    '#gid': 'groupId',
                    '#pid': 'PrincipalId'
                }
                )

It always return empty results

Can you share an output of what you expect. You logic and description is really messed up here and its difficult to understand what it is you want to achieve. — Leeroy Hannigan, Dec 06 '22 at 14:16
thought i have shared all the info I have in the questions.. output expected is the stated "if match, returning data is first table's fieldC.final_json", but i got nothing returned instead. — unacorn, Dec 07 '22 at 05:04

score 0 · Accepted Answer · answered Dec 07 '22 at 05:16

I managed to find a resolution to this. To simplify, I changed to a flatter table structure by pre-processing the Json to appending to a list.

My first table becomes:

{
  "id": {
    "S": "randomString"
  },
  "fieldA": {
    "S": "randomString"
  },
  "table_1_groupId": {
    "L": [
      {
        "S": "randomGroupIdString"
      }
    ]
  },
  "fieldB": {
    "S": "asdfsafd"
  },
  "fieldC": {
    "L": [
      {
        "M": {
          "name": {
            "S": "randomString"
          },
          "jsonData": {
            "S": "randomJsonData"
          },
          "type": {
            "S": "type_a"
          }
        }
      }
    ]
  }
}

Second table stays the same.

With that i am able to use DynamoDB query which is more efficient as well, with FilterExpressions

My query is:

response = table1.query(
                TableName=TABLE_1,
                KeyConditionExpression="id = :id",
                FilterExpression="contains(groupId, :gid)",
                ExpressionAttributeValues={
                    ':id': defaultId,
                    ':gid': groupId
                },
)

My output returns list of all data (since I haven't added any filter to the output) once they have the field 'GroupId' in Table2 match with table_1_groupId in table 1

Match non-partitionKey fields (in nested json) in both tables and retrieve data in DynamoDB table

1 Answers1