The Cost of Not Knowing MongoDB

1
const event = {  
2
	key: '0000000000000000000000000000000000000000000000000000000000000001',
3
	date: new Date('2022-02-01'),
4
	pending: 1,
5
};

1
export const reports = [  
2
	{    
3
		id: 'oneYear',    
4
		end: new Date('2022-06-15T00:00:00.000Z'),    
5
		start: new Date('2021-06-15T00:00:00.000Z'),    
6
		totals: { approved: 4, noFunds: 1, pending: 1, rejected: 1 },
7
	},  
8
	{    
9
		id: 'threeYears',    
10
		end: new Date('2022-06-15T00:00:00.000Z'),    
11
		start: new Date('2019-06-15T00:00:00.000Z'),    
12
		totals: { approved: 8, noFunds: 2, pending: 2, rejected: 2 },
13
	},  
14
	{    
15
		id: 'fiveYears',    
16
		end: new Date('2022-06-15T00:00:00.000Z'),    
17
		start: new Date('2017-06-15T00:00:00.000Z'),    
18
		totals: { approved: 12, noFunds: 3, pending: 3, rejected: 3 },
19
	},  
20
	{    
21
		id: 'sevenYears',    
22
		end: new Date('2022-06-15T00:00:00.000Z'),    
23
		start: new Date('2015-06-15T00:00:00.000Z'),    
24
		totals: { approved: 16, noFunds: 4, pending: 4, rejected: 4 },
25
	},  
26
	{    
27
		id: 'tenYears',    
28
		end: new Date('2022-06-15T00:00:00.000Z'),    
29
		start: new Date('2012-06-15T00:00:00.000Z'),    
30
		totals: { approved: 20, noFunds: 5, pending: 5, rejected: 5 },
31
	},
32
]; 

1
type SchemaV1 = {  
2
	_id: {    
3
		key: string;    
4
		date: Date;  
5
	};  
6
	approved?: number;  
7
	noFunds?: number;  
8
	pending?: number;  
9
	rejected?: number;
10
}; 

1
const operation = {  
2
	updateOne: {    
3
		filter: {      
4
			_id: { date: event.date, key: event.key },    
5
		},    
6
		update: {      
7
			$inc: {        
8
				approved: event.approved,        
9
				noFunds: event.noFunds,        
10
				pending: event.pending,        
11
				rejected: event.rejected,      
12
			},    
13
		},    
14
		upsert: true,  
15
	},
16
}; 

1
const pipeline = [
2
  {  
3
    $match: {  
4
      '_id.key': request.key,  
5
      '_id.date': { $gte: Date.now() - oneYear, $lt: Date.now() },  
6
    },  
7
  },  
8
  {  
9
    $group: {  
10
      _id: null,  
11
      approved: { $sum: '$approved' },  
12
      noFunds: { $sum: '$noFunds' },  
13
      pending: { $sum: '$pending' },  
14
      rejected: { $sum: '$rejected' },  
15
    },  
16
  },  
17
];

1
const keys = { '_id.key': 1, '_id.date': 1 };  
2
const options = { unique: true };  
3
  
4
db.appV1.createIndex(keys, options);

1
type SchemaV2 = {  
2
  _id: ObjectId;  
3
  key: string;  
4
  date: Date;  
5
  approved?: number;  
6
  noFunds?: number;  
7
  pending?: number;  
8
  rejected?: number;  
9
};

1
const operation = {  
2
  updateOne: {  
3
    filter: { key: event.key, date: event.date },  
4
    update: {  
5
      $inc: {  
6
        approved: event.approved,  
7
        noFunds: event.noFunds,  
8
        pending: event.pending,  
9
        rejected: event.rejected,  
10
      },  
11
    },  
12
    upsert: true,  
13
  },  
14
};

1
const pipeline = [
2
  {  
3
    $match: {  
4
      key: request.key,  
5
      date: { $gte: Date.now() - oneYear, $lt: Date.now() },  
6
    },  
7
  },  
8
  {  
9
    $group: {  
10
      _id: null,  
11
      approved: { $sum: '$approved' },  
12
      noFunds: { $sum: '$noFunds' },  
13
      pending: { $sum: '$pending' },  
14
      rejected: { $sum: '$rejected' },  
15
    },  
16
  },  
17
];

1
const keys = { key: 1, date: 1 };  
2
const options = { unique: true };  
3
  
4
db.appV2.createIndex(keys, options);

1
const document = {  
2
  _id: ObjectId('6685c0dfc2445d3c5913008f'),  
3
  key: '0000000000000000000000000000000000000000000000000000000000000001',  
4
  date: new Date('2022-06-25T00:00:00.000Z'),  
5
  approved: 10,  
6
  noFunds: 3,  
7
  pending: 1,  
8
  rejected: 1,  
9
};

1
const buildKey = (key: string): Buffer => {  
2
  return Buffer.from(key, 'hex');  
3
};

