CODEWITHSUNDEEP
amazon-dynamodb
Mandeep Singh
Mandeep Singh

Reputation: 8224

DynamoDB: How to fetch single item where attribute value is not in a given list of values?

I understand this query might be inefficient since it can involve a full table scan in worst case, but I need to fetch only a single item at a time.

For example, I have a table containing values like this:

{
  id: 'bc63a25e-b92b-483e-9ad3-ad6d474dfae2',
  domain: 'xyz.com',
  template_url: `https://s3.us-east-2.amazonaws.com/bucket/some-random-url.html`,
  data_elements: {
    message_link: 'http://www.google.com'
    zodiac_sign: 'Scorpio'
  }
}

I have a GSI with domain as the hash key. Now I want to fetch items from this table: 

WHERE domain == 'xyz.com' 
AND id not in <a list of ids>
LIMIT 1;

How can I achieve this type of query ? I checked the documentation and I could see there is IN operator but could not find any NOT IN operator.

Upvotes: 0

Views: 2028

Answers (3)

Ivan Mushketyk
Ivan Mushketyk

Reputation: 8285

You can run SQL queries on DynamoDB if you use EMR Hive or Redshift. In this case you can use any SQL operators to query your data.

Of course this is not intended for interactive queries and intended only for some analytics queries that are executed infrequently.

Here is how to use DynamoDB with Redshift.

Here is how to use DynamoDB with EMR Hive.

Upvotes: 0

notionquest
notionquest

Reputation: 39176

I am not sure why you have mentioned about scan as you have hashkey of the GSI. You can use the Query API with the below params.

var idArray = ["1", "2"];

var params = {
    TableName : "tablename",
    IndexName : 'your_index_name',
    KeyConditionExpression : 'domain = :domainVal', 
    FilterExpression : "NOT #id IN (:idValue)",
    ExpressionAttributeNames: { "#id": "id" },
    ExpressionAttributeValues : {
        ":domainVal" : 'xyz.com',
        ":idValue" : idArray
    }
};

I have tested the NOT IN on my table. It works fine for me.

Upvotes: 1

P&#230;tur Magnussen
P&#230;tur Magnussen

Reputation: 921

I had the same issue, and I don't think you can. You will need to use the key for the 'get' method and the 'scan' method. The only alternative (I think) would be to fetch all items and then to do a string comparison on each and everyone. I don't think I need to mention how incredibly expensive that would be.

As mentioned, I had to deal with the same issue and I ended up changing my data structure. It was a bit cumbersome to begin with and I have twice the data entries of a relational db but it is negligible and the query is incredibly fast even on the micro AWS instance.

You can't always do the same operations on a NoSQL db that you can do on a MySQL db and this is a prime example of that.

Upvotes: 1

Related Questions