CODEWITHSUNDEEP
amazon-web-servicesaws-lambdaamazon-dynamodbdynamodb-queries
Surya
Surya

Reputation: 646

Querying with FilterExpression CONTAINS method

I want to query a specific row from a table based on a track's genre and release year.

Task: Query rows with release_year=2018 AND genre="pop"

Tracks Table:

trackId----------------release_year----------------genres----------------count

trackId: "7sT7kZEYd1MrmzLLIRVZas", release_year: 2018, genres: ["pop","rap", "hip-hop"], count: 7

Below code is how I am attempting to make this query as shown in FilterExpression. Currently I'm checking if the Table's attribute: genres (Array), contains :genre, which is a String sent from the client in the request. How would one set up a GSI in a way to make this query?

let queryParams = {
    TableName: tableName,
    IndexName: req.query.index,
    KeyConditionExpression: 'release_year = :release_year and genres = :genre',
    ExpressionAttributeValues: { ':release_year': parseInt(req.query.year), ':genre': req.query.genre},
    FilterExpression : "contains (genres, :genre)", 
    ScanIndexForward: false,
    Limit: 50
  }
  
  dynamodb.query(queryParams, (err, data) => {
    if (err) {
      res.statusCode = 500;
      res.json({error: 'Could not load items: ' + err});
    } else {
      res.json(data.Items);
    }
  });

Upvotes: 0

Views: 63

Answers (1)

Leeroy Hannigan
Leeroy Hannigan

Reputation: 19773

GSI should be created with release_year as the PK. Generes is part of the FilterExpression only.

let queryParams = {
TableName: tableName,
IndexName: req.query.index,
KeyConditionExpression: 'release_year = :release_year',
ExpressionAttributeValues: { ':release_year': parseInt(req.query.year), ':genre': req.query.genre},
FilterExpression : "contains (genres, :genre)", 
ScanIndexForward: false,
Limit: 50
}


  dynamodb.query(queryParams, (err, data) => {
    if (err) {
      res.statusCode = 500;
      res.json({error: 'Could not load items: ' + err});
    } else {
      res.json(data.Items);
    }
  });

Upvotes: 1

Related Questions