CODEWITHSUNDEEP
node.jsamazon-web-servicesaws-lambdaamazon-dynamodbdynamodb-queries
Nasreen Ustad
Nasreen Ustad

Reputation: 1702

How to write DynamoDB query filter using AND & OR?

I have a User table, and I want to fetch data using the following query, for education purposes, I'm using a SQL-like query.

SELECT * FROM User
WHERE (gender = "Male")
AND (age between 25-30 OR height between 5.4-5.9 OR city="India, US")

I'm trying to create the above query in AWS Lambda using Node.js

Any feedback will be appreciated.

Upvotes: 4

Views: 8951

Answers (1)

jarmod
jarmod

Reputation: 78573

Here is how you would do it with the awscli:

aws dynamodb scan \
    --table-name User \
    --filter-expression "(gender = :gender) AND ((age BETWEEN :age1 AND :age2) OR (height BETWEEN :h1 AND :h2) OR (city = :city))" \
    --expression-attribute-values '{":gender":{"S":"Male"}, ":age1":{"N":"25"}, ":age2":{"N":"30"}, ":h1":{"N":"5.4"}, ":h2":{"N":"5.9"}, ":city":{"S":"India, US"}}'

Here's how you would do it with the low-level AWS JavaScript SDK functions:

const AWS = require("aws-sdk");
AWS.config.update({region: 'us-east-1'});
const ddb = new AWS.DynamoDB();

const params = {
  TableName: 'User',
  FilterExpression: '(gender = :gender) AND ((age BETWEEN :age1 AND :age2) OR (height BETWEEN :h1 AND :h2) OR (city = :city))',
  ExpressionAttributeValues: {
    ':gender': {S: 'Male'},
    ':age1': {N: '25'},
    ':age2': {N: '30'},
    ':h1': {N: '5.4'},
    ':h2': {N: '5.9'},
    ':city' : {S: 'India, US'},
  },
};

ddb.scan(params, (err, data) => {
  if (err) {
    console.log('Error:', err);
  } else {
    for (const item of data.Items) {
      console.log(item);
    };
  }
});

Finally, here's how you would do it with the higher-level DynamoDB DocumentClient which makes attribute mapping much simpler:

const AWS = require("aws-sdk");
AWS.config.update({region: 'us-east-1'});
const dc = new AWS.DynamoDB.DocumentClient();

const params = {
  TableName: 'User',
  FilterExpression: '(gender = :gender) AND ((age BETWEEN :age1 AND :age2) OR (height BETWEEN :h1 AND :h2) OR (city = :city))',
  ExpressionAttributeValues: {
    ':gender': 'Male',
    ':age1': 25,
    ':age2': 30,
    ':h1': 5.4,
    ':h2': 5.9,
    ':city': 'India, US',
  }
};

dc.scan(params, (err, data) => {
  if (err) {
    console.log('Error:', err);
  } else {
    for (const item of data.Items) {
      console.log(item);
    };
  }
});

Note that these are table scans and consequently all items in the table are visited.

Upvotes: 13

Related Questions