How do I query DynamoDB with non primary key field?

Question

I have the following data in my dynamoDB table.

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.

Solution 1

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.

Solution 2

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)

Solution 3

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.

Comments

[F_SO_K]

This answer is incorrect, DynamoDB does allow searching on non-indexed attributes. The search is called a scan. A search using an indexed attribute is called a query.

[Eyal Ch]

scan is not a search.

[Joseph Astrahan]

Here is the thing I am actually going to query on 4 values, logged in, group, priority and status and type. This table probably won't be more then a thousand records.

[Joseph Astrahan]

So in my case since this may grow to a thousand+ users, I noticed some other users suggesting using a secondary index. Is scan still better in this case?

[Prakash Sharma]

@JosephAstrahan No doubt scan is costly. Check updated answer. If you can store number/string to represent loggedIn field, then it is good to go :)

[Joseph Astrahan]

Thanks, this answer gives me lots of flexibility and options.

[Joseph Astrahan]

Do you know how to do a filter on a string set? like group, so it only shows results belonging to group default?

[Joseph Astrahan]

I created a question to explain this here, stackoverflow.com/questions/47585581/…

[Dmitry Grinko]

I upvoted your question. I am not sure is it possible with dynamodb. I would suggest to filter data in your lambda after getting all data..

[Joseph Astrahan]

By chance do you know how to get access to the group string set at least in a scan?

[Joseph Astrahan]

figured it out, for others its like this, FilterExpression: "loggedIn = :loggedIn and #s = :status and contains(#g,:group) ", and of course add this, ExpressionAttributeNames: {"#s": "status","#g" : "group"}. I had to do this for status and group because they are reserved keywords in DynamoDB

[Dmitry Grinko]

I'm using documentClient docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/DynamoDB/…

[Joseph Astrahan]

figured it out, for others its like this, FilterExpression: "loggedIn = :loggedIn and #s = :status and contains(#g,:group) ", and of course add this, ExpressionAttributeNames: {"#s": "status","#g" : "group"}. I had to do this for status and group because they are reserved keywords in DynamoDB