DynamoDB data structure / architecture to support set of particular queries

user_7390391 0
Viewed 39

I currently have a lambda function pushing property data into a DynamoDB with streams enabled. When a new item is added, the stream triggers another Lambda function which should query against a second DynamoDB table to see if there is a 'user query' in the table matching the new object in the stream. The items in the first table which are pushed into the stream look like this...

{
    Item: {
      partitionKey: 'myTableId',
      bedrooms: 3,
      latitude: 52.4,
      longitude: -2.6,
      price: 200000,
      toRent: false,
    },
  }

The second table contains active user queries. For example one user is looking for a house within a 30 mile radius of his location, between £150000 and £300000.

An example of this query object in the second table looks like this...

 {
        Item: {
          partitionKey: 'myTableId',
          bedrooms: 3,
          minPrice: 150000,
          maxPrice: 300000,
          minLatitude: 52.3,
          maxLatitude: 52.5
          minLongitude: -2.5,
          maxLongitude: -2.7,
          toRent: false,
          userId: 'userId',
        },
      }

When a new property enters the stream, I want to trigger a lambda which queries against the second table. I want to write something along the lines of...
get me all user queries where bedrooms == streamItem.bedrooms AND minPrice < streamItem.price AND maxPrice > streamItem.price AND minLatitude < streamItem.latitude AND maxLatitude > streamItem.latitude. Ideally I want to achieve this via queries and filters, without scanning.
I'm happy to completely restructure the tables to suit the above requirements. Been reading and reading and haven't found a suitable answer, so hoping an expert can point me in the right direction! Thank you in advance

dynamodb-queries amazon-dynamodb
1 Answers

There's no silver bullet with DynamoDB here. Your only tools are the PK/SK lookup by value and range, filters to brute force things after that, and GSIs to give an alternate point of view. You're going to have to get creative. The details depend on your circumstances.

Like if you know you're getting all those specific values every time, you can construct a PK that is bed##rent# and an SK of price. Then for those three attributes you can do exact index-based resolution and filter for the geo attributes.

If you wanted, you could quantize the price range values (store pre-determined price ranges as singular values) and put that into the PK as well. Like divide prices into 50k chunks, each of which gets a name of the leading value. If someone wanted 150,000 to 250,000 then you'd lookup using two PKs, the "150" and "200" blocks.

You get PK/SK + GSI + filter. That's it. So it's up to you to invent a solution using them, aiming for efficiency.

user_5386970
Related