mongodbnosql-aggregationsubdocument

How to make conditional projections based on sub-documents using MongoDB?


here is how the relevant part of a document looks:

{
  "map": {
    "cities": [
      {
        "name": "City1",
        "x": 15,
        "y": 5,
        "owner": 1,
        "defense": 6,
        "income": 35
      },
      {
        "name": "City2",
        "x": 12,
        "y": 14,
        "owner": 0,
        "defense": 4,
        "income": 16
      },
      {
        "name": "City3",
        "x": 6,
        "y": 19,
        "owner": 2,
        "defense": 3,
        "income": 12
      }
    ]
  },
  "players": [
    {
      "userid": "64d3ebfb42fb5b118b928f5c",
      "faction": 1
    },
    {
      "userid": "636f89f0d4666b666237cec8",
      "faction": 2
    }
  ]
}

Now how to write a query that outputs this, if I am player 1 (userid: "64d3ebfb42fb5b118b928f5c"):

{
  "map": {
    "cities": [
      {
        "name": "City1",
        "x": 15,
        "y": 5,
        "owner": 1,
        "defense": 6,
        "income": 35
      },
      {
        "name": "City2",
        "x": 12,
        "y": 14,
        "owner": 0
      },
      {
        "name": "City3",
        "x": 6,
        "y": 19,
        "owner": 2
      }
    ]
  }
}

In case for player 2 it shall return (userid: "636f89f0d4666b666237cec8"):

{
  "map": {
    "cities": [
      {
        "name": "City1",
        "x": 15,
        "y": 5,
        "owner": 1
      },
      {
        "name": "City2",
        "x": 12,
        "y": 14,
        "owner": 0
      },
      {
        "name": "City3",
        "x": 6,
        "y": 19,
        "owner": 2,
        "defense": 3,
        "income": 12
      }
    ]
  }
}

I know I can just query and programmatically remove it, but I wonder if this can be done somehow better.

I tried like this, but it obviously doesn't work because "$map.cities.owner"/"$map.cities.defense" actually is/returns an array.

{
  _id: 0,
  "map.cities.name": 1,
  "map.cities.x": 1,
  "map.cities.y": 1,
  "map.cities.owner": 1,
  "map.cities.defense": {
    $cond: {
      if: {
        $eq: ["$map.cities.owner", 1],
      },
      then: "$map.cities.defense",
      else: "$$REMOVE",
    },
  },
}

I tried using $filter but this quickly got very cumbersome and I never got the result I was looking for.

I tried using $unwind on map.cities but struggled to get it into a single document again.


Solution

  • One option is to use the user's faction as a var (according to its faction) and then $map the map to filter it:

    db.collection.aggregate([
      {
        $project: {
          "map.cities": {
            $let: {
              vars: {
                faction: {
                  "$getField": {
                    "input": {
                      $first: {
                        $filter: {
                          input: "$players",
                          cond: {
                            $eq: [
                              "$$this.userid",
                              "64d3ebfb42fb5b118b928f5c"
                            ]
                          }
                        }
                      }
                    },
                    "field": "faction"
                  }
                }
              },
              in: {
                $map: {
                  input: "$map.cities",
                  in: {
                    $mergeObjects: [
                      {
                        name: "$$this.name",
                        "x": "$$this.x",
                        "y": "$$this.y",
                        "owner": "$$this.owner"
                      },
                      {
                        $cond: [
                          {
                            $eq: [
                              "$$this.owner",
                              "$$faction"
                            ]
                          },
                          "$$this",
                          {}
                        ]
                      }
                    ]
                  }
                }
              }
            }
          }
        }
      }
    ])
    

    See how it works on the mongodb playground