在Mongo中进行动态粘性排序以获取简单的值或列表 [英] Dynamic Sticky Sorting in Mongo for a simple value or list

问题描述

我试图动态地对记录的集合进行粘性排序，每个查询的粘性值都不相同.让我举个例子吧.以下是一些示例文档:

I'm trying to dynamically sticky sort a collection of records with the value that is sticky being different with each query. Let me give an example. Here are some example docs:

{first_name: 'Joe', last_name: 'Blow', offices: ['GA', 'FL']}
{first_name: 'Joe', last_name: 'Johnson', offices: ['FL']}
{first_name: 'Daniel', last_name: 'Aiken', offices: ['TN', 'SC']}
{first_name: 'Daniel', last_name: 'Madison', offices: ['SC', 'GA']}
... a bunch more names ...

现在假设我想按姓氏的字母顺序显示名称，但我想将所有姓氏为"Joe"的记录固定在顶部.

Now suppose I want to display the names in alphabetical order by last name but I want to peg all the records with the first name "Joe" at the top.

在SQL中，这很简单:

In SQL this is fairly straight forward:

SELECT * FROM people ORDER first_name == 'Joe' DESC, last_name

将表达式置于排序条件中的能力使其变得微不足道.使用聚合框架，我可以做到这一点:

The ability to put expressions in the sort criteria makes this trivial. Using the aggregation framework I can do this:

[
  {$project: {
    first_name: 1,
    last_name: 1
    offices: 1,
    sticky: {$cond: [{$eq: ['$first_name', 'Joe']}, 1, 0]}
  }},
  {$sort: [
    'sticky': -1,
    'last_name': 1
  ]}
]

基本上，我使用聚集框架创建一个动态字段，如果名称为Joe，则为1，如果名称不是Joe，则为0，然后以相反的顺序排序.当然，在建立聚合管道时，我可以轻松地将"Joe"更改为"Daniel"，现在将"Daniel"固定在顶部.这就是我所说的动态粘性排序的部分意思.我按粘性排序的值将更改逐个查询

Basically I create a dynamic field with the aggregation framework that is 1 if the name if Joe and 0 if the name is not Joe then sort in reverse order. Of course when building my aggregation pipeline I can easily change 'Joe' to be 'Daniel' and now 'Daniel' will be pegged to the top. This is partially what I mean by dynamic sticky sorting. The value I am sticky sorting by will change query-by-query

现在，它非常适合基本值(例如字符串).当我尝试对包含数组的值进行相同操作时，问题就来了.假设我想钉住"FL"办公室中的所有用户.有了Mongo对数组的天生理解，我想我可以做同样的事情.所以:

Now this works great for a basic value like a string. The problem comes when I try to the same thing for a value that hold an array. Say I want to peg all users in 'FL' offices. With Mongo's native understanding of arrays I would think I can do the same thing. So:

[
  {$project: {
    first_name: 1,
    last_name: 1
    offices: 1,
    sticky: {$cond: [{$eq: ['$offices', 'FL']}, 1, 0]}
  }},
  {$sort: [
    'sticky': -1,
    'last_name': 1
  ]}
]

但这根本不起作用.我确实发现，如果将其更改为以下内容，它将把乔·约翰逊(仅在佛罗里达州的办公室)放在顶部:

But this doesn't work at all. I did figure out that if I changed it to the following it would put Joe Johnson (who is only in the FL office) at the top:

[
  {$project: {
    first_name: 1,
    last_name: 1
    offices: 1,
    sticky: {$cond: [{$eq: ['$offices', ['FL']]}, 1, 0]}
  }},
  {$sort: [
    'sticky': -1,
    'last_name': 1
  ]}
]

但这并没有使Joe Blow排在首位(在FL和GA均在此位置).我相信它正在做简单的比赛.所以我的第一次尝试根本不起作用，因为$ eq返回false，因为我们正在将数组与字符串进行比较.第二次尝试适用于Joe Johnson，因为我们正在比较完全相同的数组.但是Joe Blow从['GA'，'FL']！= ['FL']开始不起作用.另外，如果我想同时将FL和SC固定在顶部，则无法为其提供值['FL'，'SC']进行比较.

But it didn't put Joe Blow at the top (who is in FL and GA). I believe it is doing simple match. So my first attempt doesn't work at all since $eq returns false since we are comparing an array to a string. The second attempt works for Joe Johnson because we are comparing the exact same arrays. But Joe Blow doesn't work since ['GA', 'FL'] != ['FL']. Also if I want to peg both FL and SC at the top I can't give it the value ['FL', 'SC'] to compare against.

