I have the following data model:

{
    "_id": {
      "$oid": "63b6da81661f0ecd23cd9830"
    },
    "Plan": [
      {
        "_id": {
          "$oid": "63b6311e0871625f7ceb85ad"
        },
        "Name": "Straight ankle lock",
        "Date": {
          "$date": {
            "$numberLong": "1672725600000"
          }
        },
        "Notes": "Christian taught ankle locks",
        "TeamId": {
          "$oid": "63a291ebb60592854e23b8fb"
        }
      }
    ],
    "User": [
      {
        "_id": {
          "$oid": "6240fd2ee1335b45680bee9d"
        },
        "FirstName": "Test",
        "LastName": "User",
        "TeamId": {
          "$oid": "639fd03bb31c7995a9d4b28c"
        }
      }
    ]
  }

And I'd like to show a new object via aggregation that looks like:

    {
        "_id": {
          "$oid": "63b6da81661f0ecd23cd9830"
        },
        "PlanName": "Straight ankle lock",
        "UserName": "Test User"
     }

I've been trying to figure this out for a few days, but at this point not sure if it is even possible. Any ideas?

Thanks.

Newer model based on Ray's input using project:

    {
      "_id": {
        "$oid": "63b6da81661f0ecd23cd9830"
      },
      "InsertDate": {
        "$date": {
          "$numberLong": "1672927873507"
        }
      },
      "Plan": {
        "Name": "Straight ankle lock"
      },
      "User": {
        "FirstName": "Adam",
        "LastName": "Gusky"
      },
      "Team": {
        "TeamName": "GB2 No Gi"
      }
    }

The query I'm using to get the above data:

[
{
    $lookup: {
      from: "Plans",
      localField: "PlanId",
      foreignField: "_id",
      as: "Plan",
    },
  },
  {
    $lookup: {
      from: "Teams",
      localField: "TeamId",
      foreignField: "_id",
      as: "Team",
    },
  },
  {
    $lookup: {
      from: "Users",
      localField: "UserId",
      foreignField: "_id",
      as: "User",
    },
  },
  {
    $project: {
      Plan: {
        $first: "$Plan",
      },
      User: {
        $first: "$User",
      },
      Team: {
        $first: "$Team",
      },
      InsertDate: 1,
    },
  },
  {
    $project: {
      "Plan.Name": 1,
      "User.FirstName": 1,
      "User.LastName": 1,
      "Team.TeamName": 1,
      InsertDate: 1,
    },
  },
]

CodePudding user response：

You can simply set the value you want in the $project stage.

db.collection.aggregate([
  {
    $project: {
      _id: 1,
      PlanName: {
        $first: "$Plan.Name"
      },
      UserName: {
        "$concat": [
          {
            "$first": "$User.FirstName"
          },
          " ",
          {
            "$first": "$User.LastName"
          }
        ]
      }
    }
  }
])

Mongo Playground