0

Let me revise survey collection from the documentation to elaborate my point (a new field was added):

{ _id: 1, source: "email", results: [ { product: "abc", score: 10 }, { product: "xyz", score: 5 } ] }
{ _id: 2, source: "street", results: [ { product: "abc", score: 8 }, { product: "xyz", score: 7 } ] }
{ _id: 3, source: "email", results: [ { product: "abc", score: 7 }, { product: "xyz", score: 8 } ] }

So if I do the following query

db.survey.find(
   { results: { $elemMatch: { product: "xyz", score: { $gte: 7 } } } }
)

I get two documents

{ _id: 2, source: "street", results: [ { product: "abc", score: 8 }, { product: "xyz", score: 7 } ] }
{ _id: 3, source: "email", results: [ { product: "abc", score: 7 }, { product: "xyz", score: 8 } ] }

However, since I'm interested in only product "xyz" and their scores, I want the following result:

{ _id: 2, source: "street", results: [ { product: "xyz", score: 7 } ] }
{ _id: 3, source: "email", results: [ { product: "xyz", score: 8 } ] }

I tried $ (projection) but it wouldn't allow me to get the "source" field. I then found the thread Retrieve only the queried element in an object array in MongoDB collection suggesting $filter, but when I did:

db.survey.aggregate([
    {$project: {results: {$filter: {input: "$results", as: "results", cond: {$elemMatch: {"$$results.product": "xyz", "$$results.score": {"$gte": 7}}}}}}}
])

It tells me "Unrecognized expression '$elemMatch'". Please show me how I can get my desired output. Thanks.

ytu
  • 1,822
  • 3
  • 19
  • 42
  • Because `$elemMatch` does not work with the `$filter` operator and also `$elemMatch` is only work with array and once you used `$filter` you will get array elements inside the `cond` expression. So, you just need to use `$eq` with `$and` operator to match both the conditions. Please check the answer in the duplicate question to see how `$fitler` works. – Ashh Apr 15 '20 at 06:57
  • @Ashh I really don't understand. Your suggestion doesn't seem to provide my desired output. It would return all three documents, but one of them has an empty array in "results" field. Does that mean I need another stage in the pipeline to exclude that? – ytu Apr 15 '20 at 07:34
  • I mean this `([ { $match: { results: { $elemMatch: { product: "xyz", score: { $gte: 7 } } } }}, { $project: { results: { $filter: { input: "$results", as: "result", cond: { $and: [ { $eq: [ "$$result.product", "xyz" ] }, { $gte: [ "$$result.score", 7 ] } ] } } } } } ])` – Ashh Apr 15 '20 at 07:38

0 Answers0