How to get data from 2 tables in single query from dynamodb

Question

I have 2 tables with a one-to-many relationship and want to fetch data with single query.

Table structure: Employee

• PK : #Emp-PK
• SK : #Emp-SK
• Name : "Test"

Table structure : Employee-projects

• PK: #Emp-PRO-PK
• SK: #Emp-PRO-SK
• EmpPKKey: #Emp-PK
• EmpSKKey: #Emp-SK
• project-title: "abc"

How do I get data in a manner like

Items {
    PK : #Emp-PK
    SK : #Emp-SK,
    name : "Test",
    projects : [{
                    PK : #Emp-PRO-PK
                    SK : #Emp-PRO-SK,
                    project-title : "abc"
                },{
                    PK : #Emp-PRO-PK
                    SK : #Emp-PRO-SK,
                    project-title : "def"
                },{
                    PK : #Emp-PRO-PK
                    SK : #Emp-PRO-SK,
                    project-title : "ghi"
                }]
          }

But I am expecting this in a single query or method. Is it possible?

Answer 1

You are trying to model data like it would be in a relational database, and now you're wondering why it doesn't work as you'd expect.

Items or data that are related should be stored together to allow you to read them together. I would advise that you combine both tables to enable more efficient querying capabilities.

Answer 2

In the DynamoDB paradigm, the design of the tables is driven by the structure of future queries, not by the delineation of the entities you are modelling. The point of this paradigm is to make a narrowly defined set of queries incredibly efficient, at the expense of the lost flexibility to design ad-hoc queries. (This is obviously a terrible oversimplification, but there you have it.)

If the current table structure does not accommodate the query you intend to make, you can still create a new table and model it on the schema of the result you expect. This particular query will become super-efficient. But you must then consider all other queries.

If this query you describe is simply a rare ad-hoc query, you would have to write some application code to run queries (plural) and combine the data.