OR-query on multiple attributes in Amazon DynamoDB

Question

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:

find all transports where a contact was either shipper, carrier or consignee sorted by createDt. E.g. entering contact1 should return records 1, 2.

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.

Maurice · Accepted Answer · 2021-02-17 10:01:53Z

2

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.

edited Feb 17, 2021 at 10:01

answered Feb 17, 2021 at 9:54

Maurice

13.3k2 gold badges31 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sihaya Over a year ago

Great answer. I expect some users to have contacts that are in over 50k transports. Is that reasonable to do on the client side?

Maurice Over a year ago

Hm, it shouldn't be a problem for any reasonably sized system, but you'd have to always load all contacts, which is going to be expensive. Going for GSK1SK in the form of DATE#<ISO8601-timestamp in UTC>#TRANSP#<id>#SHIP is also possible, that should allow you to do some between queries and things like n oldest/newest entries.

Collectives™ on Stack Overflow

OR-query on multiple attributes in Amazon DynamoDB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related