垂直フラット化
ドキュメントの配列を、個別のテーブルのように取得することが可能です。例えば、restaurants コレクションから次のJSON 構造を取得します。
{ "_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" }垂直フラット化ではgrades 配列を別々のテーブルとして取得することを許可します。
SELECT * FROM [restaurants.grades]このクエリは、次のデータセットを返します。
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 |
SELECT [restaurants].[restaurant_id], [restaurants.grades].* FROM [restaurants.grades] JOIN [restaurants] WHERE [restaurants].name = 'Morris Park Bake Shop'このクエリは、次のデータセットを返します。
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 |
他の配列の中にある配列を対象としたクエリを作成することも可能です。
以下がInventory コレクションの例です。
{ "_id": { "$oid": "xxxxxxxxxxxxxxxxxxxxxx" }, "Company Branch": "Main Branch", "ItemList": [ { "item": "journal", "instock": [ { "warehouse": "A", "qty": 15 }, { "warehouse": "B", "qty": 45 } ] }, { "item": "paper", "instock": [ { "warehouse": "A", "qty": 50 }, { "warehouse": "B", "qty": 5 } ] } ] }
Insert data into the nested arrays using the syntax of <parent array>.<index>.<child array>, as follows:
INSERT INTO [Inventory.ItemList] (p_id, item, [instock.0.warehouse], [instock.0.qty], [instock.0.price]) VALUES ('xxxxxxxxxxxxxxxxxxxxxx', 'NoteBook', 'B', 20, '5$')
The Inventory collection after executing the INSERT statement:
{ "_id": { "$oid": "xxxxxxxxxxxxxxxxxxxxxx" }, "Company Branch": "Main Branch", "ItemList": [ { "item": "journal", "instock": [ { "warehouse": "A", "qty": 15 }, { "warehouse": "B", "qty": 45 } ] }, { "item": "paper", "instock": [ { "warehouse": "A", "qty": 50 }, { "warehouse": "B", "qty": 5 } ] }, { "item": "NoteBook", "instock": [ { "warehouse": "B", "qty": 20, "price": "5$" } ] } ] }