Vertical Flattening
Example Document
/* Primary key "1" */ { "address" : { "building" : "1007", "coord" : [-73.856077, 40.848447], "street" : "Morris Park Ave", "zipcode" : "10462" }, "borough" : "Bronx", "cuisine" : "Bakery", "grades" : [{ "date" : "2014-03-03T00:00:00Z", "grade" : "A", "score" : 2 }, { "date" : "2013-09-11T00:00:00Z", "grade" : "A", "score" : 6 }, { "date" : "2013-01-24T00:00:00Z", "grade" : "A", "score" : 10 }, { "date" : "2011-11-23T00:00:00Z", "grade" : "A", "score" : 9 }, { "date" : "2011-03-10T00:00:00Z", "grade" : "B", "score" : 14 }], "name" : "Morris Park Bake Shop", "restaurant_id" : "30075445" }
Selecting Values In Objects
If the FlattenObjects property is configured to allow object flattening, then the connector will traverse objects and map the fields inside them as columns. For example, this query:SELECT [address.building], [address.street] FROM restaurantsWould return this resultset:
address.building | addres.street |
1007 | Morris Park Ave |
Selecting Values In Arrays
If the FlattenArrays property is configured to allow array flattening, then the connector will traverse arrays and map their individual values as columns. For example, if Flatten Arrays were set to "2", then this query:SELECT [address.coord.0], [address.coord.1] FROM restaurantsWould return this resultset:
address.coord.0 | address.coord.1 |
-73.856077 | 40.838447 |
Note that array flattening should only be used in cases where you know the number of array items in advance, such as with "address.coord" which will always contain two items. For arrays like "grades" which can contain arbitrary numbers of items, consider using the child tables described in Automatic Schema Discovery instead, since they will allow you to read all of the values within the array.