Working with JSON data in Amazon DynamoDB

Amazon DynamoDB allows you to store JSON objects into attributes and perform many operations on these objects, including filtering, updating, and deleting. This is a very powerful capability because it allows applications to store objects (JSON data, arrays) directly into DynamoDB tables, and still retain the ability to use nested attributes within these objects in their database operations. In this post, we explore some of the things that you can do with JSON objects in DynamoDB.

Installing DynamoDB shell (ddbsh)

You can install DynamoDB shell (ddbsh) for Mac using homebrew, and for Ubuntu using pre-built binaries that are available on github. Refer to the “Using prebuilt binaries” section.

Solution overview

We use DynamoDB shell (ddbsh) to illustrate these capabilities. To learn more, refer to Query data with DynamoDB Shell – a command line interface for Amazon DynamoDB. One of the objectives of ddbsh is to provide a simple and intuitive environment for newcomers to DynamoDB to get started by running familiar SQL-like commands.

The query language in ddbsh is derived from SQL and has DynamoDB-specific extensions.

For this post, we use a simple DynamoDB table that tracks information about fast food orders. The following code is an example order:

{ ono: 12, ino: 1, details: { entree: “burger”, sides: [ “fries”, “soda” ]}, status: “WIP” }

An order that was completed looks like the following:

{ ono: 15, ino: 1, details: { entree: “salad”, sides: [ “apple”, “water” ]}, status: “DONE” }

An order that had three items looks like the following:

{ ono: 14, ino: 1, details: { entree: “salad”, sides: [ “apple”, “water” ]}, status: “WIP” }
{ ono: 14, ino: 2, details: { entree: “BLT sw”, sides: [ “water” ]}, status: “WIP” }

In the following sections, we show how to create a table and insert some sample data, query the data, update items, and add and delete attributes. We also discuss how to turn SQL into a set of API calls from your application.

Prerequisites

To get started, you need to have DynamoDB shell installed. For more information, refer to Query data with DynamoDB Shell – a command line interface for Amazon DynamoDB.

Prepare your data

Create a DynamoDB table with the following code:

us-east-1> CREATE TABLE ORDERS ( ono number, ino number ) PRIMARY KEY (ono hash, ino range);
CREATE
us-east-1>

Next, populate the four rows into DynamoDB with the following code:

us-east-1> insert into ORDERS (ono, ino, details, status)
values (12, 1, {entree: "burger", sides: ["fries", "soda"]}, "WIP"),
(14, 1, {entree: "salad", sides: ["apple", "water"]}, "WIP"),
(14, 2, {entree: "BLT sw", sides: ["water"]}, "WIP"),
(15, 1, {entree: "salad", sides: ["apple", "water"]}, "DONE");
INSERT
INSERT
INSERT
INSERT
us-east-1> select * from ORDERS;
{details: {entree:salad, sides:[apple, water]}, ino: 1, ono: 14, status: WIP}
{details: {entree:"BLT sw", sides:[water]}, ino: 2, ono: 14, status: WIP}
{details: {entree:burger, sides:[fries, soda]}, ino: 1, ono: 12, status: WIP}
{details: {entree:salad, sides:[apple, water]}, ino: 1, ono: 15, status: DONE}
us-east-1>

Now you’re ready to start querying your data.

Query your data

You can find the first item in order 14 by running the following query:

us-east-1> select * from ORDERS where ono = 14 and ino = 1;
{details: {entree:salad, sides:[apple, water]}, ino: 1, ono: 14, status: WIP}
us-east-1>

You can also perform other queries, such as finding all the order lines that include a side of water that aren’t yet complete:

us-east-1> select * from ORDERS where status != "DONE" and contains(details.sides, "water");
{details: {entree:salad, sides:[apple, water]}, ino: 1, ono: 14, status: WIP}
{details: {entree:"BLT sw", sides:[water]}, ino: 2, ono: 14, status: WIP}
us-east-1>

Here, DynamoDB unpacked the JSON object (details), found the attribute called sides, and looked in that array to see whether there was one that included water.

In the following query, an order of fries is ready, and we want to know which order should it be sent to:

us-east-1> select ono, ino from ORDERS where contains(details.sides, "fries") and status = "WIP";
{ino: 1, ono: 12}
us-east-1>

Similarly, if a burger is ready, we can determine which order should it be attached to:

us-east-1> select ono, ino from ORDERS where details.entree = "burger" and status = "WIP";
{ino: 1, ono: 12}
us-east-1>

Update items

You can also update these orders with DynamoDB shell. For example, the customer who placed order 14, item 1, changed their mind and instead of water would like a soda:

us-east-1> select * from ORDERS where ono = 14 and ino = 1;
{details: {entree:salad, sides:[apple, water]}, ino: 1, ono: 14, status: WIP}
us-east-1>

