Upsert document atomically with merging arrays

Working with Data
, ,
1

Hello, I have a question regarding atomic upsert into mongo DB which includes merging arrays.
For instance I have the following document in Mongo DB:

{
	date: 2020-12-12,
	type: 4,
	payload: {
		type: some_type,
		ids: [1], - one element always, finding documents by this field
		prices: [
			{
				id: 111,
				value: 20
			},
			{
				id: 222,
				value: 30
			}
		]
	}
}

and I received the document

{
	date: 2021-01-01,
	type: 4,
	payload: {
		type: some_type,
		ids: [1], - one element always, finding documents by this field
		prices: [
			{
				id: 333,
				value: 20
			},
			{
				id: 222,
				value: 20
			}
		]
	}
}

I need to check in DB if there is a document with the same payload.id. If no then insert the received one. If yes then merge prices (if price with price.id exists then update it, if not - insert it). In the result I need to have the following document in DB:

{
	action: POST,
	version: 4,
	payload: {
		type: some_type,
		ids: [1], - one element always, finding documents by this field
		prices: [
		{
				id: 111,
				value: 20
			},
			{
				id: 333,
				value: 20
			},
			{
				id: 222,
				value: 20
			}
		]
	}
}

Is it possible do atomically via Mongo query and if it possible to do via Java Aggregation framework?

2

Any ideas anyone? Have very similar problem and at first glance it seems completely impossible with mongo

3

Hi

According to the documentation on findOneAndUpdate ( db.collection.findOneAndUpdate() - MongoDB Manual v7.0, you can provide an aggregation pipeline for the update. This would allow you to use a pull and push or addToSet within the pipeline on the array and setOnIsert for any required inserted operations, with set for the updates. You can specify the upsert in the options.

Whether the Java driver supports this I do not know. I have done similar in c# driver, however, I had to use the BulkWrite methods as the driver wouldn’t support it at the time, so I did a PullFilter then an AddToSetEach (note these are the c# driver func names) to effectively merge the incoming documents nested array.

According to this documentation( Atomicity and Transactions - MongoDB Manual v7.0, single document operations are atomic. Whether bulkWrite falls into this category or not I do not know, but for my use case it didn’t matter.

Hope that helps.