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

Question

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)
    }

score 1 · Accepted Answer · answered Feb 08 '23 at 15:18

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'

I am picking up, what you're laying down! I'll get that a try. I have to rethink my approach with this solution. Thanks! — wilmsoft, Feb 08 '23 at 17:18

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

1 Answers1