1

I have a mongodb table, that contains a fixture of a soccer tournament, so every matchweek looks like this:

 "number": 1,
 "games" : [
            {
                    "estadio" : "G C",
                    "arbitro" : "M",
                    "resultado" : "vs",
                    "editor" : "Pepe",
                    "_id" : ObjectId("5af0889e14553f0788bc9db8"),
                    "equipo_local" : "Las Palmas",
                    "equipo_visitante" : "Villareal",
                    "fecha" : "10/05/2018",
                    "horario" : "16:00",
                    "hora" : "12:12"
            },
            {
                    "estadio" : "Ciudad de Valencia",
                    "arbitro" : "A Confirmar",
                    "resultado" : "vs",
                    "editor" : "No Asignado",
                    "_id" : ObjectId("5af0889e14553f0788bc9db7"),
                    "equipo_local" : "Levante",
                    "equipo_visitante" : "Deportivo",
                    "fecha" : "28/01/2019",
                    "horario" : "18:00"
            },
            ..
          ]

And I want to find all the objects inside "games" of every matchweek that contains "editor" : "pepe",

I've tried something like this

db.fechas.find({"games.editor": "Pepe"})

But that is showing every "games" array that contains an object with "editor": "Pepe", and I want just that specific object, not the whole array.

Iván
  • 120
  • 7

1 Answers1

2

find method checks whether your nested array contains any document matching your condition and returns entire document.

You need aggregation framework's $filter operator to get a subset of your array, try:

db.fechas.aggregate([
    {
        $addFields: {
            games: {
                $filter: {
                    input: "$games",
                    as: "game",
                    cond: {
                        $eq: [ "$$game.editor", "Pepe" ]
                    }
                }
            }
        }
    },
    {
        $match: {
            $expr: {
                $gt: [ { $size: "$games" }, 0 ]
            }
        }
    }
])

EDIT: to remove the documents where games array is empty you can use $match stage specifying condition as expression ($expr) checking array $size

mickl
  • 40,780
  • 9
  • 38
  • 59
  • Thanks for the answer, that works fine, but just one issue, it is still showing the matchweeks that don't have any game with that editor, but they have "games" empty, is there any way to avoid getting those matchweeks as a result? – Iván May 07 '18 at 18:16
  • @Iván sure, you can add next pipeline stage, check my modified answer – mickl May 07 '18 at 18:52
  • Worked perfectly! You are awesome :) – Iván May 07 '18 at 19:00