For example, I have 6 items in collection
{ _id: 1, list: ["A", "B"] }
{ _id: 2, list: ["C", "A"] }
{ _id: 3, list: ["E", "F"] }
{ _id: 4, list: ["E", "D"] }
{ _id: 5, list: ["U", "I"] }
{ _id: 6, list: ["D", "K"] }
I would do a query to merge all the items which its list have at least 1 element matches. So the result will be:
{ _id: 7, list: ["A", "B", "C"] }
{ _id: 8, list: ["E", "F", "D", "K"] }
I'm new to MongoDB so anyone help me for this query ? Thanks alot.
I found this solution which almost solves your problem.
db.lists.aggregate([
{$unwind:"$list"},
{$group:{_id:"$list", merged:{$addToSet:"$_id"}, size:{$sum:1}}},
{$match:{size: {$gt: 1}}},
{$project:{_id: 1, merged:1, size: 1, merged1: "$merged"}},
{$unwind:"$merged"},
{$unwind:"$merged1"},
{$group:{_id:"$merged", letter:{$first:"$_id"}, size:{$sum: 1}, set: {$addToSet:"$merged1"}}},
{$sort:{size:1}},
{$group:{_id: "$letter", mergedIds:{$last:"$set"}, size:{$sum:1}}},
{$match: {size:{$gt:1}}}
])
I have tested this in my mongo shell which gives the following output:
{ "_id" : "E", "matchedIds" : [ 6, 3, 4 ], "size" : 2 }
{ "_id" : "A", "matchedIds" : [ 1, 2 ], "size" : 2 }
The matchedIds represents the docs id-s which have common value in the list array.
I think in the above aggregation can be done some optimization, but initially I found this, will try to find other ways. In addition you can use $lookup aggregation at the end of aggregation pipline to match the id-s with the set values. I couldn't test this because my mongo version doesn't support $lookup. But you can manually get that values inside some for loop if you use Node.js or something else.
Edited
This algorithm will only work if the amount of intersected lists for each list is no more than 3.
For example this will work:
{ "_id" : 1, "list" : [ "A", "B" ] }
{ "_id" : 2, "list" : [ "C", "A" ] }
{ "_id" : 3, "list" : [ "E", "F" ] }
{ "_id" : 4, "list" : [ "E", "D" ] }
{ "_id" : 5, "list" : [ "U", "I" ] }
{ "_id" : 6, "list" : [ "D", "K" ] }
{ "_id" : 7, "list" : [ "A", "L" ] }
but this will not:
{ "_id" : 1, "list" : [ "A", "B" ] }
{ "_id" : 2, "list" : [ "C", "A" ] }
{ "_id" : 3, "list" : [ "E", "F" ] }
{ "_id" : 4, "list" : [ "E", "D" ] }
{ "_id" : 5, "list" : [ "U", "I" ] }
{ "_id" : 6, "list" : [ "D", "K" ] }
{ "_id" : 7, "list" : [ "L", "K" ] }
Here the lists with ids of 7, 6, 4, 3 has intersection, so the number of intersected lists is 4, in this case the provided algorithm will not work. It will work only if the amount of intersection is less than 4 for each list
Final notice
It seems you can't achieve to your desired result by doing merge computation in the mongo database layer. If you are building an application then it will be better to do computation also in the application layer.