1
type SchemaV3 = {  
2
  _id: ObjectId;  
3
  key: Buffer;  
4
  date: Date;  
5
  a?: number;  
6
  n?: number;  
7
  p?: number;  
8
  r?: number;  
9
};

1
const operation = {  
2
  updateOne: {  
3
    filter: { key: buildKey(event.key), date: event.date },  
4
    update: {  
5
      $inc: {  
6
        a: event.approved,  
7
        n: event.noFunds,  
8
        p: event.pending,  
9
        r: event.rejected,  
10
      },  
11
    },  
12
    upsert: true,  
13
  },  
14
};

1
const pipeline = [  
2
  {  
3
    $match: {  
4
      key: buildKey(request.key),  
5
      date: { $gte: Date.now() - oneYear, $lt: Date.now() },  
6
    },  
7
  },  
8
  {  
9
    $group: {  
10
      _id: null,  
11
      approved: { $sum: '$a' },  
12
      noFunds: { $sum: '$n' },  
13
      pending: { $sum: '$p' },  
14
      rejected: { $sum: '$r' },  
15
    },  
16
  },  
17
];

1
const keys = { key: 1, date: 1 };  
2
const options = { unique: true };  
3
  
4
db.appV3.createIndex(keys, options);

1
const bulkUpsertFilter = {  
2
  key: event.key,  
3
  date: event.date,  
4
};
5
6
const getReportsFilter = {  
7
  key: request.key,  
8
  date: {  
9
    $gte: new Date('2021-06-15'),  
10
    $lt: new Date('2022-06-15'),  
11
  },  
12
};

1
const _id = Buffer.from('000120220101', 'hex');

1
const buildId = (key: string, date: Date): Buffer => {  
2
  const day = date.toISOString().split('T')[0].replace(/-/g, ''); // YYYYMMDD  
3
  return Buffer.from(`${key}${day}`, 'hex');  
4
};

1
type SchemaV4 = {  
2
  _id: Buffer;  
3
  a?: number;  
4
  n?: number;  
5
  p?: number;  
6
  r?: number;  
7
};

1
const operation = {  
2
  updateOne: {  
3
    filter: { _id: buildId(event.key, event.date) },  
4
    update: {  
5
      $inc: {  
6
        a: event.approved,  
7
        n: event.noFunds,  
8
        p: event.pending,  
9
        r: event.rejected,  
10
      },  
11
    },  
12
    upsert: true,  
13
  },  
14
};

1
const pipeline = [  
2
  {  
3
    $match: {  
4
      _id: {  
5
        $gte: buildId(request.key, Date.now() - oneYear),  
6
        $lt: buildId(request.key, Date.now()),  
7
      },  
8
    },  
9
  },  
10
  {  
11
    $group: {  
12
      _id: null,  
13
      approved: { $sum: '$a' },  
14
      noFunds: { $sum: '$n' },  
15
      pending: { $sum: '$p' },  
16
      rejected: { $sum: '$r' },  
17
    },  
18
  },  
19
];

1
// A sample document
2
const doc = {  
3
  _id: { key: '0001', date: new Date('2020-01-01') },  
4
  approved: 2,  
5
  rejected: 1,  
6
};
7
8
// Making sure we have an empty collection
9
db.appV1.drop();
10
11
// Inserting the document in the `appV1` collection
12
db.appV1.insertOne(doc);
13
14
// Finding a document using `Bulk Upsert` filtering criteria
15
const bulkUpsertFilter = {  
16
  _id: { key: '0001', date: new Date('2020-01-01') },  
17
};
18
db.appV1.find(bulkUpsertFilter).explain('executionStats');
19
/*{
20
...
21
  executionStats: {
22
    nReturned: 1,
23
    totalKeysExamined: 1,
24
    totalDocsExamined: 1,
25
    ...
26
    executionStages: {
27
      stage: 'EXPRESS_IXSCAN',
28
      ...
29
    }
30
    ...
31
  },
32
  ...
33
}*/
34
35
// Finding a document using `Get Reports` filtering criteria
36
const getReportsFilter = {  
37
  '_id.key': '0001',  
38
  '_id.date': { $gte: new Date('2019-01-01'), $lte: new Date('2021-01-01') },  
39
};
40
db.appV1.find(getReportsFilter).explain('executionStats');
41
/*{
42
...
43
  executionStats: {
44
    nReturned: 1,
45
    totalKeysExamined: 0,
46
    totalDocsExamined: 1,
47
    ...
48
    executionStages: {
49
      stage: 'COLLSCAN',
50
      ...
51
    }
52
    ...
53
  },
54
  ...
55
}*/

1
const documentValue = { key: '0001', date: '2010-01-01T00:00:00.000Z' };  
2
const indexValue = "{key:0001,date:2010-01-01T00:00:00.000Z}";