0

I am trying to fuse/merge the information contained in two documents inside a collection, that contain different information and have only one key in common. This is an example:

{
  "_id": <Object ...>
  "fruit": "Apple" 
  "color": "Green"
  "market": "Townhall"
}
{
  "_id": <Object ...>
  "fruit": "Banana" 
  "color": "Yellow"
  "market": "Townhall"
}
{
  "_id": <Object ...>
  "fruit": "Orange" 
  "color": "Orange"
  "market": "Hospital"
}

This is an example of one of the type of documents inside the collection. The other type of documents inside the collection are the following ones:

{
  "_id": <Object ...>
  "market: "Townhall"
  "size": "Very big"
  "floors": 3
  "workers": 500
}
{
  "_id": <Object ...>
  "market: "Hospital"
  "size": "Medium"
  "floors": 1
  "workers": 50
}

The idea is to fuse the information in both documents, grouping them by the key "market" so that the final result is this one or similar to this one:

{
  "fruit": "Apple" 
  "color": "Green"
  "market": "Townhall"
  "floors": 3
  "workers": 500
}
{
  "fruit": "Banana" 
  "color": "Yellow"
  "market": "Townhall"
  "floors": 3
  "workers": 500
}
{
  "fruit": "Orange" 
  "color": "Orange"
  "market": "Hospital"
  "floors": 1
  "workers": 50
}

I know I have to use db.collection.aggregate(...), but so far I know how to group the documents based on the field "market", but as the number of documents of fruits is higher than the number of documents of markets and i want to exclude fields such as the id or the size, i am a bit lost because i don't know if a simple $push will work. Any ideas?

prasad_
  • 12,755
  • 2
  • 24
  • 36
Galiaris Timbuktumbe
  • 1
  • _"i don't know if a simple $push will work..."._ Have you tried anything? – prasad_ Apr 20 '21 at 06:03
  • To be honest so far no, but I think I will try db.collection.aggregate([{$group: { _id: "$market", fruit: {$push: "$fruit"}, color: {$push: "$color"}, floors: {$push: "$floors"}, workers: {$push: "$workers"}}}]) But I am very new at Mongodb and honestly I don't think might work :( – Galiaris Timbuktumbe Apr 20 '21 at 10:23
  • Ok, I have now tried it, and seems to work, but puts Apple and Banana inside an Array, and I want to split the array into two documents, and have no idea how to do it :( – Galiaris Timbuktumbe Apr 20 '21 at 10:39

1 Answers1

0

Ok, so by trying things, and looking at this post (MongoDB unwind multiple arrays), I adapted that to obtain my result. I used the following command in the Mongo Shell and it worked!!

> db.collection.aggregate([
  
{$group: 
     { _id: "$market", 
       fruit: {$push: "$fruit"}, color: {$push: "$color"},    
       floors: {$push: "$floors"}, 
       workers: {$push: "$workers"}
}},
 
{$unwind: 
     { path: '$fruit', 
       includeArrayIndex: 'fruit_index'
}}, 

{$unwind: 
     {path: '$color', 
      includeArrayIndex: 'color_index'
}},
 
{$project: 
     { floors: 1, workers: 1, fruit: 1, color: 1, 
       compare: {$cmp: ['$fruit_index', '$color_index']} 
}},
 
{$match: {compare: 0 
}},

{$project: 
     {_id: 1, fruit: 1, color: 1, floors: 1, workers: 1 
}}
])

The only comment is that floors and workers are inside arrays (nothing that $unwind cannot solve) and that I don't know how to add an additional key of "market" so that i don't have to show the market as in _id. Enjoy!

Galiaris Timbuktumbe
  • 1