MongoDBのクエリの備忘録 (Array)

mongodb

準備

使用するMongoDBはこちらと同じ。
MongoDB Shellで接続して、マニュアルと同じテストデータを挿入する。

test> 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 ] }
... ]);
{
  acknowledged: true,
  insertedIds: {
    '0': ObjectId("634137309de97ee89b8130a8"),
    '1': ObjectId("634137309de97ee89b8130a9"),
    '2': ObjectId("634137309de97ee89b8130aa"),
    '3': ObjectId("634137309de97ee89b8130ab"),
    '4': ObjectId("634137309de97ee89b8130ac")
  }
}

Array Query Operators

all

指定した全ての要素を含むarrayを取得
redとblankを含むtags

test> db.inventory.find( { tags: { $all: ["red", "blank"] } } )
[
  {
    _id: ObjectId("634137309de97ee89b8130a8"),
    item: 'journal',
    qty: 25,
    tags: [ 'blank', 'red' ],
    dim_cm: [ 14, 21 ]
  },
  {
    _id: ObjectId("634137309de97ee89b8130a9"),
    item: 'notebook',
    qty: 50,
    tags: [ 'red', 'blank' ],
    dim_cm: [ 14, 21 ]
  },
  {
    _id: ObjectId("634137309de97ee89b8130aa"),
    item: 'paper',
    qty: 100,
    tags: [ 'red', 'blank', 'plain' ],
    dim_cm: [ 14, 21 ]
  },
  {
    _id: ObjectId("634137309de97ee89b8130ab"),
    item: 'planner',
    qty: 75,
    tags: [ 'blank', 'red' ],
    dim_cm: [ 22.85, 30 ]
  }
]

このクエリと同じ

test> db.inventory.find( { $and: [ { tags: "red" }, { tags: "blank" } ] } )
[
  {
    _id: ObjectId("634137309de97ee89b8130a8"),
    item: 'journal',
    qty: 25,
    tags: [ 'blank', 'red' ],
    dim_cm: [ 14, 21 ]
  },
  {
    _id: ObjectId("634137309de97ee89b8130a9"),
    item: 'notebook',
    qty: 50,
    tags: [ 'red', 'blank' ],
    dim_cm: [ 14, 21 ]
  },
  {
    _id: ObjectId("634137309de97ee89b8130aa"),
    item: 'paper',
    qty: 100,
    tags: [ 'red', 'blank', 'plain' ],
    dim_cm: [ 14, 21 ]
  },
  {
    _id: ObjectId("634137309de97ee89b8130ab"),
    item: 'planner',
    qty: 75,
    tags: [ 'blank', 'red' ],
    dim_cm: [ 22.85, 30 ]
  }
]

これらのクエリは完全一致

test> db.inventory.find( { tags: { $all: [["red", "blank"]] } } )
[
  {
    _id: ObjectId("634137309de97ee89b8130a9"),
    item: 'notebook',
    qty: 50,
    tags: [ 'red', 'blank' ],
    dim_cm: [ 14, 21 ]
  }
]

test> db.inventory.find( { $and: [ { tags: ["red", "blank"] } ] } )
[
  {
    _id: ObjectId("634137309de97ee89b8130a9"),
    item: 'notebook',
    qty: 50,
    tags: [ 'red', 'blank' ],
    dim_cm: [ 14, 21 ]
  }
]

test> db.inventory.find( { tags: ["red", "blank"] } )
[
  {
    _id: ObjectId("634137309de97ee89b8130a9"),
    item: 'notebook',
    qty: 50,
    tags: [ 'red', 'blank' ],
    dim_cm: [ 14, 21 ]
  }
]

elemMatch

指定した全てのクエリに一致する要素を少なくとも1つは含んでいるarrayを取得
dim_cmでvalue > 15 and value < 20 は15.25のみ

test> db.inventory.find( { dim_cm: { $elemMatch: { $gt: 15, $lt: 20 } } } )
[
  {
    _id: ObjectId("634137309de97ee89b8130ac"),
    item: 'postcard',
    qty: 45,
    tags: [ 'blue' ],
    dim_cm: [ 10, 15.25 ]
  }
]

これは、arrayのいずれかの要素が指定したクエリのいずれかに一致する場合
dim_cm: [ 22.85, 30 ] のみ20未満の要素がないので該当しない

test> db.inventory.find( { dim_cm: { $gt: 15, $lt: 20 } } )
[
  {
    _id: ObjectId("634137309de97ee89b8130a8"),
    item: 'journal',
    qty: 25,
    tags: [ 'blank', 'red' ],
    dim_cm: [ 14, 21 ]
  },
  {
    _id: ObjectId("634137309de97ee89b8130a9"),
    item: 'notebook',
    qty: 50,
    tags: [ 'red', 'blank' ],
    dim_cm: [ 14, 21 ]
  },
  {
    _id: ObjectId("634137309de97ee89b8130aa"),
    item: 'paper',
    qty: 100,
    tags: [ 'red', 'blank', 'plain' ],
    dim_cm: [ 14, 21 ]
  },
  {
    _id: ObjectId("634137309de97ee89b8130ac"),
    item: 'postcard',
    qty: 45,
    tags: [ 'blue' ],
    dim_cm: [ 10, 15.25 ]
  }
]

size

指定したサイズのarrayを取得

test> db.inventory.find( { tags: { $size: 1 } } )
[
  {
    _id: ObjectId("634137309de97ee89b8130ac"),
    item: 'postcard',
    qty: 45,
    tags: [ 'blue' ],
    dim_cm: [ 10, 15.25 ]
  }
]

test> db.inventory.find({ tags: { $size: 2 } } )
[
  {
    _id: ObjectId("634137309de97ee89b8130a8"),
    item: 'journal',
    qty: 25,
    tags: [ 'blank', 'red' ],
    dim_cm: [ 14, 21 ]
  },
  {
    _id: ObjectId("634137309de97ee89b8130a9"),
    item: 'notebook',
    qty: 50,
    tags: [ 'red', 'blank' ],
    dim_cm: [ 14, 21 ]
  },
  {
    _id: ObjectId("634137309de97ee89b8130ab"),
    item: 'planner',
    qty: 75,
    tags: [ 'blank', 'red' ],
    dim_cm: [ 22.85, 30 ]
  }
]

test> db.inventory.find({ tags: { $size: 3 } } )
[
  {
    _id: ObjectId("634137309de97ee89b8130aa"),
    item: 'paper',
    qty: 100,
    tags: [ 'red', 'blank', 'plain' ],
    dim_cm: [ 14, 21 ]
  }
]

Array Index Position

クエリの条件にarrayのindexを指定する場合

test> db.inventory.find( { "dim_cm.0": { $gt: 15 } } )
[
  {
    _id: ObjectId("634137309de97ee89b8130ab"),
    item: 'planner',
    qty: 75,
    tags: [ 'blank', 'red' ],
    dim_cm: [ 22.85, 30 ]
  }
]

test> db.inventory.find( { "dim_cm.1": { $lt: 20 } } )
[
  {
    _id: ObjectId("634137309de97ee89b8130ac"),
    item: 'postcard',
    qty: 45,
    tags: [ 'blue' ],
    dim_cm: [ 10, 15.25 ]
  }
]

参考