us-east-1> update ORDERS set details.sides[1] = "soda" where ono = 14 and ino = 1;
UPDATE (0 read, 1 modified, 0 ccf)
us-east-1>

us-east-1> select * from ORDERS where ono = 14 and ino = 1;
{details: {entree:salad, sides:[apple, soda]}, ino: 1, ono: 14, status: WIP}
us-east-1>

Observe that the update references items in the array details.sides by their index. The second item (index 1) is updated here.

Add attributes to and delete attributes from an embedded object

If a customer has a special request, you can add it to the order:

us-east-1> update ORDERS set details.notes = "extra dressing" where ono = 14 and ino = 1;
UPDATE (0 read, 1 modified, 0 ccf)
us-east-1> select * from ORDERS where ono = 14;
{details: {entree:salad, notes:"extra dressing", sides:[apple, water]}, ino: 1, ono: 14, status: WIP}
{details: {entree:"BLT sw", sides:[water]}, ino: 2, ono: 14, status: WIP}
us-east-1>

If the customer who ordered the BLT sandwich decided they didn’t want the water after all, we can use the following code:

us-east-1> update ORDERS remove details.sides where ono = 14 and ino = 2;
UPDATE (0 read, 1 modified, 0 ccf)
us-east-1> select * from ORDERS where ono = 14 and ino = 2;
{details: {entree:"BLT sw"}, ino: 2, ono: 14, status: WIP}
us-east-1>

Translate SQL to the DynamoDB API

When writing applications with DynamoDB, it’s useful to quickly prototype the action you want to perform using SQL, but you then might want to turn it into a set of API calls from your application. ddhsh helps you understand how to code the UPDATE statement shown earlier using the EXPLAIN command:

us-east-1> explain update ORDERS set details.sides[1] = "soda" where ono = 14 and ino = 1;
UpdateItem({
   "TableName":   "ORDERS",
   "Key":   {
      "ino":   {
         "N":   "1"
      },
      "ono":   {
         "N":   "14"
      }
   },
   "UpdateExpression":   "SET #aTaa1.#aTaa2[1] = :vTaa1",
   "ConditionExpression":   "attribute_exists(#aTaa3)",
   "ExpressionAttributeNames":   {
      "#aTaa1":   "details",
      "#aTaa2":   "sides",
      "#aTaa3":   "ono"
   },
   "ExpressionAttributeValues":   {
      ":vTaa1":   {
         "S":   "soda"
      }
   }
})
us-east-1>

EXPLAIN shows that the query (which had a full primary key, ono and ino) can be expressed as an UpdateItem() API call. The table name and the key are specified. An update expression is encoded as follows:

"UpdateExpression":   "SET #aTaa1.#aTaa2[1] = :vTaa1"

This is done to handle any reserved words that may exist in your schema. By replacing attribute names with placeholders, you can safely perform this update. Here ddbsh changed details to #aTaa1 and sides to #aTaa2. The value (soda) was replaced with the placeholder :vTaa1. Because DynamoDB UpdateItem() will insert an item if one doesn’t already exist, ddbsh emits a check, where #aTaa3 is a placeholder for the attribute ono:

"ConditionExpression": "attribute_exists(#aTaa3)"

Because the Key condition provided the full primary key, if an item matching this primary key existed, then the attribute ono would exist in the item.

The following code is another example of translating a request into an API call:

us-east-1> explain update ORDERS remove details.sides where ono = 14 and ino = 2;
UpdateItem({
   "TableName":   "ORDERS",
   "Key":   {
      "ino":   {
         "N":   "2"
      },
      "ono":   {
         "N":   "14"
      }
   },
   "UpdateExpression":   "REMOVE #aeba1.#aeba2",
   "ConditionExpression":   "attribute_exists(#aeba3)",
   "ExpressionAttributeNames":   {
      "#aeba1":   "details",
      "#aeba2":   "sides",
      "#aeba3":   "ono"
   }
})
us-east-1>

Clean up

When you’re done with your experiments, remember to remove any tables you might have created to avoid incurring costs. You can use DROP TABLE in ddbsh to do so:

us-east-1> drop table ORDERS;
DROP
us-east-1>

Conclusion

DynamoDB allows you to store JSON data into an attribute. Then you can query data and perform restrictions and modifications to attributes inside the JSON object.

We’ve heard from customers and prospects that they want resources that will help them learn the DynamoDB API. We’re experimenting with some solutions to this, such as the EXPLAIN command in ddbsh, which we demonstrated in this post. We would appreciate your feedback on this tool. What can we add to ddbsh that would make it easier for you to use DynamoDB?

Finally, if there are specific functions or functionalities that you would like to see implemented in ddbsh, let us know by entering a support ticket or by emailing me directly at amrithie (at) amazon.com.

About the author

Amrith Kumar is a Senior Principal Engineer in Amazon Web Services and works on Amazon DynamoDB.