I have a collection with documents like the following:
{
_id : 1,
code: 1,
state: "FINISHED",
history: //Object
{ 'CREATED': { date: xxx , anotherParam: xxx },
'FINISHED': { date: xxx , anotherParam: xxx }
}
},
{
_id : 2,
code: 1,
state: "CREATED",
history: //Object
{ 'CREATED': { date: xxx , anotherParam: xxx }
},
{
_id : 3,
code: 2,
state: "FINISHED",
history: //Object
{ 'CREATED': { date: xxx , anotherParam: xxx },
'FINISHED': { date: xxx , anotherParam: xxx }
}
}
What I want to do is a find query in which I sort the documents by the date nested in history whose key is the value of state for each document.
In the previous example, I would sort by the current value of state ( inside the corresponding history key => "history.VALUE_OF_STATE.date".
The challenges is to be able to get FINISHED inside that path based on state value.
E.G: Query all documents where code == 1 and sort by history.VALUE_OF_EACH_DOCUMENT_STATE.date
This should return doc _id 1 and doc _id 2, sorting by date, considering doc 1's date as history.FINISHED.date, and doc 2's date history.CREATED.date
With a bit of hard-coding, since you only have 4 possible states:
db.states.aggregate([
{ $match: { code: 1 } },
{
$project: {
_id: 1,
code: 1,
state: 1,
history: 1,
sortingField: {
$switch: {
branches: [
{
case: { $eq: ["$state", "CREATED"] },
then: "$history.CREATED.date",
},
{
case: { $eq: ["$state", "FINISHED"] },
then: "$history.FINISHED.date",
},
],
},
},
},
},
{
$sort: { sortingField: 1 },
},
]);