amazon-dynamodbamazon-dynamodb-index

How does one create a GSI for an Item in a Array in DyanmoDB?


I have a DyanmmoDB table that has:

Partition key: State (IE: two letter State ID) Sort Key: City (Name of city in the state)

Items in the "record" is an array, let's say Advertisements

"StateShort": "AK",
 "City": "Anchorage",
 "Ads": [
  {
   "AdDateAdded": 1674671363999,
   "AdDateExpire": 1682447536551,
   "AdIDKey": "ABC-123-GUID-Here",
   "AdTitle": "This is the Title to Ad 1"
   "AdDescription": "Description of the Details to Ad 1",
   "AdOwner": "bob@example.com",
   },
{
   "AdDateAdded": 1674671363999,
   "AdDateExpire": 1682447536551,
   "AdIDKey": "DEF-456-GUID-Here",
   "AdTitle": "This is the Title to Ad 2"
   "AdDescription": "Description of the Details to Ad 2",
   "AdOwner": "bob@example.com",
   }
]

Query to retrieve all ads in State and City, easy-peasy, as they are PK and SK.

but, I do NOT want to Scan to find all the ads that AdOwner has ("bob@example.com"). They may have Ads in other states and city requiring me to Scan entire table.

FEELS like a perfect use case for a Global secondary indexes.

I've added AdOwner as a GSI but, clearly it can't find the key in the array.

Question: Is this solvable with a GSI? If so, what structure would that look like?

After creating the GSI, I've tried this code but, it returns no items

 const params = {
        "TableName": "My_table",
        "IndexName": "AdEmail-index",
        "KeyConditionExpression": "#IndexName = :AccountID",
        "ExpressionAttributeNames": {
            "#IndexName": "AdOwner",
        },
        ExpressionAttributeValues: {
            ":AccountID": "bob@example",
        },
        "ScanIndexForward": false
    }
    
    try{        
        const item = await dynamo.query(params).promise()
        console.log("what: ", item)
    }
    catch (e) {
        console.log("ERROR", e)
    }
        

Solution

  • No, a global secondary index key must be a top level attribute and be of type string, number or binary.

    You should vertically shard your items, giving you more flexibility:

    pk sk data AdOwner
    AK Anchorage#BC-123-GUID-Here {} bob@example.com
    AK Anchorage#BDEF-456-GUID-Here {} bob@example.com

    All ads in a state and city, still easy, using Query:

    SELECT * FROM MY TABLE WHERE pk = 'AK' AND sk BEGINS_WITH 'Anchorage'

    You can now create a GSI on the AdOwner to fulfill your second access pattern.

    SELECT * FROM MY TABLE.INDEX WHERE AdOwnder = 'bob@example.com'