MongoDB - Aggregate get specific objects in an array

Question

How can I get only objects in the sales array matching with 2021-10-14 date ?
My aggregate query currently returns all objects of the 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}

nimrod serok · Accepted Answer · 2022-03-17 06:45:03Z

2

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}}
])

This will separate the sales into different documents, each containing only one sale, and allow you to match conditions easily.
See: https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/

edited Mar 17, 2022 at 6:45

answered Mar 16, 2022 at 11:39

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

chridam · Accepted Answer · 2022-03-16 11:38:56Z

1

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

answered Mar 16, 2022 at 11:38

chridam

104k26 gold badges246 silver badges243 bronze badges

1 Comment

Takis Over a year ago

if sales array contains >1 members with that date 1 of them is kept. Maybe this is what you need but check this

Yong Shun · Accepted Answer · 2022-03-16 11:39:10Z

1

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

answered Mar 16, 2022 at 11:39

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Collectives™ on Stack Overflow

MongoDB - Aggregate get specific objects in an array

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related