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

Answer 1

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