1. About index
1】Index array
Suppose you have a collection of blog posts, where each document represents an article. Each post has a "comments" field, which is an array where each element is a subdocument of a comment. If you want to find the most recent blog post with the most comments, you can index on the "date" key of the nested "comments" array in the blog post collection:
db.blog.createIndex({"comments.date": 1}})Creating an index into an array actually creates an index entry for each element of the array, so if an article has 20 comments, then it has 20 index entries. Therefore, array indexes are more expensive than single-value indexes: for a single insert, update, or delete, every array entry may need to be updated (there may be thousands of index entries)
Unlike index objects, you cannot index an entire array as a single entity: indexing an array actually indexes each element in the array, not the array itself.
Indexing into an array does not contain any positional information: array indexing cannot be used to find an array element at a specific position, for example: "comments.4"
In a few special cases, a specific array entry can be indexed, such as:
db.blog.createIndex({"comments.10.votes": 1}})However, this index is only useful if it matches exactly the 11th array element
There can be at most one array field in an index. This is to avoid an explosion of index entries in a multikey index: every possible pair of elements has to be indexed, resulting in "n*m" index entries per document. Suppose there is an index on {"x":1, "Y":1}:
//x是一个数组————这是合法的
db.multi.insert({"x" : [1,2,3], "y" : 1})
//y是一个数组————这是合法的
db.multi.insert({"x" : 1, "y" : [1,2,3]})
//x和y都是数组————这是非法的！
db.multi.insert({"x" : [1,2,3], "y" : [4,5,6]})If MongoDB were to create an index for the last example above, it would have to create this many index entries:
{"x" : 1, "y" : 4}、{"x" : 1, "y" : 5}、{"x" : 1, "y" : 6}
{"x" : 2, "y" : 4}、{"x" : 2, "y" : 5}、{"x" : 2, "y" : 6}
{"x" : 3, "y" : 4}、{"x" : 3, "y" : 5}、{"x" : 3, "y" : 6}Even though these arrays only have 3 elements.
2】Multi-key index
For a key of an index, if the key is an array in a document, then the index will be marked as a multikey index. You can see whether an index is a multikey index from the output of explain(): If a multikey index is used, the value of the "isMultikey" field is true. As long as an index is marked as a multikey index, it can no longer become a non-multikey index, even if all documents with this field as an array are deleted from the collection. The only way to restore a multikey index to a non-multikey index is to drop and re-create the index.
Multikey indexes may be slower than non-multikey indexes. There may be multiple index entries pointing to the same document, so MongoDB must first remove duplicate content when returning the result set.
2. Operations on single-value arrays
1】Query
1. Operator: $all
If you need to match an array by multiple elements, use "$all". This will match a set of elements. For example, suppose you create a collection with 3 elements
db.food.insert({"_id" : 1, "fruit": ["apple", "banana", "peach"]})
db.food.insert({"_id" : 2, "fruit": ["apple", "kumquat", "orange"]})
db.food.insert({"_id" : 3, "fruit": ["cherry", "banana", "apple"]})To find documents that contain both "apple" and "banana", you can use "$all" to query:
db.food.find({"fruit": {"$all": ["apple", "banana"]}})
---查询结果
db.food.insert({"_id" : 1, "fruit": ["apple", "banana", "peach"]})
db.food.insert({"_id" : 3, "fruit": ["cherry", "banana", "apple"]})The order here doesn't matter. Note that "banana" comes before "apple" in the second result. Using "$all" on an array with only one element is the same as not using "$all".
It is also possible to use the entire array for an exact match. However, exact matching does not work well for missing elements or redundant elements. For example, the following method will match the first document before:
db.food.find({"fruit": ["apple", "banana", "peach"]})But the following won't match:
db.food.find({"fruit": ["apple", "banana"]})This one won't match either:
db.food.find({"fruit": ["banana", "apple", "peach"]})If you want to query the element at a specific position in the array, you need to use the key.index syntax to specify the index:
db.food.find({"fruit.2": "peach"]})2. Operator: $size
"$size" is also very useful for querying arrays. As the name suggests, you can use it to query arrays of a specific length. For example:
db.food.find({"fruit": {"$size": 1}})Getting documents within a range of lengths is a common query. "$size" cannot be combined with other query conditions (such as $lte), but such queries can be implemented by adding a "size" key to the document. In this way, each time an element is added to the specified array, the value of "size" is also increased. For example, this original update:
db.food.update(criteria, {"$push": {"$fruit": "strawberry"}})It will become like this:
db.food.update(criteria, {"$push": {"$fruit": "strawberry"}, "$inc": {"size": 1}})The auto-increment operation is very fast, so the impact on performance is minimal. After the document is stored in this way, it can be queried as follows:
db.food.find({"size": {"$gt": 3}})Unfortunately, this technique cannot be used with the "$addToSet" operator.
3. Operator: $slice
The "$slice" operator returns a subset of array elements that match a key.
For example, suppose we have a blog post document and we want to return the first 10 comments. We can do this:
db.blog.posts.findOne(criteria, {"comments": {"$slice": 10}})You can also return the last 10 comments, just use -10 in the query conditions.
db.blog.posts.findOne(criteria, {"comments": {"$slice": -10}})"$slice" can also specify an offset value and the number of elements you want to return, to return some result at the middle position of the element collection:
db.blog.posts.findOne(criteria, {"comments": {"$slice": [23, 10]}})This operation will skip the first 23 elements and return the 24th to 33rd elements. If the array does not have 33 elements, all elements after 23 elements are returned.
2】New
If the array already exists, "$push" will add an element to the end of the existing array, if not, create a new array. for example:
db.food.update(criteria, {"$push": {"$fruit": "strawberry"}})This relatively simple form of "$push" can also be applied to some more complex array operations. Using the "$each" sub-operator, multiple values can be added with a single "$push" operation.
db.food.update({"_id" : 3}, {"$push": {"fruit" : {"$each" : ["apricot", "grape"]}}})If you want the maximum length of the array to be fixed, you can use "$slice" and "$push" together to ensure that the array will not exceed the set maximum length. This actually results in an array containing at most N elements:
db.food.update({"_id" : 3}, {"$push": {"fruit" : {"$each" : ["apricot", "grape"], "$slice": -10} } })This example will limit the array to contain only the last 10 elements added. The value of "$slice" must be a negative integer.
If the number of elements in the array is less than 10 (after "$push"), then all elements will be retained. If the number of elements in the array is greater than 10, then only the last 10 elements will be retained. Therefore, "$slice" can be used to create a queue in the document.
Finally, you can use "$sort" before cleaning elements that require cleaning whenever adding sub-objects to the array, for example:
db.movies.update({"genre" : "horror"}, {
"$push": {
"top10" : {
"$each": [
{"name": "Nightmare on Elm street", "rating": 5.5},
{"name": "Saw", "rating": 4.3}
],
"$slice": -10,
"$sort": {"rating": -1}
}
}
})This will sort all the objects in the array based on the value of the "rating" field and keep the top 10. Note that "$slice" or "$sort" cannot be used with "$push", and "$each" must be used.
If you use an array as a data set to ensure that the elements in the array are not repeated, you can use "$addToSet" to achieve this, such as:
db.food.update({"_id" : 3}, {"$addToSet": {"fruit" : "apricot"}})Combining "$addToSet" and "$each" can add multiple different values, for example:
db.food.update({"_id" : 3}, { "$addToSet": { "fruit" : { "$each" : ["apricot", "grape"] } } })3】Modify
If the array has multiple values and we only want to operate on part of them, some tricks are needed. There are two ways to manipulate values in an array, through positional or positioning operators ("#34;)
Modify the array by subscripting, such as:
db.blog.update({"post" : "postId_1001"}, { "$inc": {"comments.0.votes": 1} })But in many cases, the index of the array to be modified cannot be known without querying the document in advance. In order to overcome this difficulty, MongoDB provides the positioning operator "#34;", which is used to locate the array elements that have been matched by the query document and update them. For example:
db.blog.update({"comments.author" : "John"}, { "$set": {"comments.$.author": "Jim"} })4】Delete
There are several ways to remove elements from an array. If you view the array as a queue or stack, you can use "$pop". This modifier can delete elements from either end of the array. {"$pop": {"key": 1}} deletes an element from the end of the array, {"$pop": {"key": -1}} deletes it from the head.
Sometimes you need to delete elements based on specific conditions, not just based on the element's position. In this case, you can use "$pull". For example:
db.food.update({"_id" : 3}, {"$pull": {"fruit" : "apricot"}})"$pull" will delete all matching documents, not just one. pair array
1 ,1, 2, 1
Executing pull 1 results in an array with only one element.
3. Operations on embedded document arrays
demo order data:
{
"total_goods_num" : 3,
"goods" : [
{
"amount" : 3,
"goods_code" : "100009464821",
"price" : { "$numberDecimal" : "9999.00" },
"wait_num" : NumberInt(3),
"goods_id" : "1007"
},
{
"amount" : 4,
"goods_code" : "100016079918",
"price" : { "$numberDecimal" : "5899.59" },
"wait_num" : NumberInt(4),
"goods_id" : "1006"
},
{
"amount" : 5,
"goods_code" : "100008699547",
"price" : { "$numberDecimal" : "4798.37" },
"wait_num" : NumberInt(5),
"goods_id" : "1005"
}
],
"bill_no" : "D20210712174",
"address" : {
"province" : "安徽省",
"city" : "",
"area" : "",
"detail" : ""
},
"bill_status" : "1",
"pay_time" : ISODate("2022-10-05T03:19:34.506+0000"),
"total_money" : { "$numberDecimal" : "15496.33000" },
}1】Query
1. Directly use nested field query
For such matching results, as long as there is data containing the query conditions in the data group, the document data will be returned.
db.getCollection("bill").find({ 'goods.goods_code': "100009464821" })2. Only return matching nested array data
2.1: You can use the $elemMatch operator (supported from mongodb 2.2)
2.2: You can use the $ operator (supported from mongodb 2.2)
---- 使用$elemMatch操作符
db.bill.find(
{
'goods.goods_code': "100009464821"
},
{
"goods": {$elemMatch:{"goods_code":"100009464821"}}
}
)
---- 使用$操作符
db.bill.find({'goods.goods_code': "100009464821"}, {_id: 0, 'goods.#39;: 1});The above two operators have this official description: the $elemMatch projection returns only the first matching element from the array. That is, only the first matched data element will be returned. (Operator description:
https://docs.mongodb.com/manual/reference/operator/projection/elemMatch/#proj._S_elemMatch)
3. Use $filter to filter the array:
3.1: Only filter the main object field in $match
3.2: Put the sub-object filter conditions in $project and filter through "$filter"
3.3: If there is a query with an array field in $match, when the array does not have data that meets the conditions, the result set will not return any document information. When the array filter is placed in $project and processed by $filter, the document can be returned, and the array key that does not meet the conditions is marked with an empty array.
db.erp_sales_order.aggregate([
{
"$match": {
"$and": [
{
"order_id": "orde202209141279id"
}
]
}
},
{
"$project": {
"perpetual_order": 1,
"contract_code": 1,
"erp_sales_order_product": {
"$filter": {
"input": "$erp_sales_order_product",
"as": "erp_sales_order_product",
"cond": {
"$or": [
{
"$eq": [
"$erp_sales_order_product.product_detail_id",
"espdi20220914331"
]
}
]
}
}
}
}
}
])Compared with $elemMatch and $, $filter will return all matched array data. Furthermore, document data that does not meet the criteria are excluded. In addition, if there are no matching elements in the array, an empty array will be returned, retaining the returned keys, while $elemMatch and $ will not return keys. For example, the results of the above query may be as follows:
{
"_id" : ObjectId("63218dcd768a5a421db1bfcd"),
"perpetual_order" : "0",
"contract_code" : "SCON2022081454",
"erp_sales_order_product" : []
}
erp_sales_order_product 没有符合条件的数据，仍然返回键2】Modify
1. Use $ to update the array
1.1: The $ operator will only match the first modified document
db.bill.update(
{
"bill_no" : "D20210623173",
"goods.amount": {'$gte':4 , '$lte':5}
},
{
"$set": {"goods.$.wait_num": 56}
}
)According to the query of the above statement, there are 2 pieces of data that meet the conditions of "goods.amount", but after execution, only the first document data will be updated (the document of "goods_id" : "1006")
2. Use arrayFilters to update the array
2.1: Set arrayFilters to modify one or more elements in a nested array
2.2: The "$" in 'goods.$.wait_num', where "tt" is equivalent to giving an alias to the element, can be any string
2.3: Based on 2.2, the filtering operation in arrayFilters must match the alias string, such as: 'tt.amount', otherwise the modification will fail.
db.bill.updateMany(
{
"bill_no" : "D20210623173"
},
{
'$set': {
'goods.$[tt].wait_num': 36
}
},
{
arrayFilters: [
{'tt.amount': {'$gte':4 , '$lte':5}}
]
}
)Suppose the above execution statement is adjusted as follows:
db.bill.updateMany(
{
"bill_no" : "D20210623173"
},
{
'$set': {
'goods.$[tt].wait_num': 6
}
},
{
arrayFilters: [
{'elem.amount': {'$gte':4 , '$lte':5}}
]
}
)After execution, there will be an error similar to the following:
2022-10-05T11:46:26.597+0800 E QUERY [js] WriteError: No array filter found for identifier 'tt' in path 'goods.$[tt].wait_num' :
WriteError({
"index" : 0,
"code" : 2,
"errmsg" : "No array filter found for identifier 'tt' in path 'goods.$[tt].wait_num'",
"op" : {
"q" : {
"bill_no" : "D20210623173"
},
"u" : {
"$set" : {
"goods.$[tt].wait_num" : 6
}
},
"multi" : true,
"upsert" : false,
"arrayFilters" : [
{
"elem.amount" : {
"$gte" : 4,
"$lte" : 5
}
}
]
}
})3】Delete
3.1: Using $pull operator
db.bill.update(
{
"bill_no" : "D20210623173"
},
{
"$pull": {"goods": { "amount": {'$gte':4 , '$lte':5} }}
}
)
