BizTalk Adapter for Cosmos DB

Build 21.0.7930

Vertical Flattening

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

{
  "_id" : ObjectId("568c37b748ddf53c5ed98932"),
  "address" : {
    "building" : "1007",
    "coord" : [-73.856077, 40.848447],
    "street" : "Morris Park Ave",
    "zipcode" : "10462"
  },
  "borough" : "Bronx",
  "cuisine" : "Bakery",
  "grades" : [{
      "date" : ISODate("2014-03-03T00:00:00Z"),
      "grade" : "A",
      "score" : 2
    }, {
      "date" : ISODate("2013-09-11T00:00:00Z"),
      "grade" : "A",
      "score" : 6
    }, {
      "date" : ISODate("2013-01-24T00:00:00Z"),
      "grade" : "A",
      "score" : 10
    }, {
      "date" : ISODate("2011-11-23T00:00:00Z"),
      "grade" : "A",
      "score" : 9
    }, {
      "date" : ISODate("2011-03-10T00:00:00Z"),
      "grade" : "B",
      "score" : 14
    }],
  "name" : "Morris Park Bake Shop",
  "restaurant_id" : "30075445"
}
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:

dategradescoreP_id_index
2014-03-03T00:00:00.000ZA2568c37b748ddf53c5ed989321
2013-09-11T00:00:00.000ZA6568c37b748ddf53c5ed989322
2013-01-24T00:00:00.000ZA10568c37b748ddf53c5ed989323

You may also want to include information from the base restaurants table. You can do this with a join. Flattened arrays can only be joined with the root document. The adapter expects the left part of the join is the array document you want to flatten vertically. Disable SupportEnhancedSQL to join nested Cosmos DB documents -- this type of query is supported through the Cosmos DB API.

SELECT [restaurants].[restaurant_id], [restaurants.grades].* FROM [restaurants.grades] JOIN [restaurants] WHERE [restaurants].name = 'Morris Park Bake Shop'
This query returns the following data set:

restaurant_iddategradescoreP_id_index
300754452014-03-03T00:00:00.000ZA2568c37b748ddf53c5ed989321
300754452013-09-11T00:00:00.000ZA6568c37b748ddf53c5ed989322
300754452013-01-24T00:00:00.000ZA10568c37b748ddf53c5ed989323
300754452011-11-23T00:00:00.000ZA9568c37b748ddf53c5ed989324
300754452011-03-10T00:00:00.000ZB14568c37b748ddf53c5ed989325

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930