Scan dynamodb table based on filter which is not present in all object

February 9, 2022

So I’m trying to filter my dynamoDb data based on role as you can see role attribute is present in some of the objects not in all


[
  {
    id: "7",
    email: 'test1@gmail.com',
    name: 'test1',
    age: '12',
  },
  {
    id: "8",
    email: 'test2@gmail.com',
    name: 'test2',
    age: '12',
  },
  {
    email: 'test3@gmail.com',
    name: 'test3',
    age: '12',
    test: 'test',
    role: 'ADMIN'
  },
  {
    email: 'test4@gmail.com',
    name: 'test4',
    age: '12',
    test: 'test',
    role: 'ADMIN'
  }
]

if I try to scan with email it works but if I try to scan with role its gives me error:
Invalid FilterExpression: Attribute name is a reserved keyword; reserved keyword: role

I tried with code

let params = {
  TableName: 'tableName',
  FilterExpression: 'role =:role',
  ExpressionAttributeValues: { ':role': 'ADMIN' },
  expressionAttributeNames: { '#role': 'role' }
}
let result = await this.docClient.scan(params).promise();

and this

let params = {
  TableName: 'tableName',
  FilterExpression: 'attribute_not_exists(role)',
  ExpressionAttributeValues: { ':role': 'ADMIN' },
  expressionAttributeNames: { '#role': 'role' }
}
let result = await this.docClient.scan(params).promise();

both giving me same error

>Solution :

I believe you had a typo in your first code snippet. FilterExpression: 'role =:role', should be FilterExpression: '#role =:role',.

So the following should work,

let params = {
  TableName: 'tableName',
  FilterExpression: '#role =:role',
  ExpressionAttributeValues: { ':role': 'ADMIN' },
  ExpressionAttributeNames: { '#role': 'role' }
}
let result = await this.docClient.scan(params).promise();