How can I get only objects in the sales array matching with 2021-10-14 date ?
My aggregate currently return all objects of sales array if at least one is matching.

Dataset Documents

{
    "name": "#0",
    "sales": [{
        "date": "2021-10-14",
        "price": 3.69,
    },{
        "date": "2021-10-15",
        "price": 2.79,
    }]
},
{
    "name": "#1",
    "sales": [{
        "date": "2021-10-14",
        "price": 1.5,
    }]
}

Aggregate

{
  $match: {
    sales: {
      $elemMatch: {
        date: '2021-10-14',
      },
    },
  },
},
{
  $group: {
    _id: 0,
    data: {
      $push: '$sales',
    },
  },
},
{
  $project: {
    data: {
      $reduce: {
        input: '$data',
        initialValue: [],
        in: {
          $setUnion: ['$$value', '$$this'],
        },
      },
    },
  },
}

Result

{"date": "2021-10-14","price": 3.69},
{"date": "2021-10-15","price": 2.79},
{"date": "2021-10-14","price": 1.5}

Result Expected

{"date": "2021-10-14","price": 3.69},
{"date": "2021-10-14","price": 1.5}

CodePudding user response：

You actually need to use a $replaceRoot or $replaceWith pipeline which takes in an expression that gives you the resulting document filtered using $arrayElemAt (or $first) and $filter from the sales array:

[
    { $match: { 'sales.date': '2021-10-14' } },
    { $replaceWith: {
       $arrayElemAt: [
           {
               $filter: {
                   input: '$sales',
                   cond: { $eq: ['$$this.date', '2021-10-14'] }
               }
           },
          0 
       ]
   } }
]

OR

[
    { $match: { 'sales.date': '2021-10-14' } },
    { $replaceRoot: {
       newRoot: {
           $arrayElemAt: [
               {
                   $filter: {
                       input: '$sales',
                       cond: { $eq: ['$$this.date', '2021-10-14'] }
                   }
               },
              0 
           ]
       }
    } }
]

Mongo Playground

CodePudding user response：

In $project stage, you need $filter operator with input as $reduce operator to filter the documents.

{
  $project: {
    data: {
      $filter: {
        input: {
          $reduce: {
            input: "$data",
            initialValue: [],
            in: {
              $setUnion: [
                "$$value",
                "$$this"
              ],
            }
          }
        },
        cond: {
          $eq: [
            "$$this.date",
            "2021-10-14"
          ]
        }
      }
    }
  }
}

Sample Mongo Playground

CodePudding user response：

How about using $unwind:

.aggregate([
{$match: {    sales: {$elemMatch: {date: '2021-10-14'}    }  }},
{$unwind: '$sales'},
{$match: {'sales.date': '2021-10-14'}},
{$project: {date: '$sales.date', price: '$sales.price', _id: 0}}
])