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