Querying a parameter that’s not an index on DynamoDb

Question

TableName : people

id | name | age | location

id_1 | A | 23 | New Zealand

id_2 | B | 12 | India

id_3 | C | 26 | Singapore

id_4 | D | 30 | Turkey

keys: id -> hash and age->range

Question 1

I’m trying to execute a query: “Select * from people where age > 25” I can get it to work queries like “Select age from people where id = id_1 and age > 25” which is not what I need, just need to select all values.

And if I don’t need age to be a range index, how should i modify my query params to just return the list of records matching the criterion: age > 25?

Question 2

AWS throws an error when either Lines 23 or 24-41 are commented. : Query Error: ValidationException: Either the KeyConditions or KeyConditionExpression parameter must be specified in the request. status code: 400, request id: []

Is the KeyConditions/KeyConditionsExpressions parameter required? Does it mean that I cannot query the table on a parameter that's not a part of the index?

  func queryDynamo() {
        log.Println("Enter queryDynamo")

        svc := dynamodb.New(nil)

        params := &dynamodb.QueryInput{
            TableName: aws.String("people"), // Required
            Limit:     aws.Long(3),
            // IndexName: aws.String("localSecondaryIndex"),
            ExpressionAttributeValues: map[string]*dynamodb.AttributeValue{
                ":v_age": { // Required
                    N: aws.String("25"),
                },
                ":v_ID": {
                    S: aws.String("NULL"),
                },
            },
            FilterExpression: aws.String("age >= :v_age"),

            // KeyConditionExpression: aws.String("id = :v_ID and age >= :v_age"),
            KeyConditions: map[string]*dynamodb.Condition{
                "age": { // Required
                    ComparisonOperator: aws.String("GT"), // Required
                    AttributeValueList: []*dynamodb.AttributeValue{
                        { // Required
                            N: aws.String("25"),
                        },
                        // More values...
                    },
                },
                "id": { // Required
                    ComparisonOperator: aws.String("EQ"), // Required
                    // AttributeValueList: []*dynamodb.AttributeValue{
                    //  S: aws.String("NOT_NULL"),
                    // },
                },
                // More values...
            },
            Select:           aws.String("ALL_ATTRIBUTES"),
            ScanIndexForward: aws.Boolean(true),
        }

//Get the response and print it out.
        resp, err := svc.Query(params) 

        if err != nil {
            log.Println("Query Error: ", err.Error())
        }

        // Pretty-print the response data.
        log.Println(awsutil.StringValue(resp))
    }

JaredHatfield JaredHatfield · Accepted Answer · 2015-07-24T22:19:31

DynamoDB is a NoSQL based system so you will not be able to retrieve all of the records based on a condition on a non-indexed field without doing a table scan.

A table scan will cause DynamoDB to go through every single record in the table, which for a big table will be very expensive in either time (it is slow) or money (provisioned read IOPS).

Using a filter is the correct approach and will allow the operation to complete if you switch from a query to a scan. A query must always specify the hash key.

A word of warning though: if you plan on using a scan operation on a table of more than just a few (less than 100) items that is exposed in a front end you will be disappointed with the results. If this is some type of cron job or backend reporting task where response time doesn't matter this is an acceptable approach, but be careful not to exhaust all of your IOPS and impact front end applications.

Querying a parameter that’s not an index on DynamoDb

1 Answers