MongoDB filter for specific data in Array and return only specific fields in the output

Question

I have a below structure maintained in a sample collection.

{
  "_id": "1",
  "name": "Stock1",
  "description": "Test Stock",
  "lines": [
    {
      "lineNumber": "1",
      "priceInfo": {
        "buyprice": 10,
        "sellprice": 15
      },
      "item": {
        "id": "BAT10001",
        "name": "CricketBat",
        "description": "Cricket bat"
      },
      "quantity": 10
    },
    {
      "lineNumber": "2",
      "priceInfo": {
        "buyprice": 10,
        "sellprice": 15
      },
      "item": {
        "id": "BAT10002",
        "name": "CricketBall",
        "description": "Cricket ball"
      },
      "quantity": 10
    },
    {
      "lineNumber": "3",
      "priceInfo": {
        "buyprice": 10,
        "sellprice": 15
      },
      "item": {
        "id": "BAT10003",
        "name": "CricketStumps",
        "description": "Cricket stumps"
      },
      "quantity": 10
    }
  ]
}

I have a scenario where i will be given lineNumber and item.id, i need to filter the above collection based on lineNumber and item.id and i need to project only selected fields.

Expected output below:

{
  "_id": "1",
  "lines": [
    {
      "lineNumber": "1",
      "item": {
        "id": "BAT10001",
        "name": "CricketBat",
        "description": "Cricket bat"
      },
      "quantity": 10
    }
  ]
}

Note: I may not get lineNumber all the times, if lineNumber is null then i should filter for item.id alone and get the above mentioned output.The main purpose is to reduce the number of fields in the output, as the collection is expected to hold huge number of fields.

I tried the below query,

db.sample.aggregate([
{ "$match" : { "_id" : "1"} ,
{ "$project" : { "lines" : { "$filter" : { "input" : "$lines" , "as" : "line" , "cond" : 
    { "$and" : [ { "$eq" : [ "$$line.lineNumber" , "3"]} , { "$eq" : [ "$$line.item.id" , "BAT10001"]}]}}}}}
])

But i got all the fields, i'm not able to exclude or include the required fields.

Answer 1

I tried the below query and it worked for me,

db.Collection.aggregate([
{ $match: { _id: '1' } },
{
  $project: {
    lines: {
      $map: {
        input: {
          $filter: {
            input: '$lines',
            as: 'line',
            cond: {
              $and: [
                { $eq: ['$$line.lineNumber', '3'] },
                { $eq: ['$$line.item.id', 'BAT10001'] },
              ],
            },
          },
        },
        as: 'line',
        in: {
          lineNumber: '$$line.lineNumber',
          item: '$$line.item',
          quantity: '$$line.quantity',
        },
      },
    },
  },
},

])

Answer 2

You can achieve it with $unwind and $group aggregation stages:

db.collection.aggregate([
    {$match: {"_id": "1"}},
    {$unwind: "$lines"},
    {$match: {
        $or: [
            {"lines.lineNumber":{$exists: true, $eq: "1"}},
            {"item.id": "BAT10001"}
        ]
    }},
    {$group: {
        _id: "$_id",
        lines: { $push: {
            "lineNumber": "$lines.lineNumber",
            "item": "$lines.item",
            "quantity": "$lines.quantity"
        }}
    }}
])

• $match - sets the criterias for the documents filter. The first stage is takes document with _id = "1", the second takes only documents which have lines.lineNumber equal to "1" or item.id equal to "BAT10001".
• $unwind - splits the lines array into seperated documents.
• $group - merges the documents by the _id element and puts the generated object with lineNumber, item and quantity elements into the lines array.