Sorting based on dynamic filed value

Hello everyone,

I’m currently using MongoDB in my application to manage documents with a structure like the one below:

{
_id: ObjectId('123456'),
status: 'in work',
title: 'Document 5',
description: 'Description of a document',
stages: {
    pending: {
       deadline: "30/10/2023",
       title: 'Pending status'
    },
    in work: {
       deadline: "04/11/2023",
       title: 'In work status'
    },
    done: {
       deadline: "10/11/2023",
       title: 'Done status'
    },
 }
}

Each document is in a specific stage, and this stage is reflected in the status field. I need to sort these documents based on their respective stages, specifically by the deadline associated with each stage.

For instance, I would like to retrieve documents sorted by their deadlines within the “in work” stage. This query should also consider various other parameters, such as pageSize, that I want to include in the query.

Is this achievable in MongoDB, and if so, how can I go about it?

Thank you in advance for your assistance!

Your title indicate dynamic field value but your use-case looks like dynamic field name.

So my best recommendation at this time is to change your model to use The Attribute Pattern:

With this stages: would become an array as in:

stages: [
    {  stage: "pending" ,
       deadline: "30/10/2023",
       title: 'Pending status'
    },
    {  stage: "in work" ,
       deadline: "04/11/2023",
       title: 'In work status'
    },
    {  stage: "done" ,
       deadline: "10/11/2023",
       title: 'Done status'
    },
 ]

In the link I provided, they explain why it is a better design.

As for the deadline: field, I strongly suggest that you use the date data type rather the string. Date data type is faster than string and provides a richer API. What is particularly bad about your string dates, is that you use the format “DD/MM/YYYY” which is like worst to use when you want to sort by dates since you would need to extract YYYY, MM and DD before your sort or query by range. It means that could not use an index. The format “YYYY/MM/DD” would be better because for sorting or range query. But it would be slower than date data type because comparison is done character per character.

Thank you for the response @steevej

This approach that you explained and suggested is nice and much more performant, but it still doesn’t solve my problem.

I need to figure out the stage based on the status of the entry. Can this approach solve the issue?

Thank you!

To understand correctly what you want, we would need more sample documents and the expected results.

If you stick with the original document structure, I think the first stage will be to extract the stage date that you want with a $set that uses $switch to get the date.

{ "$set" : {
    "_tmp.deadline" : { "$switch" : {
        "branches" : [
            {
                "case" : { "$eq" : [ "$status" , "pending" ] } ,
                "then" : { "$stages.pending.deadline" }
            } ,
            {
                "case" : { "$eq" : [ "$status" , "in work" ] } ,
                "then" : { "$stages.in work.deadline" }
            } ,
            {
                "case" : { "$eq" : [ "$status" , "done" ] } ,
                "then" : { "$stages.done.deadline" }
            } 
        ]
    } }
} }

Then another $set that convert the deadline into a value that can be sorted

{ "$set" : {
    "_tmp.sortable_deadline" : { "$dateFromString" : {
        "dateString" : "$_tmp.deadline" ,
        "format" : "%d/%m/%Y"
    } }
} }

The you would be able to sort

{ "$sort" : {
    "_tmp.sortable_deadline" : 1
} }

A cleanup $project to remove all temporary fields

{ "$project" : { "_tmp"  : 0 } }