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

Question

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

• the query shall take only the userid as input
• only where the "players.faction" is matching the "map.cities.owner", the actual values of "defense" and "income" are shown
• a player shall not see the values for cities he/she does not own
• an alternative could be, that the values default to e.g. 0 if the player is not owning the city

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.

Answer 1

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