MongoDb select all record and order them on an array basis

Question

In mongoDb I am trying to achieve the result mentioned in "$queryResult" with aggregate, I want to select all records form a collection and order them on an array, Can you please help in it.

Record in Db

$recordsInDb = [
    ["coreId" => 10, "name" => 'a'],
    ["coreId" => 20, "name" =>'a'],
    ["coreId" => 30,"name" => 'a']
];

order for query

$order = [20,10];

Desired Results

$queryResult = [
    {coreId:20,name:'a'}
    {coreId:10,name:'a'},
    {coreId:30,name:'a'}
]

Answer 1

Can't think of a much better approach than to create an additional array field with each document in the collection that has the ordered list. Unwind that field and use the includeArrayIndex property of the $unwind operator to generate the index positions. Use that info to then sort the documents, with an additional tenary operator $cond that evaluates the logical expression array element === coreId , and depending on the result, returns the sort index if true, otherwise return a constant n > order.length.

The following shows the approach described above, though there is plenty room for improvement but at least that should give you some direction. Of course it will be up to you to convert the pipeline to the appropriate driver language (which I assume is PHP):

var order = [20, 10];
db.records.aggregate([
    { 
        "$project": {
            "coreId" : 1,
            "name" : 1,
            "sortOrder": { "$literal": order } // create an additional field
        }
    },
    { 
        "$unwind": {
            // flatten the above array
            "path": "$sortOrder", 
            // create the index position for each array element
            "includeArrayIndex": "sortIndex", 
        }
    },
    {
        "$project": {
            "coreId": 1,
            "name": 1,
            "sortIndex": {
                 "$cond": [
                    { "$eq": [ "$coreId", "$sortOrder" ] }, 
                    "$sortIndex", 999999 
                 ]
            }
        }
    },
    { "$sort": { "sortIndex": 1 } },
    {
        "$group": {
            "_id": "$coreId",
            "name": { "$first": "$name" },
            "index": { "$first": "$sortIndex" }
        }
    },
    { "$sort": { "index": 1 } },
    { 
        "$project": {
            "_id": 0,
            "coreId" : "$_id",
            "name" : 1
        }
    }
])

Sample Result

/* 1 */
{
    "name" : "a",
    "coreId" : 20
}

/* 2 */
{
    "name" : "a",
    "coreId" : 10
}

/* 3 */
{
    "name" : "a",
    "coreId" : 30
}