MongoDB - How to sort document that has certain object in array field-CodePudding

I have a mongodb collection, containing documents with the following general structure:

{
  "id" : 1,
  "name" : "steve"
  "food" : [
    {
      "name" : "steak",
      "price" : 10000
    },
    {
      "name" : "noodle",
      "price" : 5000
    }
  ],
},
{
  "id" : 2,
  "name" : "kevin"
  "food" : [
    {
      "name" : "steak",
      "price" : 5000
    }
  ],
},
{
  "id" : 3,
  "name" : "jason"
  "food" : [
    {
      "name" : "soup",
      "price" : 7000
    },
    {
      "name" : "noodle",
      "price" : 8000
    }
  ],
},
{
  "id" : 4,
  "name" : "bob"
  "food" : [
    {
      "name" : "soup",
      "price" : 7000
    },
    {
      "name" : "steak",
      "price" : 3000
    }
  ],
}

How would one, with this setup, construct a query to retrieve all documents with a particular food name and sort it by that particular food price (ex: find all document with food.name="steak" and sort the documents by "steak" price)?

result should look like this:

{
  "id" : 4,
  "name" : "bob"
  "food" : [
    {
      "name" : "soup",
      "price" : 7000
    },
    {
      "name" : "steak",
      "price" : 3000
    }
  ],
},
{
  "id" : 2,
  "name" : "kevin"
  "food" : [
    {
      "name" : "steak",
      "price" : 5000
    }
  ],
},
{
  "id" : 1,
  "name" : "steve"
  "food" : [
    {
      "name" : "steak",
      "price" : 10000
    },
    {
      "name" : "noodle",
      "price" : 5000
    }
  ],
}

CodePudding user response：

I think your question is similar to this post: MongoDB sort documents by array elements

Try this query, it works for me:

db.getCollection('collection_name').aggregate([
{"$match": {"food.name": "steak"}},
{"$addFields": {
    "order": {
        "$filter": {
            "input": "$food",
            "as": "f",
            "cond": { "$eq": [ "$$f.name", "steak" ] }
        }
    }
}},
{"$sort": {"order.price": 1}},
{"$project": {"order": 0}}
]);