Upsert multilevel nested object in every level

Hello,
I’m relatively new to MongoDB.
I have to make a backend, where i can count a multi level object structure items. Every time a data arrive i have to insert into the db or if it’s exists in the db then increment the counter. But i have to do it in every level.

Example:
On an another database i have a lot of data with this structure:

"a":[
	{
    	"a_id":1,
    	"b":[
			{
				"b_id":2,
				"c":[
					{
						"c_id":3,
						someOtherMetadata...
					},...
				],
				someOtherMetadata...
			},...
		],
		someOtherMetadata...
    },...
]

With a lot of “A”-s and a lot of “B”-s inside every “A” and a lot of “C”-s inside every “B”.

And i have to store and count it in MongoDB if somebody select one “C” (the deepest level) item. I want to store the same structure without metadata and with a counter in the db but only add item on the first call.

"a":[
	{
    	"a_id":1,
		"count":1,
    	"b":[
			{
				"b_id":2,
				"count":1,
				"c":[
					{
						"c_id":3,
						"count":1
					},...
				],
			},...
		],
    },...
]

For example i have an initially empty db and the first call arrive with: {"a_id":1, "b_id":1, "c_id":1} (The call is always contains the deepest level id and all id above)

And it inserts to the db:

"a":[
	{
    	"a_id":1,
		"count":1,
    	"b":[
			{
				"b_id":1,
				"count":1,
				"c":[
					{
						"c_id":1,
						"count":1
					}
				]
			}
		]
    }
]

And the next call with: {"a_id":1, "b_id":2, "c_id":1}

Then the db become this:

"a":[
	{
    	"a_id":1,
		"count":2,
    	"b":[
			{
				"b_id":1,
				"count":1,
				"c":[
					{
						"c_id":1,
						"count":1
					}
				]
			},
			{
				"b_id":2,
				"count":1,
				"c":[
					{
						"c_id":1,
						"count":1
					}
				]
			}
		]
    }
]

The “A” counter incremented and the new “B” inserted into the A’s array.

The next call with: {“a_id”:1, “b_id”:1, “c_id”:2}

Then the db become this:

"a":[
	{
    	"a_id":1,
		"count":3,
    	"b":[
			{
				"b_id":1,
				"count":2,
				"c":[
					{
						"c_id":1,
						"count":1
					},
					{
						"c_id":2,
						"count":1
					}
				]
			},
			{
				"b_id":2,
				"count":1,
				"c":[
					{
						"c_id":1,
						"count":1
					}
				]
			}
		]
    }
]

The next call with: {“a_id”:1, “b_id”:1, “c_id”:1}

Then the db become this:

"a":[
	{
    	"a_id":1,
		"count":4,
    	"b":[
			{
				"b_id":1,
				"count":3,
				"c":[
					{
						"c_id":1,
						"count":2
					},
					{
						"c_id":2,
						"count":1
					}
				]
			},
			{
				"b_id":2,
				"count":1,
				"c":[
					{
						"c_id":1,
						"count":1
					}
				]
			}
		]
    }
]

The count of an item is always the sum of all nested items count inside it.

If “A” exists then increment, otherwise insert. If “B” exists inside the “A” then increment, otherwise insert and so on with “C”…

I don’t have any idea how i can achieve this.

Hi @Tamas_Szabo ,

Welcome to The MongoDB Community Forums! :wave:

The schema design that you described seems to be very hard to work with. As designed, I think this schema would be difficult to index, thus query performance would suffer. Typically schema design in MongoDB follows how the data would be used. Could you elaborate on the use case so maybe there are some suggestions that can be made? I believe these documentations and blogs below would be useful for your with regard to schema design

Regards,
Tarun

Hi @Tarun_Gaur,

Thanks for the answer.

The reason why i choose this schema is the querying of the data.
The write speed of the data is not important. I’m not even check the result of the add operation on the frontend. If some of the requests fails, due to the network or other reasons, it’s not a problem for me. I don’t need the exact numbers (count), this is only statistical data. The important part is the scale and the ratio.

The important and time critical part is the querying.
When i need the data:

  • I need the root object “A” by it’s id (a_id), with all nested object inside it.
  • I need the top 5 “A” object with the most “count”.
  • I need the top 5 “B” inside an “A” item, and so on…

And the list of “A” objects is not to big. (few thousand) Because this “A” objects only used for a short period of time and after that time i delete it from the db.

That’s why i used this schema.

I tought, this schema is perfect for this type of queries, but if you know a better solution, please tell me.

