DynamoDB: Query items where a field has specific type

I have a legacy table in DynamoDB, and this table contains a non-key field term which is usually a Number, but in some cases it is a String containing a number

I want to query all items having this field a String value, and it seems to be a pretty complex task

I tried NOT Contains (term, {S: "a"}) and it returns all items with Numbers and Strings

The only solution which works is to use

begins_with(term, {S: "0"} OR
begins_with(term, {S: "1"} OR
...
begins_with(term, {S: "9"}

I suppose that this is not the best solution

Maybe someone knows cleaner solution?

You should use the filter function attribute_type:

attribute_type(term, "S"}

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Functions

How big is the table? What's your motivation for finding the string values?

As Lee said you could use the filter function, but be aware that behind the scenes you're performing a full scan of the table, not a query. So depending on the size of your table this may be undesirable. Unfortunately, I don't see any way around performing a scan given the parameters of your problem.

If this is a one time job on a large table, I'd opt to set up a Lambda or ECS task to slowly scan through the table and process the items in batches. Progress can be kept using a LastEvaluatedKey. Upside of this is that you can update the strings to be numbers while you do this (which I suspect may be your motiviation)

https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Scan.html

https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_BatchWriteItem.html