subdocument query not containing values outside array with regex matching

Question

If I have a collection of recipes where each item looks like:

{
    name: "Recipe 1",
    ingredients: [{
        name: "dark chocolate",
        qty: "1 g"
    }, {
        name: "milk",
        qty: "2 g"
    }, {
        name: "unsalted butter",
        qty: "3 g"
    }]
}

How can I find all recipes that:

have an ingredient of which the name either contains milk or chocolate
but do not contain any other ingredients that do not either contain milk or chocolate?

The example above would not match that query.

Summary: I need all recipes that contain milk, chocolate or both, but nothing else.

You could negate looking for choc and milk by using `$not` ( http://docs.mongodb.org/manual/reference/operator/not/ ) and `$elemMatch` ( http://docs.mongodb.org/manual/reference/projection/elemMatch/ ): `{ ingredients: { $not: { $elemMatch: {name: /chocolate|milk/} } } }` Or something similar — Sammaye, Feb 08 '13 at 12:39

score 2 · Accepted Answer · edited May 23 '17 at 12:04

Using a regex expression that looks for docs not containing ingredients of milk or chocolate (using technique from here) and then negating that with a $not does the trick:

db.recipes.find({'ingredients.name': {$not: /^((?!(milk|chocolate)).)*$/}})

Tested against:

{
  "_id": ObjectId("5114f7b748994465c5b5c369"),
  "name": "Recipe 1",
  "ingredients": [
    {
      "name": "dark chocolate",
      "qty": "1 g"
    },
    {
      "name": "milk",
      "qty": "2 g"
    },
    {
      "name": "unsalted butter",
      "qty": "3 g"
    }
  ]
}
{
  "_id": ObjectId("5114f9f348994465c5b5c36a"),
  "name": "Recipe 2",
  "ingredients": [
    {
      "name": "dark chocolate",
      "qty": "1 g"
    },
    {
      "name": "milk",
      "qty": "2 g"
    }
  ]
}
{
  "_id": ObjectId("5114fcec48994465c5b5c36b"),
  "name": "Recipe 3",
  "ingredients": [
    {
      "name": "milk chocolate",
      "qty": "1 g"
    }
  ]
}
{
  "_id": ObjectId("5114fd0e48994465c5b5c36c"),
  "name": "Recipe 4",
  "ingredients": [
    {
      "name": "soy",
      "qty": "1 g"
    }
  ]
}
{
  "_id": ObjectId("5114fd5248994465c5b5c36d"),
  "name": "Recipe 5",
  "ingredients": [
    {
      "name": "chocolate mud",
      "qty": "1 g"
    }
  ]
}

It outputs:

{
  "_id": ObjectId("5114f9f348994465c5b5c36a"),
  "name": "Recipe 2",
  "ingredients": [
    {
      "name": "dark chocolate",
      "qty": "1 g"
    },
    {
      "name": "milk",
      "qty": "2 g"
    }
  ]
}
{
  "_id": ObjectId("5114fcec48994465c5b5c36b"),
  "name": "Recipe 3",
  "ingredients": [
    {
      "name": "milk chocolate",
      "qty": "1 g"
    }
  ]
}
{
  "_id": ObjectId("5114fd5248994465c5b5c36d"),
  "name": "Recipe 5",
  "ingredients": [
    {
      "name": "chocolate mud",
      "qty": "1 g"
    }
  ]
}

While your regex indeed seems correct (tested separately), this does not return any results. If I take away the $not operator, it returns everything (while you would assume recipes with either milk or chocolate in the name wouldn't be returned). — smhg, Feb 08 '13 at 14:45
@smhg Hmm...it does work when I try it. Maybe I'm misunderstanding your question. See my update. — JohnnyHK, Feb 08 '13 at 15:03
which version of mongodb do you use? i'll try it on the same version — smhg, Feb 08 '13 at 15:09
Aha, the problem on my side is that there are also recipes without ingredients and those are returned too (i was only doing counts). You couldn't know that of course. Thank you for your answer! — smhg, Feb 08 '13 at 15:37

subdocument query not containing values outside array with regex matching

1 Answers1