CODEWITHSUNDEEP
amazon-dynamodb
sihaya
sihaya

Reputation: 1427

OR-query on multiple attributes in Amazon DynamoDB

I have a table like this:

Transports

id (PK) createDt shipperId carrierId consigneeId
1 23 contact3 contact2 contact1
2 24 contact1 contact2 contact3
3 28 contact3 contact2 contact4

My access pattern is:

How can I do this in DyanomoDB?

I thought about creating a GSI. But then I need to create a separate GSI for each column, which would mean I need to join the query results on the columns myself. Perhaps there is an easier way.

Upvotes: 1

Views: 184

Answers (1)

Maurice
Maurice

Reputation: 13197

I'd create a GSI on the table and split your single record up into multiple ones. That would make writes slightly more complex, because you write multiple entities, but I'd do something like this:

PK SK type GSI1PK GSI1SK other attributes
TRANSP#1 TRANSP#1 transport createDt, (shipperId, carrierId, consigneeId)...
TRANSP#1 CONTACT#SHIP shipper-contact CONTACT#contact3 TRANSP#1#SHIP ...
TRANSP#1 CONTACT#CARR carrier-contact CONTACT#contact2 TRANSP#1#CARR ...
TRANSP#1 CONTACT#CONS consignee-contact CONTACT#contact1 TRANSP#1#CONS ...
  • To get all information about a given Transport ID you do a query with PK=TRANSP#<id>
  • To get just the basic information about a given Transport, you can do a GetItem on PK=TRANSP#<id> and SK=TRANSP<id> (You could also duplicate the contact infos here if they're fairly static.)
  • To get all transports a contact is involved in, you do a PK=CONTACT#<id> and SK starts with TRANSP on GSI1

If you really need server-side sorting, you might choose a different GSI1SK, maybe prefix it with the dt value, but I'd probably just do that client side.

Upvotes: 2

Related Questions