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.
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