join three mongoDB collections to generate csv table

Viewed 44

say I have three collections

users

{
  "_id": {
    "$oid": String
  },
  "username": String,
}

marks

{
  "_id": {
    "$oid": String
  },
  "user_id": String,
  "exam_id": String,
  "question_id": String,
  "mark": String
}

and questions

{
  "_id": {
    "$oid": String
  },
  "exam_id": String,
  "text": String,
}

How to make a csv file like this: (assume exam_id is known)

question1.text question2.text ...
user1.username mark mark ...
user2.username mark mark ...
... ... ... ...

What I have now:

const ws = fs.createWriteStream("result.csv");
const fastcsv = require("fast-csv");
const db = MongoClient.connect('mongodb://localhost:27017', { useUnifiedTopology: true }).db('exam');

db.collection('users')
     .aggregate([
         {
             $lookup:
             {
                 from: 'marks',
                 localField: '_id',
                 foreignField: 'user_id',
                 as: 'marks_doc'
             }
         }
     ])
    //.find({'exam_id': exam_id})
    .toArray(function(err, doc) {
        if (err) throw err;
        console.log(doc);

        fastcsv.write(doc, {headers: true}).on("finish", function() {
            console.log('done!');
        }).pipe(ws);

        db.close();
    });
0 Answers
Related