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
– Dheemanth Bhat, Commented Feb 24, 2021 at 19:29
@DheemanthBhat no, the numbers don't mean anything here, just sample key names for the example — user2402616
– user2402616, Commented Feb 24, 2021 at 20:06

Dheemanth Bhat · Accepted Answer · 2021-02-27 10:12:31Z

1

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"]
}

edited Feb 27, 2021 at 10:12

answered Feb 24, 2021 at 20:54

Dheemanth Bhat

4,5022 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2402616 Over a year ago

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 Over a year ago

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

Dheemanth Bhat Over a year ago

Check if this helps: 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 Over a year ago

"MongoDB amazes me every day! and SO helps in doing this!" :) Check the updated answer.

user2402616 Over a year ago

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

|

Collectives™ on Stack Overflow

MongoDB Sort Documents by Values in Array

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related