CODEWITHSUNDEEP
node.jsamazon-web-servicesamazon-dynamodbserverless-framework
Joseph Astrahan
Joseph Astrahan

Reputation: 9082

How do I query DynamoDB with non primary key field?

I have the following data in my dynamoDB table.

enter image description here

Here is my code:

const userStatusParams = {
        TableName: process.env.USERSTATUS_TABLE,
        KeyConditionExpression: "loggedIn = :loggedIn",
        ExpressionAttributeValues: {
          ":loggedIn": true
        }
      };
      var usersResult;
      try {
        usersResult = await dynamoDbLib.call("query", userStatusParams);
        console.log(usersResult);
      }catch (e) {
        console.log("Error occurred querying for users belong to group.");
        console.log(e);
      }

Amazon returns back this error:

{ ValidationException: Query condition missed key schema element: userId
    at Request.extractError ...

How do I have it return all records where loggedIn == true?

My database is currently structured like this via my serverless.yml config.

phoneNumberTable: #This table is used to track phone numbers used in the system.
      Type: AWS::DynamoDB::Table
      Properties:
        TableName: ${self:custom.phoneNumberTable}
        AttributeDefinitions: #UserID in this case will be created once and constantly updated as it changes with status regarding the user.
          - AttributeName: phoneNumber
            AttributeType: S
        KeySchema:
          - AttributeName: phoneNumber
            KeyType: HASH
        ProvisionedThroughput:
            ReadCapacityUnits: ${self:custom.dynamoDbCapacityUnits.${self:custom.pstage}}
            WriteCapacityUnits: ${self:custom.dynamoDbCapacityUnits.${self:custom.pstage}}

I did a little research into this via other answers but could not figure out for my situation. In other answers they had sort keys but I do not use a sort key here.

Upvotes: 20

Views: 34876

Answers (3)

Eyal Ch
Eyal Ch

Reputation: 10066

In order to query a DynamoDB table, you can only query attributes that are part of the Primary Key or part of an index.

The Primary Key can be either:

  1. Partition/Hash Key, or
  2. Partition/Hash Key and a Sort/Range Key

In addition to the Primary Key, you can also create two types of index:

  1. A Local Secondary Index, where the same Partition Key is used but with a different Sort key, or
  2. A Global Secondary Index, where both the Partition Key and Sort Keys are different.

In order to query for loggedIn records, you need to either include this attribute in your Primary Key, or add a Local or Global Secondary Index including the loggedIn attribute.

Upvotes: 4

Prakash Sharma
Prakash Sharma

Reputation: 16482

If you are doing query then you have to pass the primary key which in your case is userId. If you do not have primaryKey and if you want all the logged in = true fields then you can do scan with filterExpression like this

const userStatusParams = {
        TableName: process.env.USERSTATUS_TABLE,
        FilterExpression: 'loggedIn = :loggedIn',
        ExpressionAttributeValues: {
          ":loggedIn": true
        }
      };
      var usersResult;
      try {
        // Do scan
        usersResult = await dynamoDbLib.call("scan", userStatusParams);
        console.log(usersResult);
      }catch (e) {
        console.log("Error occurred querying for users belong to group.");
        console.log(e);
      }

Update: Since the scan operation is less efficient, the other way to solve this problem is to create a GSI, with primary key loggedIn. But the problem here is that you cannot make any field primary key which have boolean data type.. It has to be number, string, binary. So to create a gsi you need to store accepted data type in loggedIn field instead of boolean.

Though I am not sure how much performance impact it will have for a table of thousand records but the good thing about gsi is that you can create them later even on the existing table if in future you find out some performance impact. Also, the number of gsi you can create on table is limited to 5. So utilise gsi wisely.

Upvotes: 19

Dmitry Grinko
Dmitry Grinko

Reputation: 15212

A Scan operation always scans the entire table or secondary index, then filters out values to provide the desired result, essentially adding the extra step of removing data from the result set. Avoid using a Scan operation on a large table or index with a filter that removes many results, if possible. Read more

You should use global secondary index!

AWS Console > DynamoDb > tab Indexes of your table > Create index > 

primary key - loggedIn
secondary key - userId
projected attributes - all

We should add secondary key to have unique pair. Don't use index name (loggedIn) so as loggedIn should be unique.

Than you can use a Query method with primary key (loggedIn)

Upvotes: 4

Related Questions