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

Viewed 5866

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.

2 Answers
Related