MongoDB (12)-Query an array of embedded documents

Insert test data

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 } ] }]);

The following chestnuts will use the test data here

Query documents nested in an array

Query  all documents that contain {warehouse: "A", qty: 5} in the instock array

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

Fields in nested documents, except for field names and field values, must be in the same order, otherwise they will not match

Chestnut that exactly matches the entire document array

> 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 } ] }

Specify query conditions on fields embedded in the document array

In the instock array, the qty field value of at least one document is ≤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 } ] }

To access the field of the document in the array, if you don’t know the exact index value of the document, you can only follow the format below

数组字段名.文档字段名instock.qty

Use array index to query fields in embedded documents

The chestnuts above are searched directly based on the field name

In the instock array, the first element contains the field qty, and the value ≤ 20 documents

> 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 } ] }

Specify the combination (multiple) query conditions in the fields in the document array

Chestnut one

Find the documents in the instock array [at least one embedded document contains qty> 10, and at least one embedded document (but not necessarily the same embedded document) contains 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 } ] }
  • There is no need to meet both conditions in the same document (of course, the same document can also meet at the same time)
  • As long as there is at least one document that meets both conditions in the entire document array

Chestnut II

Find the documents in the instock array [at least one embedded document contains qty = 5, and at least one embedded document (but not necessarily the same embedded document) contains 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 } ] }

A single nested document in the document array satisfies multiple query conditions

Preface

The above chestnuts are all single nested documents or multiple nested documents that satisfy multiple query conditions.

What if you want to ensure that a single nested document must satisfy multiple query conditions at the same time?

Use the $elemMatch operator! (I also mentioned it when I talked about arrays earlier)

Chestnut one

Find the document in the instock array [There is at least one embedded document containing qty = 5 and 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 } ] }

Chestnut II

Find the document in the instock array [There is at least one embedded document containing qty> 10 and ≤ 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 } ] }