DynamoDB JavaScript SDK v2 v3: query – 6

Renzo Mischianti

3 years ago

Spread the love

The Query operation in Amazon DynamoDB finds items based on primary key values.

You must provide the name of the partition key attribute and a single value for that attribute. Query returns all items with that partition key value. Optionally, you can provide a sort key attribute and use a comparison operator to refine the search results. (cit. Amazon)

Key Condition Expression

To specify the search criteria, you use a key condition expression—a string that determines the items to be read from the table or index.

You must specify the partition key name and value as an equality condition.

You can optionally provide a second condition for the sort key (if present). The sort key condition must use one of the following comparison operators:

a = b — true if the attribute a is equal to the value b
a < b — true if a is less than b
a <= b — true if a is less than or equal to b
a > b — true if a is greater than b
a >= b — true if a is greater than or equal to b
a BETWEEN b AND c — true if a is greater than or equal to b, and less than or equal to c.
begins_with (a, substr)— true if the value of attribute a begins with a particular substring.

So you can specify in the params condition like this:

    KeyConditionExpression: 'ItemId = :endpoint_category and begins_with(ItemName, :endpoint_sub_domain)',

the attribute of the expression must declared in a separate section so you can add this declaration section

ExpressionAttributeValues: {
        ':vn': {N: '2'},
        ':endpoint_category' : {S: 'mischianti'},
        ':endpoint_sub_domain' : {S: 'mqtt'}
    },

where you can specify a variable name to use in the other section.

Filter Expressions for Query

If you need to further refine the Query results, you can optionally provide a filter expression. A filter expression determines which items within the Query results should be returned to you. All of the other results are discarded.

A filter expression is applied after a Query finishes, but before the results are returned. Therefore, a Query consumes the same amount of read capacity, regardless of whether a filter expression is present.

A Query operation can retrieve a maximum of 1 MB of data. This limit applies before the filter expression is evaluated.

A filter expression cannot contain partition key or sort key attributes. You need to specify those attributes in the key condition expression, not the filter expression.

The syntax for a filter expression is identical to that of a condition expression. Filter expressions can use the same comparators, functions, and logical operators as a condition expression, with the addition of the not-equals operator (<>). (Cit. Amazon)

So for additional filter you can add a section like this:

    FilterExpression: 'ValueNum > :vn',

where ValueNum isn’t a Key but a data attribute.

AWS Console query

Before check the script in javascript we take a look on AWS console, the same operation can be do with Web interface.

Go to the DynamoDB page;
Select Table on the left menu;
Select Items tab;

or with this url but check the region:

https://eu-west-1.console.aws.amazon.com/dynamodb/home?region=eu-west-1#tables:selected=TestTableMischianti;tab=items

In the header of the Table you can select Add filter;
Select your filter and click on Start search.

Now to get a query that check:

ItemId (the category): must be equal to ‘mischianti’;
ItemName (full url): start with ‘mqtt’;
ValueNum (sub-domain level): must be greater than 2.

From console is quite simple, here the image of selection:

DynamoDB Query table from AWS Web Console

Script to query table

Now we are going to execute script to query table from NodeJS.

We have already seen the parameters, here the complete json structure:

var params = {
    TableName: "TestTableMischianti",
    ExpressionAttributeValues: {
        ':vn': {N: '2'},
        ':endpoint_category' : {S: 'mischianti'},
        ':endpoint_sub_domain' : {S: 'mqtt'}
    },
    KeyConditionExpression: 'ItemId = :endpoint_category and begins_with(ItemName, :endpoint_sub_domain)',
    ProjectionExpression: 'ItemName, ValueStr',
    FilterExpression: 'ValueNum > :vn',
};

SDK v2

Now the asynchronous script with SDK v2.

To execute this script go in the folder dynamodb-examples\jsv2 and launch node items_query.js.

/*
 * DynamoDB Script Examples
 * Query items with DynamoDB
 * DB of selected region in AWS.config.update
 *
 * AUTHOR:  Renzo Mischianti
 *
 * https://mischianti.org/
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2017 Renzo Mischianti www.mischianti.org All right reserved.
 *
 * You may copy, alter and reuse this code in any way you like, but please leave
 * reference to www.mischianti.org in your comments if you redistribute this code.
 */

var AWS = require("aws-sdk");

AWS.config.update({
    apiVersion: '2012-08-10',
    region: "eu-west-1",
    // endpoint: "http://localhost:8000",
    // // accessKeyId default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // accessKeyId: "9oiaf7",
    // // secretAccessKey default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // secretAccessKey: "yz5i9"
});

var ddb = new AWS.DynamoDB();

console.log("Start script!");

