AWS DynamoDB tables and GSI

Question

I'm trying to understand how DynamoDB tables work with GSI and I'm very confused by their documentation.  
  
From this , a music library table looks like this in a JSON-like format (if I understand it correctly):

```
// Music Library Table
[
    {
        "song_id": "song-129", // Partition Key
        "details": { /** details is a Sort Key */
            "title": "Wild Love",
            "artist": "Argyboots",
            "downloads": 15000,
            // etc.
        },
        "month-2018-01": { /** Also a Sort Key? */
            "month": "2018-01", /** GSI Primary Key */
            "month_total": 1000 /** GSI Secondary Key */
        },
        "download_id_1": { /** Also a Sort Key? */
            "time": "timestamp"
        },
        "download_id_2": { /** Also a Sort Key? */
            "time": "timestamp"
        },
        "download_id_3": { /** Also a Sort Key? */
            "time": "timestamp"
        },
    }
]
```

It seems like there are several combinations of Primary Keys = (Partition Key + Details / Month / DownloadID). But they wrote  
  
_and Sort-Key=DownloadID_  
  
Also from this , an HR table looks like this:

```
// HR Table
[
    {
        "employee_id": "hr-974", /** Partition Key */
        "employee_name": { /** Also a Sort Key? */
            "name": "Murphy, John",
            "start_date": "2008-11-08",
            // etc.
        },
        "YYY-Q1": { /** Also a Sort Key? */
            "order_total": "$5,000",
            "name": "Murphy, John"
        },
        // ...
        "v0_job_title": { /** Also a Sort Key? */
            "job_title": "operator-1",
            "start_date": "2008-11-08",
            // etc.
        },
        "v1_job_title": { /** Also a Sort Key? */
            "job_title": "operator-2",
            "start_date": "2008-11-10",
            // etc.
        }
    }
]
```

But it seems like it doesn't because:  
  
_Use the global secondary index to find all employees working in a particular warehouse by searching on a warehouse ID (such as Warehouse_01)._  
  
It seems like warehouses have their own entries with their own IDs.  
  
So how should the tables look like in JSON format?

thathelpfulhuman · Accepted Answer

DynamoDB tables are very confusing at first, coming from a relational database, thinking of it as JSON or even how you would lay out data in a spreadsheet.  
  
Let's focus on the HR data, your second question.  
  
First you would look up in the table with sork key=Warehouse_01  
  
The data that would come back is   
{ PK: "HR-974", SK: "Warehouse_01", "Attr1": "Murphy, John" }  
  
Using that PK you could now make a second get item call where PK=HR-974 and get back all of that data (Employee information, Q1 order totals, Warehouse id, Job title history).  
  
If you only wanted the employee data, you could look up for PK=HR-974 and SK=Employee_Name  
  
It is not in the example... but there is probably an entry for the warehouse as well.  
The PK=Warehouse_01 and there could be SK with any of the following: Location, Inventory type, Warehouse type, etc and the Data-Item attributes   
  
What would the JSON look like is tricky to answer, because  
1) It depend on which slice of data through the table you grab  
2) Is it the JSON from DynamoDB or application specific JSON  
  
In your application, you have to keep track of the fact that when SK is EmployeeName then Attr1 means EmployeeName, but if SK is  then Attr1 means OrderTotals.  
  
Raw JSON  
{  
PK: HR-974, SK: "Employee_Name", Attr1: "Murphy, John", ...  
}  
  
Application level JSON  
{  
EmployeeID: HR-974, Type: "Employee_Name", EmployeeName: "Murphy, John", ...  
}  
  
If this is helpful, subscribe to my   .

AWS DynamoDB tables and GSI

Relevant content