接下来，我尝试结合使用$ setUnion和$ size.

Next I try using a combination of $setUnion and $size.

[
  {$project: {
    first_name: 1,
    last_name: 1
    offices: 1,
    sticky: {$size: {$setUnion: ['$offices', ['FL', 'SC']]}}
  }},
  {$sort: [
    'sticky': -1,
    'last_name': 1
  ]}
]

我尝试使用$ let和$ literal的各种组合，但是它总是抱怨我试图将文字数组传递给$ setUnion的参数.具体说:

I've tried using various combinations of $let and $literal but it always complains about me trying to pass a literal array into $setUnion's arguments. Specifically it says:

disallowed field type Array in object expression

有什么办法吗?

推荐答案

无法重现您的错误，但是您的问题中有一些错别字"，所以我不确定您的实际身份.

Cannot reproduce your error but you have a few "typos" in your question so I cannot be sure what you actually have.

但是假设您实际上正在使用MongoDB 2.6或更高版本，则可能需要 $setIntersection 或 $setIsSubset 运算符，而不是 $setUnion .这些运算符表示要与之比较的数组内容匹配"，其中 $setUnion 只是将提供的数组与现有数组组合在一起:

But presuming you actually are working with MongoDB 2.6 or above then you probably want the $setIntersection or $setIsSubset operators rather than $setUnion. Those operators imply "matching" contents of the array they are compared to, where $setUnion just combines the supplied array with the existing one:

db.people.aggregate([
    { "$project": {
        "first_name": 1,
        "last_name": 1,
        "sticky": { 
            "$size": { 
                "$setIntersection": [ "$offices", [ "FL", "SC" ]] 
            }
        },
        "offices": 1
    }},
    { "$sort": {
        "sticky": -1,
        "last_name": 1
    }}
])

在以前的版本中，您没有那些设置运算符您只是使用 $unwind 使用数组，以及相同类型的 $cond 在 $group 将它们重新组合在一起:

In prior versions where you do not have those set operators you are just using $unwind to work with the array, and the same sort of $cond operation as before within a $group to bring it all back together:

db.people.aggregate([
    { "$unwind": "$offices" },
    { "$group": {
        "_id": "$_id",
        "first_name": { "$first": "$first_name" },
        "last_name": { "$first": "$last_name",
        "sticky": { "$sum": { "$cond": [
            { "$or": [
                { "$eq": [ "$offices": "FL" ] },
                { "$eq": [ "$offices": "SC" ] },
            ]},
            1,
            0
        ]}},
        "offices": { "$push": "$offices" }
    }},
    { "$sort": {
        "sticky": -1,
        "last_name": 1
    }}
])

但是您当然是在正确的轨道上.只需选择正确的设置操作或其他方法即可获得您的精确需求.

But you were certainly on the right track. Just choose the right set operation or other method in order to get your precise need.

或者因为您已经发布了获取所需内容的方法，所以编写这种有序匹配"的更好方法是:

Or since you have posted your way of getting what you want, a better way to write that kind of "ordered matching" is this:

db.people.aggregate([
    { "$project": {
        "first_name": 1,
        "last_name": 1,
        "sticky": { "$cond": [
            { "$anyElementTrue": {
                "$map": {
                    "input": "$offices",
                    "as": "o",
                    "in": { "$eq": [ "$$o", "FL" ] }
                }
            }},
            2,
            { "$cond": [
                { "$anyElementTrue": {
                    "$map": {
                        "input": "$offices",
                        "as": "o",
                        "in": { "$eq": [ "$$o", "SC" ] }
                    }
                }},
                1,
                0
            ]}
        ]},
        "offices": 1
    }},
    { "$sort": {
        "sticky": -1,
        "last_name": 1
    }}
])

这将使它的文档具有优先权，其中包含"FL"的办公室"文档优先于"SC"文档，因此优先于所有其他文档，并在单个字段中进行操作.对于人们来说，使用 $unwind 在没有设置运算符的早期版本中.您只需嵌套

And that would give priority it documents with "offices" containing "FL" over "SC" and hence then over all others, and doing the operation within a single field. That should also be very easy for people to see how to abstract that into the form using $unwind in earlier versions without the set operators. Where you simply provide the higher "weight" value to the items you want at the top by nesting the $cond statements.

这篇关于在Mongo中进行动态粘性排序以获取简单的值或列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！