Mongodb query using a nested path based on the value of another property

user_1729795 0
Viewed 358

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

spring-data-mongodb mongodb-query mongodb
1 Answers

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 },
  },
]);
user_1257400
Related