How to create Global Secondary Index for Json Attr

Question

I have a table with column Id and JSON column.

Id       |       JSON
----------------------------------------------------------------
101      |  {"person_id":456,"f_name":"t", "l_name":"Jack"}
         |
102      |  {"person_id":123,"f_name":"M", "l_name":"Ron"}
         |
103      |  {"person_id":789,"f_name":"A", "l_name":"Tom"}

I am able to create GSI(Global Secondary Index) on column Id , but I would like to know how to create GSI on person_id and f_name. Is it possible to create GSI for json attributes? As upon observation, I was able to create GSI on columns in tables of Dynamo DB..Please provide a C#.Net example..

Answer 1

You can create a GSI or LSI on any top-level JSON element. So you have to bring the json element to column level and then create the index.

Answer 2

Taken from http://aws.amazon.com/dynamodb/faqs/

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.

Q: If I have nested JSON data in DynamoDB, can I retrieve only a specific element of that data?

Yes. When using the GetItem, BatchGetItem, Query, or Scan APIs, you can define a ProjectionExpression to determine which attributes should be retrieved from the table. Those attributes can include scalars, sets, or elements of a JSON document.

Although I haven't managed to do it, or seen any examples of it being done with cloud formation.