Kafka MongoDB sink append record into existing mongoDB document

I’m using Kafka MongoDB connector to push records from Kafka to MongoDB Collection and changing the _id filed value by column name First_ID’s Value.

Now in my MySQL table, I’ve two columns name First_ID and Secondary_ID

Here
First_ID has a unique ID for all records
Secondary_ID represents a group of records.

By default, Kafka MongoDB sink will create one document for each Kafka message (replacing _id value by First_ID value ), but I want to configure this connector so it appends the records with the same Secondary_ID into a single MongoDB document.

Example:

Following are the records into MySQL


+----------+--------------+----------+-----+
| First_ID | Secondary_ID | Report_No| Age |
+----------+--------------+----------+-----+
|   1      |    123       |    775   | 30  |
|   2      |    123       |    730   | 28  |
|   3      |    456       |    321   | 40  |
|   4      |    456       |    347   | 60  |
|   5      |    211       |    322   | 20  |
+----------+--------------+----------+-----+

By default, Mongo DB will create 5 Documents in the collection, but I want to configure the connector, so it will create only 3 documents for these records like this ( because it has three unique Secondary_ID )

  • Document 1
{
    "_id":{
        "Seconday_ID":123
    },
"values":[
  {
        "First_ID":1,
        "Secondary_ID":123,
        "Report_No":775,
        "Age":30
  },
    {
        "First_ID":2,
        "Secondary_ID":123,
        "Report_No":730,
         "Age":28
    }
  ]
}
  • Document 2
{
    "_id":{
        "Seconday_ID":456
    },
"values":[
  {
        "First_ID":3,
        "Secondary_ID":456,
        "Report_No":321,
        "Age":40
  },
    {
        "First_ID":4,
        "Secondary_ID":456,
        "Report_No":347,
         "Age":60
    }
  ]
}
  • Document 3
{
    "_id":{
        "Seconday_ID":456
    },
"values":[
   {
        "First_ID":5,
        "Secondary_ID":211,
        "Report_No":322,
        "Age":20
    }
  ]
}

So, Is it possible to achieve a Document structure like this and is it possible to achieve upsert operation in this?