1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

mongoose, Mongo and JSON

23 October, 2014

I am working on a reporting app that is built on the Quickbooks Online API. It pulls in reporting information that needs to be saved to a local database. I am using Mongo as the data store and using the mongoose ODM with node.js

That said, I have a schema already defined like so:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
module.exports = mongoose.model('vbDetail', {

    company_name: String,
    rowsdata: {vals:
                {
                    date: Date,
                    transaction_type: String,
                    transaction_num: String,
                    due_date: Date,
                    amount: Number,
                    open_balance: Number,
                    balance: Number
                }
            },

    meta_rows: [{
        identifier: String,
        processing_date: Date,
        processing_amount :Date,
        notes: String
    }]
})

Saving the JSON to this is straightforward. It does look tricky to loop over certain nested objects1. But it is a matter of just getting the loops right.

The JSON response itself looks like this (truncated):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
{ Header: 
           { ColData: 
              [ { value: 'GunnChamberlain PL' },
                { value: '' },
                { value: '' },
                { value: '' },
                { value: '' },
                { value: '' },
                { value: '' } ] },
          Rows: 
           { Row: 
              [ { ColData: 
                   [ { value: '03/10/2014' },
                     { value: 'Bill' },
                     { value: '2341' },
                     { value: '03/10/2014' },
                     { value: '500.0' },
                     { value: '500.0' },
                     { value: '500.0' } ],
                  type: 'Data' },
                { ColData: 
                   [ { value: '04/30/2014' },
                     { value: 'Bill' },
                     { value: '4663' },
                     { value: '04/30/2014' },
                     { value: '450.0' },
                     { value: '450.0' },
                     { value: '950.0' } ],
                  type: 'Data' },
                { ColData: 
                   [ { value: '05/31/2014' },
                     { value: 'Bill' },
                     { value: '4878' },
                     { value: '05/31/2014' },
                     { value: '875.0' },
                     { value: '875.0' },
                     { value: '1825.0' } ],
                  type: 'Data' },
                { ColData: 
                   [ { value: '06/30/2014' },
                     { value: 'Bill' },
                     { value: '5115' },
                     { value: '06/30/2014' },
                     { value: '680.0' },
                     { value: '680.0' },
                     { value: '2505.0' } ],
                  type: 'Data' } ] },
          Summary: 
           { ColData: 
              [ { value: 'Total for GunnChamberlain PL' },
                { value: '' },
                { value: '' },
                { value: '' },
                { value: '2505.0' },
                { value: '2505.0' },
                { value: '' } ] },
          type: 'Section' }

The concept is to loop over the Rows object that corresponds to individual companies. Then, for each nested Row objects loop over the ColData object and save the values to the database.

Here's what the code for it looks like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
   //test
  for (var row in report["Rows"]["Row"]) {
      while (count < companies) {
          // save the rows corresponding to each client
          for (var rowdata in report.Rows.Row[count].Rows.Row) {
              for (var coldata in report.Rows.Row[count].Rows.Row[rowdata]
                  // save company name
                  var vbd = new vbDetail({
                      company_name: report.Rows.Row[count].Header.ColDat
                  });

                  // save the row data per company
                  vbd.rowsdata = ({
                      vals: {
                          date: report.Rows.Row[count].Rows.Row[rowdata].ColData[0].value,
                          transaction_type: report.Rows.Row[count].Rows.Row[rowdata].ColData[1].value,
                          transaction_num: report.Rows.Row[count].Rows.Row[rowdata].ColData[2].value,
                          due_date: report.Rows.Row[count].Rows.Row[rowdata].ColData[3].value,
                          amount: report.Rows.Row[count].Rows.Row[rowdata].ColData[4].value,
                          open_balance: report.Rows.Row[count].Rows.Row[rowdata].ColData[5].value,
                          balance: report.Rows.Row[count].Rows.Row[rowdata].ColDat
                      }
                  }) console.log(vbd);

                  // Save the record to DB
                  vbd.save(function(err) {
                      if (err) console.log(err);
                  })
                }
              count++;
            }
      }
}