Amazon DynamoDB documents and lists are supported with the CData ODBC Driver for Amazon DynamoDB. You can access documents and lists directly at the root level or use the '.' character as a hierarchy divider to drill down to documents and lists.

Reporting Values in Documents and Lists

When data types are autodetected, they are reported down to the lowest level that can be reliably detected. For instance, a document called Customer with a child called Address and a child on Address called Street would be represented by the column Customer.Address.Street.

However, this process does not apply to Lists since a list could have any number of entries. Once a List or a Set is detected, additional values are not reported as being available in the table schema.

Getting Back Unreported Values

If there are attributes that frequently do not have a value and thus are not autodetected, these can still be retrieved by specifying the correct path to them. For instance, to get the Special attribute from the Customer document:

SELECT [Customer.Address.Street], [Customer.Special] FROM MyTable

Once a List has been detected, additional values are not reported. But individual values on the list can be referenced by specifying '.' and a number. For instance:

SELECT [MyList.0], [MyList.1.Email], [MyList.1.Age] FROM MyTable

This will retrieve the first value on the list and the second value's Email and Age attributes.

Inserting Documents and Lists

INSERTs in Amazon DynamoDB require that the full object is specified. Insert a document or list at the root. Pass in the full JSON aggregate. For instance:

INSERT INTO MyTable (PrimaryKey, EmailAddresses, Address, MyList) VALUES ('uniquekey', '["[email protected]", "[email protected]"]', '{"Street":"123 Fake Street", "City":"Chapel Hill", "Zip":"27713"}', '[{"S":"somestr"},{"NS":[1,2]},{"N":4}]')

In this case, the EmailAddress is inserted as a StringSet, Address is inserted as a document, and MyList is inserted as a list.

Updating Documents and Lists

Updates are supported using the same syntax that is available during selects. Documents and Lists can be specified using the '.' character to specify hierarchy. For instance:

UPDATE MyTable SET [EmailAddress.0]='[email protected]', [EmailAddress.1]='[email protected]', [Address.Street]='123 Fake Street', [Address.City]='Chapel Hill', [Address.Zip]='27713', [MyList.0]='somestr', [MyList.1]='[1,2]', [MyList.2]=4 WHERE PrimaryKey='uniquekey'

Note that EmailAddress and MyList must be autodetected to resolve how to handle EmailAddress differently from MyList. If you are in doubt about whether or not something will be automatically detected, specifying the full JSON to update will always work.