var params = {
    TableName: "TestTableMischianti",
    ExpressionAttributeValues: {
        ':vn': {N: '2'},
        ':endpoint_category' : {S: 'mischianti'},
        ':endpoint_sub_domain' : {S: 'mqtt'}
    },
    KeyConditionExpression: 'ItemId = :endpoint_category and begins_with(ItemName, :endpoint_sub_domain)',
    ProjectionExpression: 'ItemName, ValueStr',
    FilterExpression: 'ValueNum > :vn',
};


ddb.query(params, function (err, data) {
    if (err) {
        console.error("Unable ti get item", JSON.stringify(params, null, 2), ". Error JSON:", JSON.stringify(err, null, 2));
    } else {
        console.log("Get Item succeeded:", JSON.stringify(data, null, 2));
    }
});

console.log("End script!");

And here the result:

D:\Projects\AlexaProjects\dynamodb-management\dynamodb-examples\jsv2>node items_query.js
Start script!
End script!
Get Item succeeded: {
  "Items": [
    {
      "ItemName": {
        "S": "mqtt.home.mischianti.org"
      },
      "ValueStr": {
        "S": "MQTT test url mischianti"
      }
    }
  ],
  "Count": 1,
  "ScannedCount": 2
}

In the response there is important information in addition to the data, the Count and ScannedCount, the Count is the number of results, ScannedCount is the number of line scanned, this parameter give you the efficiency of your query, It’s the number of line filtered by Key where you are going to apply the filter, less is the number more is the efficiency.

Now the Synchronous script node items_query_async_await.js.

/*
 * DynamoDB Script Examples
 * Query items with DynamoDB
 * DB of selected region in AWS.config.update
 *
 * AUTHOR:  Renzo Mischianti
 *
 * https://mischianti.org/
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2017 Renzo Mischianti www.mischianti.org All right reserved.
 *
 * You may copy, alter and reuse this code in any way you like, but please leave
 * reference to www.mischianti.org in your comments if you redistribute this code.
 */

var AWS = require("aws-sdk");

AWS.config.update({
    apiVersion: '2012-08-10',
    region: "eu-west-1",
    // endpoint: "http://localhost:8000",
    // // accessKeyId default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // accessKeyId: "9oiaf7",
    // // secretAccessKey default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // secretAccessKey: "yz5i9"
});

var ddb = new AWS.DynamoDB();

(async function () {
    console.log("Start script!");

    var params = {
        TableName: "TestTableMischianti",
        ExpressionAttributeValues: {
            ':vn': {N: '2'},
            ':endpoint_category' : {S: 'mischianti'},
            ':endpoint_sub_domain' : {S: 'mqtt'}
        },
        KeyConditionExpression: 'ItemId = :endpoint_category and begins_with(ItemName, :endpoint_sub_domain)',
        ProjectionExpression: 'ItemName, ValueStr',
        FilterExpression: 'ValueNum > :vn',
    };

    try {
        const data = await ddb.query(params).promise();
        console.log("Get Item succeeded:", JSON.stringify(data, null, 2));
    }catch (err) {
        console.error("Unable ti get item", JSON.stringify(params, null, 2), ". Error JSON:", JSON.stringify(err, null, 2));
    }

    console.log("End script!");
})();

and the relative console output:

D:\Projects\AlexaProjects\dynamodb-management\dynamodb-examples\jsv2>node items_query_async_await.js
Start script!
Get Item succeeded: {
  "Items": [
    {
      "ItemName": {
        "S": "mqtt.home.mischianti.org"
      },
      "ValueStr": {
        "S": "MQTT test url mischianti"
      }
    }
  ],
  "Count": 1,
  "ScannedCount": 2
}
End script!

You can also use the DocumentClient to simplify the parameters node items_query_doc_client_async_await.js:

/*
 * DynamoDB Script Examples
 * Query items with DynamoDB
 * DB of selected region in AWS.config.update
 *
 * AUTHOR:  Renzo Mischianti
 *
 * https://mischianti.org/
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2017 Renzo Mischianti www.mischianti.org All right reserved.
 *
 * You may copy, alter and reuse this code in any way you like, but please leave
 * reference to www.mischianti.org in your comments if you redistribute this code.
 */

var AWS = require("aws-sdk");

AWS.config.update({
    apiVersion: '2012-08-10',
    region: "eu-west-1",
    // endpoint: "http://localhost:8000",
    // // accessKeyId default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // accessKeyId: "9oiaf7",
    // // secretAccessKey default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // secretAccessKey: "yz5i9"
});

var docClient = new AWS.DynamoDB.DocumentClient();

