$expr arrayElementAt 在嵌入文档的聚合中不起作用 [英] $expr arrayElementAt not working in aggregation for embedded document
问题描述
我正在做 mongo db 聚合之类的
I am doing mongo db aggregation like
$cursor = $this->collection->aggregate(
array(
array(
'$project' => array(
'FullName' => array('$concat' => array('$first_name', ' ', '$middle_name', ' ', '$last_name')),
'FirstMiddle' => array('$concat' => array('$first_name', ' ', '$middle_name')),
'FirstLast' => array('$concat' => array('$first_name', ' ', '$last_name')),
'FirstName' => array('$concat' => array('$first_name')),
'MiddleName' => array('$concat' => array('$middle_name')),
'LastName' => array('$concat' => array('$last_name')),
'Student' => '$$ROOT'
)
),
array(
'$match' =>
array(
'$or' => array(
array("FullName" => new MongoDBBSONRegex($arg, 'i')),
array("FirstLast" => new MongoDBBSONRegex($arg, 'i')),
array("FirstMiddle" => new MongoDBBSONRegex($arg, 'i')),
array("FirstName" => new MongoDBBSONRegex($arg, 'i')),
array("MiddleName" => new MongoDBBSONRegex($arg, 'i')),
array("LastName" => new MongoDBBSONRegex($arg, 'i')),
array("Student.registration_temp_perm_no" => $arg),
'$expr' => array(
'$eq'=> array(
array('$arrayElemAt' => array('$allotment_details.room_id', -1)), $this->RoomId)),
),
// "Student.assigned_keys" => ['$exists' => false],
"Student.schoolId" => new MongoDBBSONObjectID($this->SchoolId)
)
)
)
);
我有一个集合，其中包含像
I have collection which contains data like
"first_name": "John",
"middle_name": "",
"last_name": "Mayor",
"allotment_details": [
{
"allotment_id": "ff666d55-2fcc-79b2-e4da-e165939555bb",
"room_id": "5be2d9aad2ccda0fdc006a65",
"bay_id": ObjectId("5be2d9aad2ccda0fdc006a61"),
...
}
上面的代码适用于三种名称类型的精细连接并搜索 $arg 中传递的所有数据.请注意，我添加了 array('$arrayElemAt' => array('$allotment_details.room_id', -1)), $this->RoomId)) 以便根据名字的串联，这些学生应该基于 $this->RoomId 来获取.
The above code is working for fine concatenation of three names types and searching all the data passed in $arg. Note that I have added array('$arrayElemAt' => array('$allotment_details.room_id', -1)), $this->RoomId)) in order to fetch students based on concatenation of names and those students should be fetched on the basis of $this->RoomId.
上面的代码不是获取分配到房间的学生，而是根据名字的串联获取所有学生.请帮忙！！！
The above code is not fetching students assigned to a room but is fetching all the students based on concatenation of names. Please help !!!
推荐答案
快速修复
您的管道"在这里不起作用主要因为您最初的 $project 缺少您想在以后使用的字段.因此，快速修复" 基本上是将该字段包含在预计"文档中，因为这就是聚合管道阶段的工作方式:
Quick fix
Your "pipeline" does not work here primarily because your initial $project lacks the field you want to use an a later stage. The "quick fix" is therefore basically to include that field in the "projected" document, since that's how aggregation pipeline stages work:
array(
array(
'$project' => array(
'FullName' => array('$concat' => array('$first_name', ' ', '$middle_name', ' ', '$last_name')),
'FirstMiddle' => array('$concat' => array('$first_name', ' ', '$middle_name')),
'FirstLast' => array('$concat' => array('$first_name', ' ', '$last_name')),
'FirstName' => array('$concat' => array('$first_name')),
'MiddleName' => array('$concat' => array('$middle_name')),
'LastName' => array('$concat' => array('$last_name')),
'Student' => '$$ROOT',
'allotment_details' => 1 # that's the change
)
),
或者即使您使用 $$ROOT 表示 Student，只需限定该路径下的字段:
Or even since you used $$ROOT for Student anyway, simply qualify the field under that path:
'$expr' => array(
'$eq'=> array(
array('$arrayElemAt' => array('$Student.allotment_details.room_id', -1)),
$this->RoomId
)
),
但是我会强烈*恳求您不要那样做.
连接字符串"的整个概念以便以后做 内容上的 $match 是一个非常糟糕的主意，因为这意味着在实际完成任何过滤"之前，整个集合在管道中被重写.
The whole concept of "concatenating strings" in order to do a later $match on the content is a really bad idea since it means the whole collection gets rewritten in the pipeline before any "filtering" actually gets done.
同样寻找最后一个"数组元素的匹配也是一个问题.一个更好的方法是实际上将新项目"添加到数组的开始"，而不是结束".这实际上是 $position 甚至可能是 $push 为你做，通过分别更改添加项目的位置或项目的排序顺序.
Likewise looking to match on the "last" array element is also an issue. A far better approach is to instead actually add "new items" to the "beginning" of the array, instead of the "end". This is actually what the $position or possibly even the $sort modifiers to $push do for you, by changing where items get added or the sorted order of items respectively.
这需要通过更改存储方式来做一些工作，但好处是大大提高了此类查询的速度，而无需评估 $expr 参数.
This takes a little work by changing the way you store things, but the benefits are greatly improved speed of such queries like you want without needing an evaluated $expr argument.
核心概念是使用如下语法预置"新数组项:
The core concepts are to "pre-pend" new array items with syntax like:
$this->collection->updateOne(
$query,
[ '$push' => [ 'allotment_details' => [ '$each' => $allotments, '$position' => 0 ] ] ]
)
其中 $alloments 必须 是 $each 和 $position 用于 0 以便添加新的数组项first".
Where $alloments must be an array as required by $each and $position is used to 0 in order to add the new array item "first".
或者，如果您实际上在数组中的每个对象中都有类似 created_date 的属性，那么您可以"使用类似 $sort 改为修饰符.
Alternately if you actually have something like created_date as a property within each of the objects in the array, then you "could" use something like $sort as a modifier instead.
$this->collection->updateOne(
$query,
[ '$push' => [
'allotment_details' => [ '$each' => $allotments, '$sort' => [ 'created_date' => -1 ] ]
]]
)
这实际上取决于您的查询"和其他访问要求是否依赖于最后添加"或最新日期"，然后通常还取决于您是否打算更改这样的 created_date 或其他排序"属性的方式会影响排序"时数组元素的顺序.
It really depends on whether your "query" and other access requirements rely on "last added" or "latest date", and then also typically if you intend to possibly alter such a created_date or other "sort" property in a way which would effect the order of the array elements when "sorted".
您这样做的原因是匹配数组中的最新"(现在是第一个")项目:
The reason you do this is then matching the "latest" ( which is now the "first" ) item in the array simply becomes:
$this->collection->find([
'allotment_details.0.room_id': $this->RoomId
])
MongoDB 允许使用 "Dot 指定第一个"数组索引Notation"，使用 0 索引.你不能做的是指定一个负"索引，即:
MongoDB allows the "first" array index to be specified with "Dot Notation", using the 0 index. What you cannot do is specify a "negative" index i.e:
$this->collection->find([
'allotment_details.-1.room_id': $this->RoomId # not allowed :(
])
这就是为什么您在更新"时执行上面显示的操作以便将您的数组重新排序"为可行的形式.
That's the reason why you do the things shown above on "update" in order to "re-order" your array to the workable form.
另一个主要问题是字符串的连接.正如已经提到的，这会产生不必要的开销，只是为了进行您想要的匹配.这也是不必要的"，因为您可以使用 $or 来完全避免这种情况，因为它们已经存在于实际文档中:
The other main issue is the concatenation of strings. As already mentioned this creates unnecessary overhead just in order to do the matching you want. It's also "unnecessary" since you can complete avoid this using $or with the conditions on each of the fields as they exist already within the actual document:
$this->collection->find([
'$or' => [
[ 'first_name' => new MongoDBBSONRegex($arg, 'i') ],
[ 'last_name' => new MongoDBBSONRegex($arg, 'i') ],
[ 'middle_name' => new MongoDBBSONRegex($arg, 'i') ],
[ 'registration_temp_perm_no' => $arg ]
],
'schoolId' => new MongoDBBSONObjectID($this->SchoolId),
'allotment_details.0.room_id': $this->RoomId
])
当然，无论实际需要什么完整"查询条件，但您应该了解基本概念.
And of course whatever the "full" query conditions actually need to be, but you should be getting the basic idea.
此外，如果您实际上不是在寻找部分词"，那么 " 文本搜索" 在具有名称"的字段上定义.创建索引后:
Also if you are not actually looking for "partial words", then a "text search" defined over the fields with the "names". After creating the index that would be:
$this->collection->find([
'$text' => [ '$search' => $arg ],
'schoolId' => new MongoDBBSONObjectID($this->SchoolId),
'allotment_details.0.room_id': $this->RoomId
])
<小时>
总的来说，我真的建议您仔细查看所有其他选项，而不是对现有代码进行一点小改动.通过稍微仔细地重新构建您存储事物的方式以及索引"事物的方式，您可以获得巨大的性能优势，而您的广泛的 $concat 蛮力"方法根本无法提供.
Overall I would really recommend looking closely at all the other options rather than making one small change to your existing code. With a little careful re-structuring of how you store things and indeed "index" things, you get huge performance benefits that your extensive $concat "brute force" approach simply cannot deliver.
注意 现代 PHP 版本通常支持 [] 作为 array() 的更简短的表示.它更干净，更容易阅读.所以请使用它.
N.B Modern PHP Releases generally support
[]as a much more brief representation ofarray(). It's a lot cleaner and far easier to read. So please use it.
这篇关于$expr arrayElementAt 在嵌入文档的聚合中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
