Empty string in DynamoDB query returns no results

💬 Queries and Resources
1

I was hoping to get some advice. I have a query that scans a DynamoDB table with a filter. The attribute and value used in the filter are populated based on values selected in a dropdown. For any value other than an empty string it works but with an empty string it returns zero results (it should return 5 records). I can preview what the filter built from the dropdown values will evaluate to and if I hardcode that in the JSON parameter editor it returns the correct number of results.

Parameterized expression that doesn't work:

{"TableName": "example",
 "ExpressionAttributeNames": { 
     "#attrName" : "{{LookupField.value}}" 
 },
 "ExpressionAttributeValues": { 
     ":attrValue" : "{{LookupValue.value}}"
 },
 "FilterExpression": "#attrName=:attrValue"
}

Hardcoded expression that works:

{"TableName": "example",
 "ExpressionAttributeNames": { 
     "#attrName" : "example_attribute" 
 },
 "ExpressionAttributeValues": { 
     ":attrValue" : ""
 },
 "FilterExpression": "#attrName=:attrValue"
}
2

Hey @laura! This is definitely a common Retool use case. Can you try removing the string quotes from around "{{ Lookupvalue.value }}"? Retool will automatically cast the empty string that your Dropdown evaluates to into a string.

3

@justin thank you for helping me with this! I tried removing the quotes. It now shows that the query resolves to what you see below (null instead of “”) but it still doesn’t return any of the rows where that attribute is set as empty or missing in Dynamo

  {"TableName": "example",
   "ExpressionAttributeNames": { 
       "#attrName" : "example_attribute" 
   },
   "ExpressionAttributeValues": { 
       ":attrValue" : null
  },
  "FilterExpression": "#attrName=:attrValue"
 }
4

I was able to figure out the problem and a workaround. I created a temporary state variable and stored the output of the dropdown there. I was able to see it was converting the empty string to a null and that must have been what was passed to DynamoDB. I added an interim step using a Run JS Code query that converts the null back to an empty string before running the query and now it is returning the expected results.