Secondary index on top-level item in JSON in DynamoDB

Question

I put an Item with JSON into a table in DynamoDB:

String jsonString = "{"
+ " \"jsonid\": 4711"
+ " }";
Item item = new Item()
.withPrimaryKey("Id", 1)
.withJSON("jsondata", jsonString);

table.putItem(item);

The json is stored in attribute jsondata as

{ "jsonid" : { "N" : "4711" }}

Is it possible to create an index that indexes "jsonid"?

Or do I have to create an extra attribute for that? According to https://aws.amazon.com/dynamodb/faqs/ it seems that it should be possible, but I don't understand how to specify the index. "jsondata.jsonid" doesn't seem to work when creating the index through the console.

"Q: Is querying JSON data in DynamoDB any different?

No. You can create a Global Secondary Index or Local Secondary Index on any top-level JSON element. For example, suppose you stored a JSON document that contained the following information about a person: First Name, Last Name, Zip Code, and a list of all of their friends. First Name, Last Name and Zip code would be top-level JSON elements. You could create an index to let you query based on First Name, Last Name, or Zip Code. The list of friends is not a top-level element, therefore you cannot index the list of friends. For more information on Global Secondary Indexing and its query capabilities, see the Secondary Indexes section in this FAQ."

kosa kosa · Accepted Answer · 2017-07-21T16:18:32

When AWS documentation says top-level, they meant table level attributes JSON (not attribute/field level Json). For example, based on AWS DynamoDB Json documentation example, Car is "top-level", "Spec" is nested structured and being designated as "Document". You can't create indexes on "Spec", but you can create indexes on attributes of "Car". Same applicable for the example in your question. Attributes of "Person" will be top level elements, but "Friends" will be nested, so you can't really create indexes on "Friends".

One possible solution for your example could be, take out "jsonid" element and then store the JSON, for example: { "N" : "4711" } then you can create index on N. If your structure is this much easy, then you don't even indexes, designating N as primary key would be sufficient too.

Here is Java SDK example on how to create indexes using DynamoDBMapper.

Secondary index on top-level item in JSON in DynamoDB

2 Answers