Update a collection based on another collection

Working with Data
1

using Mongo version 4.2 I need to update a field in one collection as based on a field from another collection, according to a mutual ID field in both collections.

In a regualr SQL, the update would be:

UPDATE col1, col2
SET col2.entityId = col1.Entity_ID
WHERE col1.id = col2.id
and col2.type='DEVICE';

How can I achieve this?

Thanks

2

It would help us to help you if you could provide sample input document and expected result.

It is easier for you to provide sample documents that you have than for us to create arbitrary document that could match your use case.

3

In addition to which the SQL offered is not correct since col1 doesn’t get updated!

4

Hi,

So here is an example of a document from both collections:

col2 (the collection to be updated):

	"userName" : "admin",
	"date" : ISODate("2022-04-10T20:37:44.854+03:00"),
	"entityId" : 234,
	"type" : "DEVICE",
	"entityName" : "G-37299D",
	"subscriberName" : "MTP14262 R5.12u 053 ",
	"deviceName" : "G-37299D",
	"id" : 13760,
	"tei" : "000148190607400",
	"operationDTO" : "CREATE",
	"operation" : "Associated a TG DMO Trng 2a",
	"version" : 0,
	"auditId" : 315
},

col1 (the source for the update):

	"id" : 13760,
	"version" : 0,
	"audit_id" : 10823,
	"parent_name" : "Garda Cluster2",
	"role" : "",
	"imei" : NumberLong("108242225510"),
	"group_member_id" : "",
	"codeplugversion" : 7325,
	"issi" : 28827,
	"radioucsbarredcallesrerouting_id" : 14840254,
	"active_scan_tgs_allowed" : 4,
	"radioitmpath" : "",
	"directorate_id" : "",
	"department_id" : "",
	"branch_id" : "",
	"section_id" : "",
	"Entity_ID" : 111
},

So I need to update col2.entityId field from col1.Entity_ID field,
according to matching id’s field in both collections (but only for those documents where the col2.type=‘DEVICE’)

Thanks

5

To add more to my previous reply - regarding the expected result:
I need col2.entityId to be to be updated to value “111” (which is the value of col1.Entity_ID)
accroding to a match between col2.id and col1.id (in this case tha value “13760” which matches in both collections)

Thanks,
Tamar

6

May be there is a better way with the new update with aggregation. But my approach would be to use lookup to get the correct value and then merge that value back into the collection.

Untested with many blanks still to fill.

We start we a match stage for

match_stage = { "$match" : {{ "type" : "DEVICE" } }

Then a lookup stage for

lookup_stage = { "$lookup" : { 
  "from" : "col1" ,
  "localField" : "id" ,
  "foreignField" : "id" ,
  "as" : _tmp_lookup_result
} }

Then a small utility stage to get the first (should be the only one) element of the lookup.

set_first_stage = { "$set" : { "_tmp_first" : { "$first": "$_tmp_lookup_result" } } }

The next stage is a project that only keeps the _id and the value we want to merge into the collection. So far I used tmp* for field names because I like to see the intermediate results when I debug. The project gets rid of the the tmp*.

project_stage = { "$project" : { "entityId" : "$_tmp_first.Entity_ID' } }

If we just aggregate the pipeline

pipeline = [ match_stage , lookup_stage , set_first_stage , project_stage ]

we should get results like

[ { _id: ObjectId(...), entityId: 111 } ]

If happy with the result we can use a merge stage to set the new value in the collection

merge_stage = { "$merge" : {  "into" : "col2" ,  "on" : "_id" } }

So the final pipeline that updates the documents is

pipeline = [ match_stage , lookup_stage , set_first_stage , project_stage , merge_stage ]
2 Likes