Using aggregate to fetch documents and merge them based on an id property

Working with Data
,
#1

Hello,

I am trying to use aggregate operations to retrieve data in a collection in a unique manner.

We have primary objects and supplemental objects in the same collection that are linked by a custom string id. Example:

Primary object = {"my_custom_id":"1234", "primaryProp":"abc"} 
Supplemental object = {"my_custom_id":"1234", "supplementalProp":"123"}.

The requirement is when data is read, we need to factor in the supplemental objects properties to the base primary object before returning the data. So a resulting query would come back as the following flattened object:

object = {"my_custom_id":"1234", "primaryProp":"abc", "supplementalProp":"123"}

If the supplemental object has a property that conflicts with the primary, it should be overwritten to use the supplemental value.

I am new to Mongo and it looks like an aggregate operation is what I need. I have match criteria to quickly filter down to the objects I need. From there, the resultant objects will be a bunch of primary and supplemental objects that can be matched via my custom id. The output I need is described above where we flatten the supplemental values into the base primary object and return the result.

This is where I get a little lost. It seems like merge, add fields could work but it looks like the result is persisted to the database. Whereas I am just looking to return the newly constructed object to my user but leave the objects that were used as part of the aggregate operation untouched.

Any advice would be much appreciated! Thank you.

#3

Aggregation is indeed the way to go.

The first stage would be a $lookup to find the supplemental object.

The second state would be a $replaceRoot that uses $mergeObjects. A little bit like they do in this example.

#4

Thank you for the response. That looks very close to what I am trying to do.

A follow up question. How can I use $lookup on only the records that are returned by the match stage? So instead of using lookup to find the match within a different collection, I want to perform it on the objects returned in the first match stage.

Should I be looking for a different stage? Maybe $group?

#5

Maybe you do not really need $lookup, $mergeObjects can be used as the accumulator of $group. See https://www.mongodb.com/docs/manual/reference/operator/aggregation/mergeObjects/#-mergeobjects-as-an-accumulator.

You might need to do a $sort to ensure

c.aggregate( [
    { "$match" : { "my_custom_id" : "1234" } } ,
    { "$sort" : { "_id" : 0 } } ,
    { "$group" : {
        "_id" : null , 
        "_result" : { "$mergeObjects" : "$$ROOT" }
    } } ,
    { "$replaceRoot" : {
        "newRoot" , "$_result"
    } }
] )