1. Nested query

1.0 Preparing Data

db.inventory.insertMany( [
   { item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" },
   { item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" },
   { item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" },
   { item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" },
   { item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A"}]);Copy the code

1.1 instance

If we need to query for entries in size whose attribute uom is a specified condition, this is a nested query. Nested queries can be supported in two ways.

  • Query filter document
// uom:"cm" means to query for items whose UMO is cm
db.inventory.find( { size: { h: 14, w: 21, uom: "cm"}})Copy the code
  • Second: dot notation
db.inventory.find( { "size.uom": "in"})// Range queries can also be used
db.inventory.find( { "size.h": { $lt: 15}})Copy the code

2. Array-based queries

2.0 Preparing Data

db.inventory.insertMany([
   { item: "journal", qty: 25, tags: ["blank"."red"], dim_cm: [ 14.21 ] },
   { item: "notebook", qty: 50, tags: ["red"."blank"], dim_cm: [ 14.21 ] },
   { item: "paper", qty: 100, tags: ["red"."blank"."plain"], dim_cm: [ 14.21 ] },
   { item: "planner", qty: 75, tags: ["blank"."red"], dim_cm: [ 22.85.30 ] },
   { item: "postcard", qty: 45, tags: ["blue"], dim_cm: [ 10.15.25]}]);Copy the code

2.1 instance

Mongo can also have matching query syntax support if the field type of an attribute in an entry is array. Details are as follows:

// Matches exactly the elements in the array, if there are multiple elements, the order must be the same
db.inventory.find( { tags: ["red"."blank"]})// Whether the matching field contains the query element, regardless of sorting or other elements
db.inventory.find( { tags: { $all: ["red"."blank"]}})// Support range query
db.inventory.find( { dim_cm: { $gt: 25}})// Support multi-condition query
db.inventory.find( { dim_cm: { $gt: 15, $lt: 20 } } )
db.inventory.find( { dim_cm: { $elemMatch: { $gt: 22, $lt: 30}}})// Use subscripts to specify which elements to compare
db.inventory.find( { "dim_cm.1": { $gt: 25}})// Length alignment
db.inventory.find( { "tags": { $size: 3}})Copy the code

3. Nested document objects based on arrays

3.0 Data Preparation

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

3.1 instance

// single condition query
db.inventory.find( { "instock": { warehouse: "A", qty: 5}})// range query
db.inventory.find( { 'instock.qty': { $lte: 20}})// subscript query
db.inventory.find( { 'instock.0.qty': { $lte: 20}})// Multi-condition query
db.inventory.find( { "instock": { $elemMatch: { qty: { $gt: 10, $lte: 20}}}})Copy the code

4 Query the specified attributes

4.0 Preparing Data

db.inventory.insertMany( [
  { item: "journal", status: "A", size: { h: 14, w: 21, uom: "cm" }, instock: [ { warehouse: "A", qty: 5 } ] },
  { item: "notebook", status: "A",  size: { h: 8.5, w: 11, uom: "in" }, instock: [ { warehouse: "C", qty: 5 } ] },
  { item: "paper", status: "D", size: { h: 8.5, w: 11, uom: "in" }, instock: [ { warehouse: "A", qty: 60 } ] },
  { item: "planner", status: "D", size: { h: 22.85, w: 30, uom: "cm" }, instock: [ { warehouse: "A", qty: 40 } ] },
  { item: "postcard", status: "A", size: { h: 10, w: 15.25, uom: "cm" }, instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35}}]]);Copy the code

4.1 instance

Sometimes all fields are not required. Mongodb supports query of specified fields as follows:

// Query two fields and the default _id field
db.inventory.find( { status: "A" }, { item: 1, status: 1})// Ignore the _id field
db.inventory.find( { status: "A" }, { item: 1, status: 1, _id: 0})// Exclude two fields
db.inventory.find( { status: "A" }, { status: 0, instock: 0})// Contains conditions
db.inventory.find(
   { status: "A" },
   { item: 1, status: 1."size.uom": 1})// Exclude a field from the nesting
db.inventory.find(
   { status: "A" },
   { "size.uom": 0})// Display the array fields in the nesting
db.inventory.find( { status: "A" }, { item: 1, status: 1."instock.qty": 1})// Displays array fields and refers to periodic ranges
db.inventory.find( { status: "A" }, { item: 1, status: 1, instock: { $slice: -1}})Copy the code

5. Aggregate the query

5.1 instance

Mongodb also supports aggregated query to analyze data as follows:

// take the maximum value of the field
db.orders.aggregate([
   { $match: { status: "A" } },
   { $group: { _id: "$cust_id", total: { $sum: "$amount"}}}])// Complex aggregation
{ $addFields: {
    maxTime: { $max: "$times" },
    minTime: { $min: "$times" }
} },
{ $project: {
    _id: 1, name: 1, times: 1, maxTime: 1, minTime: 1,
    avgTime: { $avg: ["$maxTime"."$minTime"] }
} },

{ $match: {

    name: "Joe Schmoe",

    maxTime: { $lt: 20 },

    minTime: { $gt: 5 },

    avgTime: { $gt: 7 }

} }

{ $match: { name: "Joe Schmoe" } },

{ $addFields: {
    maxTime: { $max: "$times" },
    minTime: { $min: "$times" }
} },

{ $match: { maxTime: { $lt: 20 }, minTime: { $gt: 5 } } },

{ $project: {
    _id: 1, name: 1, times: 1, maxTime: 1, minTime: 1,
    avgTime: { $avg: ["$maxTime"."$minTime"] }
} },

{ $match: { avgTime: { $gt: 7}}}Copy the code