MongoDB（12）- 查詢嵌入文檔的數組

插入測試數據

db.inventory.insertMany( [
   { item: "journal", instock: [ { warehouse: "A", qty: 5 }, { warehouse: "C", qty: 15 } ] },
   { item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] },
   { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] },
   { item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
   { item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);

後面的栗子都會用到這裡的測試數據

查詢嵌套在數組中的文檔

查詢 instock 數組中包含 { warehouse: “A”, qty: 5 } 的所有文檔

> db.inventory.find( { "instock": { warehouse: "A", qty: 5 } } )
{ "_id" : ObjectId("60b6dbbf67b3da7412587546"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }

嵌套文檔內的字段，除了字段名、字段值一樣，順序也得一致，否則不匹配

精確匹配整個文檔數組的栗子

> db.inventory.find({instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ]})
{ "_id" : ObjectId("60b6dbbf67b3da7412587548"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 15 } ] }

在文檔數組中嵌入的字段上指定查詢條件

在 instock 數組中，至少有一個文檔的 qty 字段值是 ≤20 的

> db.inventory.find( { 'instock.qty': { $lte: 20 } } )
{ "_id" : ObjectId("60b6dbbf67b3da7412587546"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da7412587547"), "item" : "notebook", "instock" : [ { "warehouse" : "C", "qty" : 5 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da7412587548"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 15 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da7412587549"), "item" : "planner", "instock" : [ { "warehouse" : "A", "qty" : 40 }, { "warehouse" : "B", "qty" : 5 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da741258754a"), "item" : "postcard", "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }

訪問數組中文檔的字段，如果不知道文檔的準確索引值，只能按照以下格式

數組字段名.文檔字段名
instock.qty

使用數組索引查詢嵌入文檔中的字段

上面的栗子是直接根據字段名查找

在 instock 數組中，第一個元素包含字段 qty ，且值 ≤20 的文檔

> db.inventory.find( { 'instock.0.qty': { $lte: 20 } } )
{ "_id" : ObjectId("60b6dbbf67b3da7412587546"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da7412587547"), "item" : "notebook", "instock" : [ { "warehouse" : "C", "qty" : 5 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da741258754a"), "item" : "postcard", "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }

在文檔數組中的字段指定組合（多個）查詢條件

栗子一

找到在 instock 數組中【至少有一個嵌入文檔包含 qty > 10，以及至少有一個嵌入文檔（但不一定是同一個嵌入文檔）包含 qty ≤20 】的文檔

> db.inventory.find( { "instock.qty": { $gt: 10,  $lte: 20 } } )
{ "_id" : ObjectId("60b6dbbf67b3da7412587546"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da7412587548"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 15 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da7412587549"), "item" : "planner", "instock" : [ { "warehouse" : "A", "qty" : 40 }, { "warehouse" : "B", "qty" : 5 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da741258754a"), "item" : "postcard", "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }

不需要在同一個文檔同時滿足兩個條件（當然同一個文檔同時滿足也可以）
只要整個文檔數組中，兩個條件都至少有一個滿足的文檔即可

栗子二

找到在 instock 數組中【至少有一個嵌入文檔包含 qty = 5，以及至少有一個嵌入文檔（但不一定是同一個嵌入文檔）包含 warehouse = A 】的文檔：

> db.inventory.find( { "instock.qty": 5, "instock.warehouse": "A" } )
{ "_id" : ObjectId("60b6dbbf67b3da7412587546"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da7412587549"), "item" : "planner", "instock" : [ { "warehouse" : "A", "qty" : 40 }, { "warehouse" : "B", "qty" : 5 } ] }

在文檔數組中單個嵌套文檔滿足多個查詢條件

前言

上面的栗子都是單個嵌套文檔或多個嵌套文檔滿足多個查詢條件即可

如果想確保單個嵌套文檔必須同時滿足多個查詢條件呢？

使用 $elemMatch 運算符！（前面講數組的時候也提到過）

栗子一

找到在 instock 數組【至少有一個包含 qty = 5 和 warehouse = A 的嵌入文檔】的文檔

> db.inventory.find( { "instock": { $elemMatch: { qty: 5, warehouse: "A" } } } )
{ "_id" : ObjectId("60b6dbbf67b3da7412587546"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }

栗子二

找到在 instock 數組【至少有一個包含 qty > 10 且 ≤ 20 的嵌入文檔】的文檔

> db.inventory.find( { "instock": { $elemMatch: { qty: { $gt: 10, $lte: 20 } } } } )
{ "_id" : ObjectId("60b6dbbf67b3da7412587546"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da7412587548"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 15 } ] }
{ "_id" : ObjectId("60b6dbbf67b3da741258754a"), "item" : "postcard", "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }

Tags: mongodb 測試高級進階技能系列 - MongoDB