It is possible to retrieve an array of objects as if it were a separate table. Take the following JSON structure from the restaurants table for example:

{
  "restaurantid" : "30075445",
  "address" : {
    "building" : "1007",
    "coord" : [-73.856077, 40.848447],
    "street" : "Morris Park Ave",
    "zipcode" : "10462"
  },
  "borough" : "Bronx",
  "cuisine" : "Bakery",
  "grades" : [{
      "date" : 1393804800000,
      "grade" : "B",
      "score" : 2
    }, {
      "date" : 1378857600000,
      "grade" : "A",
      "score" : 6
    }, {
      "date" : 1358985600000,
      "grade" : "A",
      "score" : 10
    }],
  "name" : "Morris Park Bake Shop"
}

Vertical flattening will allow you to retrieve the grades array as a separate table by using the syntax below:

SELECT * FROM [restaurants.grades]

This query returns the following data set:

date	grade	score	_index
1393804800000	B	2	1
1378857600000	A	6	2
1358985600000	A	10	3

The grades array could also be nested some levels deeper. In that case, the same syntax should be used:

SELECT * FROM [restaurants.cuisine.bakery.grades]

There are also cases where the nested structure includes another array in a higher level. Take the following JSON as an example:

{
  "restaurantid" : "30075445",
  "reviews": [
   {
    "grades": [
     {
      "date": 1393804800000,
      "score": 2,
      "grade": "B"
     },
     {
      "date": 1378857600000,
      "score": 6,
      "grade": "A"
     },
     {
      "date": 1358985600000,
      "score": 10,
      "grade": "A"
     }]
    }],
  "name" : "Morris Park Bake Shop"
}

For this structure, the index of the reviews array will need to get wrapped in square brackets. If they are already being used as escape characters in the SQL query, the square brackets will need to be escaped themselves as shown in the query below:

SELECT * FROM [restaurants.reviews.\[0\].grades]

This query will return the same data set as the JSON structure at the top. Note that this syntax is case sensitive, so make sure to write the field names the same way that they're saved in DynamoDB.