CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
vasantharajan
vasantharajan

Reputation: 265

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

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.

Upvotes: 7

Views: 6955

Answers (2)

vasantharajan
vasantharajan

Reputation: 265

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',
        },
      },
    },
  },
},

])

Upvotes: 10

Neodan
Neodan

Reputation: 5252

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.

Upvotes: 4

Related Questions