How to make a geo distance query only return results that I have not yet "Liked", Elastic Search, Dynamo DB

Question

I am trying to query my database to compile a list of posts near a location, that a given user has/hasnot liked.

allPostsNearLocationUserHasLiked(
    userId: ID,
    location: LocationInput,
    radius: Int
): [Post]
allPostsNearLocationUserHasNotLiked(
    userId: ID,
    location: LocationInput,
    radius: Int
): [Post]

To do this I am currently using AWSAppSync, with dynamoDB streaming data to Elastic Search. This allows me to easily do geospatial searches and obtain all the posts near a given location.

I am wondering what is the most efficient way is to compute this using dynamoDB? Or would it be better suited for me to switch over to a SQL database for my likes/users?

I have a Users, posts, and Likes DynamoDB table. I was thinking of using a pipeline resolver to:

1) Get the list of all the posts near a users location (Elastic Search)

2) Query the likes table, to get all the likes I have made (DynamoDB)

3) Combine the results item by item.

I have serious doubts about the performance of this especially step 3 which is an O(M*N) operation.

Is there any way to do this whole query natively in Elastic Search?

## DynamoDB Table?? Or maybe SQL?
type Like {
    likeId: ID!
    userId: ID!
    likedPostId: ID!
}

type Query {
    #Implement with Elastic Search
    allPostsNearLocation(location: LocationInput, radius: Int): [Post]

    ## Elastic search??? 
    allPostsNearLocationUserHasLiked(
        userId: ID,
        location: LocationInput,
        radius: Int
    ): [Post]
    allPostsNearLocationUserHasNotLiked(
        userId: ID,
        location: LocationInput,
        radius: Int
    ): [Post]
}

type Location {
    lat: Float!
    lon: Float!
}

input LocationInput {
    way: Float!
    lon: Float!
}

type Mutation {
    putPost(
        author: String!,
        title: String!,
        content: String!,
        location: LocationInput!,
        url: String!
    ): Post
    putUser(name:String): User

    likePost(userId: ID!, postId: ID!): Like
}

#DynamoDB Table
type User{
    userId: ID!
    name: String
    likes: [Like]
}

#DynamoDB table
type Post {
    id: ID!
    author: String!
    title: String!
    content: String!
    url: String!
    location: Location!
}

schema {
    query: Query
    mutation: Mutation
}

Answer 1

I'm not sure what is your performance requirements, but I think that your initial plan should be ok, if:

1) Get the list of all the posts near a users location (Elastic Search)

This should be fast, if the Elasticsearch index is set up with the right mapping, size, sharding and hardware depends on the data size.

2) Query the likes table, to get all the likes I have made (DynamoDB)

This can be fast, in case you could have an in memory cache of the 'likes', either fully in memory, or lazy/LRU cache.

3) Combine the results item by item.

if the result size is not too large (use pages of 10-100 items?), then getting the response from Elasticsearch, running on that stream and enriching/filtering it based on an in memory dictionary should be OK.

Good luck!