p.s.: A few hours ago (after a lot of reading) finally i was able to write an update pipeline to solve this problem. I will post it here, maybe it will help somebody. But before that, i want to write a description to it.

1 Like

As i writed in the previous comment, i found a solution.
And now i post it with some description, maybe it will help someone.

Big thanks to @Prasad_Saya, who made a solution to a similar problem in here: Prasad_Saya’s post
The only difference is that, this solution works only with two level. And i needed a three level solution.

I used a same $reduce method to produce this object: { "items":[...], "updated":true } but after i transformed the object to the original array with a $let and not with a new aggregation step.

The process is:

  • First i search the “A” object with a query filter and upsert it if not exists. (This is the easy part)
  • After i found the “A” object, i reduce the “b” array inside it. The reduce produce this object { "items":[...], "updated":true } with the updated array and "updated":true, or if the id not found then the unmodified array and "updated":false.
  • After this, i transform the object with a $let. If updated==true then simply write the array or else add a new element to the array.
  • And i made the same steps with the “c” array inside the “B” object.

And the full update pipeline is:

db.collection.update({
  "a_id": A_ID
},
[
   {
    $set: {
      count: {
        $add: [
          {
            $ifNull: [
              "$count",
              0
            ]
          },
          1
        ]
      },
      b: {
        $let: {
          vars: { "data":
            {
              $reduce: {
                input: {
                  $ifNull: [
                    "$b",
                    []
                  ]
                },
                initialValue: {
                  "items": [],
                  "updated": false
                },
                in: {
                  $cond: [
                    {
                      $eq: [
                        "$$this.b_id",
                        B_ID
                      ]
                    },
                    {
                      "items": {
                        $concatArrays: [
                          "$$value.items",
                          [
                            {
                              "b_id": "$$this.b_id",
                              "count": {
                                $add: [
                                  "$$this.count",
                                  1
                                ]
                              },
                              "c": {
                                $let: {
                                  vars: { "data":
                                    {
                                      $reduce: {
                                        input: {
                                          $ifNull: [
                                            "$$this.c",
                                            []
                                          ]
                                        },
                                        initialValue: {
                                          "items": [],
                                          "updated": false
                                        },
                                        in: {
                                          $cond: [
                                            {
                                              $eq: [
                                                "$$this.c_id",
                                                C_ID
                                              ]
                                            },
                                            {
                                              "items": {
                                                $concatArrays: [
                                                  "$$value.items",
                                                  [
                                                    {
                                                      "c_id": "$$this.c_id",
                                                      "count": {
                                                        $add: [
                                                          "$$this.count",
                                                          1
                                                        ]
                                                      }
                                                    }
                                                  ]
                                                ]
                                              },
                                              "updated": true
                                            },
                                            {
                                              "items": {
                                                $concatArrays: [
                                                  "$$value.items",
                                                  [
                                                    "$$this"
                                                  ]
                                                ]
                                              },
                                              "updated": "$$value.updated"
                                            }
                                          ]
                                        }
                                      }
                                    }
                                  },
                                  in: {
                                    $cond: [
                                      {
                                        $eq: [
                                          "$$data.updated",
                                          false
                                        ]
                                      },
                                      {
                                        $concatArrays: [
                                          "$$data.items",
                                          [
                                            {
                                              "c_id": C_ID,
                                              "count": 1
                                            }
                                          ]
                                        ]
                                      },
                                      {
                                        $concatArrays: [
                                          "$$data.items",
                                          []
                                        ]
                                      }
                                    ]
                                  }
                                }
                              }
                            }
                          ]
                        ]
                      },
                      "updated": true
                    },
                    {
                      "items": {
                        $concatArrays: [
                          "$$value.items",
                          [
                            "$$this"
                          ]
                        ]
                      },
                      "updated": "$$value.updated"
                    }
                  ]
                }
              }
            }
          },
          in: {
            $cond: [
              {
                $eq: [
                  "$$data.updated",
                  false
                ]
              },
              {
                $concatArrays: [
                  "$$data.items",
                  [
                    {
                      "b_id": B_ID,
                      "count": 1,
                      "c": [
                        {
                          "c_id": C_ID,
                          "count": 1,
                          
                        }
                      ]
                    }
                  ]
                ]
              },
              {
                $concatArrays: [
                  "$$data.items",
                  []
                ]
              }
            ]
          }
        },
        
      }
    }
  }
],
{
  "upsert": true
})
1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.