(async function () {
    console.log("Start script!");

    var params = {
        TableName: "TestTableMischianti",
        ExpressionAttributeValues: {
            ':vn': 2,
            ':endpoint_category' : 'mischianti',
            ':endpoint_sub_domain' : 'mqtt'
        },
        KeyConditionExpression: 'ItemId = :endpoint_category and begins_with(ItemName, :endpoint_sub_domain)',
        ProjectionExpression: 'ItemName, ValueStr',
        FilterExpression: 'ValueNum > :vn',
    };

    try {
        const data = await docClient.query(params).promise();
        console.log("Get Item succeeded:", JSON.stringify(data, null, 2));
    }catch (err) {
        console.error("Unable ti get item", JSON.stringify(params, null, 2), ". Error JSON:", JSON.stringify(err, null, 2));
    }

    console.log("End script!");
})();

the output console become more simple too.

D:\Projects\AlexaProjects\dynamodb-management\dynamodb-examples\jsv2>node items_query_doc_client_async_await.js
Start script!
Get Item succeeded: {
  "Items": [
    {
      "ItemName": "mqtt.home.mischianti.org",
      "ValueStr": "MQTT test url mischianti"
    }
  ],
  "Count": 1,
  "ScannedCount": 2
}
End script!

SDK v3

Now the SDK v3 script with the advised implementation.

To execute this script go in the folder dynamodb-examples\jsv3 and launch node items_query_async_await.js.

/*
 * DynamoDB Script Examples v3
 * Query items with DynamoDB async await
 * DB of selected region in configDynamoDB
 *
 * AUTHOR:  Renzo Mischianti
 *
 * https://mischianti.org/
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2017 Renzo Mischianti www.mischianti.org All right reserved.
 *
 * You may copy, alter and reuse this code in any way you like, but please leave
 * reference to www.mischianti.org in your comments if you redistribute this code.
 */

const { DynamoDBClient, QueryCommand } = require("@aws-sdk/client-dynamodb");
const configDynamoDB = {
    version: 'latest',
    region: "eu-west-1",
    // endpoint: "http://localhost:8000",
    // credentials: {
    //     // accessKeyId default can be used while using the downloadable version of DynamoDB.
    //     // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    //     accessKeyId: "9oiaf7",
    //     // secretAccessKey default can be used while using the downloadable version of DynamoDB.
    //     // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    //     secretAccessKey: "yz5i9"
    //
    // }
};

const dbClient = new DynamoDBClient(configDynamoDB);

(async function () {
    console.log("Start script!");

    var params = {
        TableName: "TestTableMischianti",
        ExpressionAttributeValues: {
            ':vn': {N: '2'},
            ':endpoint_category' : {S: 'mischianti'},
            ':endpoint_sub_domain' : {S: 'mqtt'}
        },
        KeyConditionExpression: 'ItemId = :endpoint_category and begins_with(ItemName, :endpoint_sub_domain)',
        ProjectionExpression: 'ItemName, ValueStr',
        FilterExpression: 'ValueNum > :vn',
    };

    try {
        const command = new QueryCommand(params);
        const data = await dbClient.send(command);
        console.log("PutItem succeeded:", JSON.stringify(params, null, 2), JSON.stringify( data, null, 2),);
    }catch (err) {
        console.error("Unable add item", JSON.stringify(params, null, 2), ". Error JSON:", JSON.stringify(err, null, 2));
    }

    console.log("End script!");
})();

And here the output console

D:\Projects\AlexaProjects\dynamodb-management\dynamodb-examples\jsv3>node items_query_async_await.js
Start script!
PutItem succeeded: {
  "TableName": "TestTableMischianti",
  "ExpressionAttributeValues": {
    ":vn": {
      "N": "2"
    },
    ":endpoint_category": {
      "S": "mischianti"
    },
    ":endpoint_sub_domain": {
      "S": "mqtt"
    }
  },
  "KeyConditionExpression": "ItemId = :endpoint_category and begins_with(ItemName, :endpoint_sub_domain)",
  "ProjectionExpression": "ItemName, ValueStr",
  "FilterExpression": "ValueNum > :vn"
} {
  "$metadata": {
    "httpStatusCode": 200,
    "httpHeaders": {
      "server": "Server",
      "date": "Wed, 03 Feb 2021 09:20:04 GMT",
      "content-type": "application/x-amz-json-1.0",
      "content-length": "128",
      "connection": "keep-alive",
      "x-amzn-requestid": "5KF74ED811JMPF7UQHEGII73LFVV4KQNSO5AEMVJF66Q9ASUAAJG",
      "x-amz-crc32": "4135422727"
    },
    "requestId": "5KF74ED811JMPF7UQHEGII73LFVV4KQNSO5AEMVJF66Q9ASUAAJG",
    "attempts": 1,
    "totalRetryDelay": 0
  },
  "Count": 1,
  "Items": [
    {
      "ItemName": {
        "S": "mqtt.home.mischianti.org"
      },
      "ValueStr": {
        "S": "MQTT test url mischianti"
      }
    }
  ],
  "ScannedCount": 2
}
End script!

Thanks

Now you have all instruments to query, manage, create your table in AWS environment.

AWS Support forum

Spread the love