How to create a lookup that will be nicer for typed serialization?

[Uros_Jarc (Uroš Jarc)]

I have following collections…

# PERSON COLLECTION SCHEMA
{
    "_id":{"$oid":"64a20e0c65e828693428a8c1"},
    "username":"username_0",
}

# CONTACT COLLECTION SCHEMA
{
    "_id":{"$oid":"64a20e0c65e828693428a8c5"},
    "person_id":{"$oid":"64a20e0c65e828693428a8c1"}, <--- Person reference
    "data":"data_0",
}

# MESSAGE COLLECTION SCHEMA
{
    "_id":{"$oid":"64a278f583364c2f4f3c81c8"},
    "contact_receiver_id":{"$oid":"64a20e0c65e828693"}, <-- Contact reference
    "contact_sender_id":{"$oid":"64a20e0c65e82869"}, <--Contact reference
}

I want to create lookup which end result would look like this… results must be placed in the fields of created objects…

{
  "user": { <--- ALL USER INFOS WRAPED IN USER FIELD
      "_id": { "$oid": "64a20e0c65e828693428a8c1" },
      "username": "username_0",
  },
  "contacts": [
    {
      "contact": { <--- ALL CONTACT INFOS WRAPPED IN CONTACT FIELD
          "_id": { "$oid": "64a20e0c65e828693428a8c5" },
          "person_id": { "$oid": "64a20e0c65e828693428a8c1" },
          "data": "data_0"
      },
      "messages": [
          {
            "_id": { "$oid": "64a20e0c65e828693428a8c5" },
            "contact_receiver_id":{"$oid":"64a20e0c65e828693428a8c1"},
            "contact_sender_id":{"$oid":"64a20e0c65e828693428a8d3"},  
          },
          .......
      ]
    },
    .......
  ],
}

Currently I know how to get only nested lookup but the end result is not sutable because I dont know how to place objects in the fields of created objects…

My current results…

{
  "_id": { "$oid": "64a20e0c65e828693428a8c1" },
  "username": "username_0",
  "contacts": [
    {
      "_id": { "$oid": "64a20e0c65e828693428a8c5" },
      "person_id": { "$oid": "64a20e0c65e828693428a8c1" },
      "data": "data_0",
      "messages": [
          {
            "_id": { "$oid": "64a20e0c65e828693428a8c5" },
            "contact_receiver_id":{"$oid":"64a20e0c65e828693428a8c1"},    
            "contact_sender_id":{"$oid":"64a20e0c65e828693428a8d3"},      
          },
          .......
      ]
    },
    .......
  ],
}

Motivation why I want this…

I use Kotlin Mongodb driver and to serialize data I want to use this data classes to get nested lookup results…

@Serializable
data class DbContactsMessages(
    val contact: Contact,       <---- Domain class
    val messages: List<Message>,
)

@Serializable
data class UserConctactsMessages(
    val person: Person,         <---- Domain class
    val contacts: List<DbContactsMessages>,
)

If I would use ordinary lookup I would need dataclasses that copy domain objects fields and have additional lists holding lookup results what I don’t want since I would repeat domain objects all over again…

Please can some1 help me structure this aggregation query that suits my needs. All the best to everyone!

[Uros_Jarc (Uroš Jarc)]

I have solved the problem…

You have to $project object before you do any lookup…
The following aggregation will produce desired solution…

[
  {
    $match: {
      _id: ObjectId("64a20e0c65e828693428a8c1"),
    },
  },
  { $project: { person: "$$ROOT" } },
  {
    $lookup: {
      from: "Contact",
      localField: "_id",
      foreignField: "person_id",
      pipeline: [
        {
          $project: {
            contacts: "$$ROOT",
          },
        },
        {
          $lookup: {
            from: "Messages",
            localField: "_id",
            foreignField: "contact_id",
            as: "messages",
          },
        },
      ],
      as: "persons",
    },
  },
]

The result of the query…

{
  "user": { ... all user informations ... },
  "contacts": [
    {
      "contact": { ... all contact informations ... },
      "messages": [
          { ... all message informations ... },
          { ... all message informations ... },
          { ... all message informations ... },
          .......
      ]
    },
    .......
  ],
}