BizTalk Adapter for Amazon DynamoDB

Build 20.0.7654

Vertical Flattening

It is possible to retrieve an array of objects as if it were a separate table. Take the following JSON structure from the restaurants collection 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" : "A",
      "score" : 2
    }, {
      "date" : 1378857600000,
      "grade" : "A",
      "score" : 6
    }, {
      "date" : 1358985600000,
      "grade" : "A",
      "score" : 10
    }, {
      "date" : 1322006400000,
      "grade" : "A",
      "score" : 9
    }, {
      "date" : 1299715200000,
      "grade" : "B",
      "score" : 14
    }],
  "name" : "Morris Park Bake Shop",
}
Vertical flattening will allow you to retrieve the grades array as a separate table:
SELECT * FROM [restaurants.grades]
This query returns the following data set:

dategradescorerestaurantid_index
1393804800000A2300754451
1378857600000A6300754452
1358985600000A10300754453

You may also want to include information from the base restaurants table. You can do this with a join.

SELECT restaurants.name, [restaurants.grades].* FROM [restaurants] JOIN [restaurants.grades] ON restaurants.restaurantid = [restaurants.grades].restaurantid WHERE restaurants.restaurantid = 30075445 AND [restaurants.grades].restaurantid = 30075445
This query returns the following data set:

namerestaurantiddategradescore_index
Morris Park Bake Shop300754451393804800000A21
Morris Park Bake Shop300754451378857600000A62
Morris Park Bake Shop300754451358985600000A103
Morris Park Bake Shop300754451322006400000A94
Morris Park Bake Shop300754451299715200000B145

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7654