MongoDB Sort Documents by Values in Array

Question

I have a collection that contains an optional Array. This Array can then contain Strings (but will be an enum essentially).

i.e.

123: {
   foo: 'bar',
   arr: ['fizz','buzz']
},
456: {
   foo:'bar',
},
789: {
   foo: 'bar',
   arr: ['fizz']
},
000: {
    foo:'bar',
    arr:['buzz']
}

I'd like to sort these doce by any value in arr. I.e. If I want to sort by possible 'buzz' values in any existing arr Then, I'd expect to see 123 & 000 at the top.

Is this possible? I tried {sort: {arr:{'buzz':1}}} but that does not work. Any ideas? Thanks

You have numbers like `123` as field names so are they dynamic? — Dheemanth Bhat, Feb 24 '21 at 19:29
@DheemanthBhat no, the numbers don't mean anything here, just sample key names for the example — user2402616, Feb 24 '21 at 20:06

Dheemanth Bhat · Accepted Answer · 2021-02-27T10:12:31.203

UPDATE based on comments

Query:

let input = "buzz";

db.collectionName.find(
    {
        $or: [
            { $text: { $search: input } },
            { _id: { $exists: true } }
        ]
    },
    {
        "foo": 1,
        score: { $meta: "textScore" }
    }
).sort({ 
    score: { $meta: "textScore" }
});

IMPORTANT: You need to create text index as shown below:

db.collectionName.insertMany([
    /* 1 createdAt:2/25/2021, 2:14:03 AM*/
    {
        "_id": ObjectId("6036ba93e715d911e89e062b"),
        "foo": "bar4",
        "arr": ["buzz"]
    },

    /* 2 createdAt:2/25/2021, 2:14:03 AM*/
    {
        "_id": ObjectId("6036ba93e715d911e89e062a"),
        "foo": "bar3",
        "arr": ["fizz"]
    },

    /* 3 createdAt:2/25/2021, 2:14:03 AM*/
    {
        "_id": ObjectId("6036ba93e715d911e89e0629"),
        "foo": "bar2"
    },

    /* 4 createdAt:2/25/2021, 2:14:03 AM*/
    {
        "_id": ObjectId("6036ba93e715d911e89e0628"),
        "foo": "bar1",
        "arr": ["fizz", "buzz"]
    }
]);

db.collectionName.createIndex({ arr: "text" });

Output:

/* 1 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e0628"),
    "foo" : "bar1",
    "score" : 1.1
},

/* 2 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e062b"),
    "foo" : "bar4",
    "score" : 1.1
},

/* 3 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e0629"),
    "foo" : "bar2"
},

/* 4 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e062a"),
    "foo" : "bar3"
}

Refer this link for some insights on textScore.

OLD ANSWER

Try this:

let input = "buzz";

db.collectionName.aggregate([
    {
        $project: {
            "foo": 1,
            "sortArray": {
                $size: {
                    $ifNull: [
                        {
                            $filter: {
                                input: "$arr",
                                as: "item",
                                cond: { $in: [input, "$arr"] }
                            }
                        },
                        []
                    ]
                }
            }
        }
    },
    {
        $sort: { sortArray: -1 }
    }
]);

Output:

/* 1 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e0628"),
    "foo" : "bar1",
    "sortArray" : 2
},

/* 2 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e062b"),
    "foo" : "bar4",
    "sortArray" : 1
},

/* 3 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e0629"),
    "foo" : "bar2",
    "sortArray" : 0
},

/* 4 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e062a"),
    "foo" : "bar3",
    "sortArray" : 0
}

My test data looks like this:

/* 1 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e062b"),
    "foo" : "bar4",
    "arr" : ["buzz"]
},

/* 2 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e062a"),
    "foo" : "bar3",
    "arr" : ["fizz"]
},

/* 3 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e0629"),
    "foo" : "bar2"
},

/* 4 createdAt:2/25/2021, 2:14:03 AM*/
{
    "_id" : ObjectId("6036ba93e715d911e89e0628"),
    "foo" : "bar1",
    "arr" : ["fizz", "buzz"]
}

I love your solution, it's very clever. I adopted the same approach of introducing another variable (your `sortByArray`) - but in straight JS in post-processing upon retrieving from Mongo. This is because I cannot use Aggregate in the framework I'm using (Meteor). It's because I need to retain reactivity. Do you know of any way to solve this without an aggregate? Thanks! — user2402616, Feb 26 '21 at 16:24
By straight JS I meant just plain old javascript. I.e. I retrieve results of all Objects in one regular Mongo `find` - then post-process in JS — user2402616, Feb 26 '21 at 16:35
Check if this helps: https://www.w3schools.com/code/tryit.asp?filename=GO2G6SCU67A6 .So once u get the documents from database, use that logic inside node.js. if in case u are not able to access let me know — Dheemanth Bhat, Feb 26 '21 at 17:24
Thanks, I had something similar to what you did in that link. I was just wondering if there was any way to achieve the sort without JS and without a Mongo Aggregate. So - just using a regular Mongo `find` or something similar — user2402616, Feb 26 '21 at 18:02
"MongoDB amazes me every day! and SO helps in doing this!" :) Check the updated answer. — Dheemanth Bhat, Feb 27 '21 at 10:09
Ah, sorting by score! Very neat. I'll have to consider that. Don't know if I'm going to add an index just yet — user2402616, Mar 01 '21 at 14:19

MongoDB Sort Documents by Values in Array

1 Answers1