How to merge an array field in multiple documents into a single output array in MongoDB and mongoexport to csv

Question

I have a MongoDB collection, Groups. Each group has an array of members, referenced by the member's unique id. A user can be a member of many groups.

Group data looks like this:

{ _id: 1, members: ['1', '3', '5'], status: 'active' }
{ _id: 2, members: ['4', '1', '10', '11'], status: 'inactive' }
{ _id: 3, members: ['1', '2', '9'], status: 'active' }

I'm trying to extract all members of active groups as a single array of member ids, without duplication. I want to export them to a csv file using mongoexport.

I can export the ids of the relevant projects and their member lists:

mongoexport -h localhost:3001 --db mydbname --collection groups --type=csv --fields _id,members --query '{"status": "active"}' --out mongotestoutput.txt

But I can't figure out how to merge the member lists into a single array. I've been looking at Aggregation but I'm just getting lost among all the different options, I can't see which one would do what I need. Very grateful for any help.

Anton · Accepted Answer · 2019-06-13 15:02:17Z

1

Use aggregation with $unwind and then $out to create a new collection that looks like you need. Then export this new collection to CSV file.

db.test1.insertMany([
  { _id: 1, members: ['1', '3', '5'], status: 'active' },
  { _id: 2, members: ['4', '1', '10', '11'], status: 'inactive' },
  { _id: 3, members: ['9'], status: 'active' }
])

{_id:0} here and below is used to suppress _id field

db.test1.aggregate([
  {$unwind: "$members"},
  {$project:{_id:0}},
  {$out:"test2"}
])


db.test2.find({},{_id:0})
{ "members" : "1", "status" : "active" }
{ "members" : "3", "status" : "active" }
{ "members" : "5", "status" : "active" }
{ "members" : "4", "status" : "inactive" }
{ "members" : "1", "status" : "inactive" }
{ "members" : "10", "status" : "inactive" }
{ "members" : "11", "status" : "inactive" }
{ "members" : "9", "status" : "active" }

Or if you need to get members by status in the array - add another $group, $addToSet stage:

db.test1.aggregate([
  {$unwind: "$members"},
  {$project:{_id:0}},
  { "$group": { "_id": "$status", members:{$addToSet:"$members"} } },
  {$out:"test3"}
])

db.test3.find()
{ "_id" : "inactive", "members" : [ "4", "1", "10", "11" ] }
{ "_id" : "active", "members" : [ "1", "3", "5", "9" ] }

See MongoPlayground

edited Jun 13, 2019 at 15:02

answered Jun 11, 2019 at 13:03

Anton

4,0902 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Little Brain Over a year ago

The result I want is ['1', '3', '5', '9'], that is a single array of all users who are members of an active group. I think your answer is giving something else.

Anton Over a year ago

Then just another aggregation step: { "$group": { "_id": "$status", members:{$push:"$members"} }. I'll update the answer

Little Brain Over a year ago

Hmm, no, I'm still seeing one output doc per input doc, not a single array combining members from different groups. Unwind and group both seem to me like they only act on a single document, they don't seem to combine values from many documents?

Anton Over a year ago

Hmm... I thought { "_id" : "active", "members" : [ "1", "3", "5", "9" ] } is exactly the line that you are looking for. See Mongoplayground: mongoplayground.net/p/f6YyjrORziD

Little Brain Over a year ago

Thank you for the link to mongoplayground, what a great tool! For some reason I don't get that output in my actual code, I will have to investigate further.

|

Little Brain · Accepted Answer · 2019-06-13 11:59:17Z

0

This question shows two ways to get the result I'm looking for. The accepted answer uses push, reduce and setUnion. The newer answer is simpler, using unwind and addToSet. Both work for me but I'm going with the simpler version:

db.collection.aggregate([
  { $match: { "status": "active" },
  { $unwind: "$members"},
  { $group:{
     _id: 0,
     selectedMembers: { $addToSet: '$members' }      
  }}
])

I'm extracting the array I want from the JSON object returned by this expression.

edited Jun 13, 2019 at 11:59

answered Jun 11, 2019 at 16:29

Little Brain

2,8732 gold badges37 silver badges62 bronze badges

Collectives™ on Stack Overflow

How to merge an array field in multiple documents into a single output array in MongoDB and mongoexport to csv

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related