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:
date | grade | score | P_id | _index |
2014-03-03T00:00:00.000Z | A | 2 | 568c37b748ddf53c5ed98932 | 1 |
2013-09-11T00:00:00.000Z | A | 6 | 568c37b748ddf53c5ed98932 | 2 |
2013-01-24T00:00:00.000Z | A | 10 | 568c37b748ddf53c5ed98932 | 3 |
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_id | date | grade | score | P_id | _index |
30075445 | 2014-03-03T00:00:00.000Z | A | 2 | 568c37b748ddf53c5ed98932 | 1 |
30075445 | 2013-09-11T00:00:00.000Z | A | 6 | 568c37b748ddf53c5ed98932 | 2 |
30075445 | 2013-01-24T00:00:00.000Z | A | 10 | 568c37b748ddf53c5ed98932 | 3 |
30075445 | 2011-11-23T00:00:00.000Z | A | 9 | 568c37b748ddf53c5ed98932 | 4 |
30075445 | 2011-03-10T00:00:00.000Z | B | 14 | 568c37b748ddf53c5ed98932 | 5 |