Amplify-cli: Filtering on weeknumbers greater then 10 doesn't return results

[sprucify]

I'm having a table of around 4000 recipes.
Every recipe has a weeknumber.

When I run:

this.apiService.ListRecipes({ weeknumber: { eq: moment().week() } }, 4000)

I get zero results sinds it is currently week 20

It only works when entering a static number between 1 and 10.
My table Recipes has also records with weeknumbers between 11 and 52

Another unexpected behavior is, that it is performing the limit: 4000 before filtering the weeknumbers.

I was expecting it would first ask the database for all entries with weeknumber X and then limit the results.

[sammartinez]

@sprucify This doesn't seem to be a specific question in regards to Amplify. Can you provide a little more information on how you are using Amplify?

[sprucify]

I basically amplify push a schema containing my model:

type Recipe 
  @model
{
  id: ID!
  title: String
  key: String
  image: String
  numberOfPersons: Int
  courses: [String]
  time: String
  energy: String
  proteine: String
  carbohydrates: String
  fat: String
  saturatedFat: String
  fibers: String
  salt: String
  sugar: String
  recipeTypes: [String]
  specials: [String]
  containsProducts: [String]
  diet: [String]
  ingredients: [Ingredient]
  preperation: [String]
  editDate: String
  menuSuggestion: String
  weeknumber: Int
}

And use the amplify codegen generated functions.

I just discovered that weeknumbers greater then 10 are not coming through the eq: filter

[sprucify]

When I run manual queries in AppSync, I get only results for weeknumber 1 - 10
As soon as I query weeknumber 11 or higher I get zero results back.
Double checked inside dynamo table, and it contains records with weeknumber 11 - 52

[vparpoil]

Another unexpected behavior is, that it is performing the limit: 4000 before filtering the weeknumbers.

This is the standard behavior of dynamoDB scan:

A single Scan operation will read up to the maximum number of items set (if using the Limit parameter) or a maximum of 1 MB of data and then apply any filtering to the results using FilterExpression.

Also, default limit is set to 10 by amplify generated resolvers, so you should pass limit : 53 to your queries to be safe.

You need to use Query on indexes to avoid this behavior. In your use case, I believe you should index dynamoDB with the weeknumber key and then modify the resolver for the Query to use it (like here). I guess it should solve your issue.

[sprucify]

But why is this only returning items with weeknumbers between 1 and 10?

 listRecipes(limit:90000 ) {
    items {
        id
        weeknumber
      }   
  }

[sprucify]

Ok the dynamoDB scan function indeed first limits the results and apply the filtering on that subset, this is not a very helpful order in my opinion.

In a response, DynamoDB returns all the matching results within the scope of the Limit value. For example, if you issue a Query or a Scan request with a Limit value of 6 and without a filter expression, DynamoDB returns the first six items in the table that match the specified key conditions in the request (or just the first six items in the case of a Scan with no filter). If you also supply a FilterExpression value, DynamoDB will return the items in the first six that also match the filter requirements (the number of results returned will be less than or equal to 6).

You wouldn't believe the reason behind why I'm only getting back records for week 1 to 10.
My total table size is 5,56MB
Divide this bij 52 weeks and I'm on 0.1MB per week.
Times 10 and I'm on 1MB which is unbelievable the maximum data size for the scan operation!

This took me 11 hours of debugging to find the reason.

So how on earth are other companies getting API's build with all these limits?

[vparpoil]

If you need to download 5MB of data, you can use a recursive approach. Here is a draft of how it looks like:

let allRecipes = []
let search = {limit: 100000}
const recursiveGetRecipes = (currentResult) => {
    // if first iteration or if there is a next Token
    if (currentResult === null || currentResult.nextToken != null) {
        if (currentResult !== null && currentResult.nextToken != null) {
           // other iterations
            search.nextToken = currentResult.nextToken
        }
        API.graphql(graphqlOperation(listRecipes, search)).then((result) => {
            currentResult = result.data.listRecipes;
            allRecipes = allRecipes.concat(currentResult.items);
            this.recursiveGetRecipes(currentResult)
        }).catch((error) => {
            // error
        })
    } else {
        // done
    }
}
recursiveGetRecipes(null);

[sprucify]

Ideally I don't want the full 5MB, but only the records for the current weeknumber.
Can I somehow increase DynamoDB scan limit of 1MB?

[wizawuza]

I don't know enough about DynamoDB but can't you do a Query vs. a Scan to help achieve this, and then do a filter accordingly? The only problem is that you'd have to make sure you have an index on 'weeknumber' as a Query with filter requires that.

I believe that's why with a normal Scan you can do any sort of filters in GraphQL - it always scans everything and then filters from there, so no index is required..

In other words, you may have to write some resolvers.

[wizawuza]

I don't know enough about this directive, but maybe @key may be useful to you (#1463) when it is complete.

[mikeparisstuff]

@sprucify When developing applications with DynamoDB you need to design your access patterns into the DynamoDB table. We are close to releasing a new @key directive that will make modeling situations like this a lot easier.

In your case you want to "list recipes by weekNumber". DynamoDB is a distributed hash table that allows you to filter by two attributes, a partition key and a sort key. The partition key determines which physical hosts (partitions) will hold a particular object and the sort key specifies how items within that partition should be sorted. To implement a plain "list recipes by weekNumber" you need an index where the partition key is the weekNumber. To implement a more complex, "list recipes by weekNumber sorted by date" you would need the partition key to be the weekNumber and the sort key to be the date. On one hand DynamoDB forces you to think about your access patterns beforehand but in return you get a database that will scale effortlessly to any workload you can throw at it.

[sprucify]

@mikeparisstuff thanks for clearing this out. Still not a fan of the quirky limits in DynamoDB.
Maybe one day I will be appreciate it more when the app needs to scale fast.

Is a partition in any way related to the 1MB limit?

[sprucify]

I think this issue will be solved with the new @key feature right?
I'm trying to figure out how to do list recipes from week x with this new feature

[mikeparisstuff]

@sprucify Yes the @key directive should help enable your use case. The 1MB limit relates to the maximum amount of data that can returned by a given Query. If the query specifies a max items limit that returns more than 1MB of data, then the results will be truncated to 1MB and you will get back fewer items than the limit provided.

[clodal]

Hi @mikeparisstuff understand that the max limit per query is 1MB.

However, I am facing this issue in my application when I queried with limit of 8, I got 2 records; If 2 exceeded the limits of 1mb and that’s why it got cut off, why is it that when I queried again with limit of 16, I got 4 records instead?

Shouldn't I expect 2 records to return, since 2 records would already exceed the 1MB mark?