How to compare calculated field vs collection field in MongoDB?

Vote count: 0

I am newbie to mongodb. I am trying to convert following sql query to mongodb query.

select ename,sal,comm from emp where comm > (sal*1.10);

and below is what I have tried so far.

1. db.emp.aggregate([ {$match: {COMM: {$exists: true}}}, { $project: { ENAME : 1, SAL : 1, COMM :1, 'CALSAL' : { $multiply : [ '$SAL', 1.10 ]}}} ,{$match : {'CALSAL' : {$gt : '$COMM'}}}]);

2. db.emp.aggregate([ {$match: {COMM: {$exists: true}}}, { $project: { ENAME : 1, SAL : 1, COMM :1, 'CALSAL' : { $multiply : [ '$SAL', 1.10 ]}}} ,{$match : { '$COMM' : {$gt : 'CALSAL'} }}]);

3. db.emp.aggregate([ {$match: {COMM: {$exists: true}}}, { $project: { ENAME : 1, SAL : 1, COMM :1, 'CALSAL' : { $multiply : [ '$SAL', 1.10 ]}, cmp_value: {$cmp: ['$COMM', 'CALSAL']} } ,{$match : { cmp_value : {$gt : 0} }}]);

any help would be appreciated.

Thanks in advance.

asked 27 secs ago

Suhas Khulpe

1 Answer

Vote count: 0

After lot of efforts I got two ways to solve above problem.

1. db.emp.find({COMM: {$exists: true}, $where: "this.COMM > (this.SAL * 1.10)"},{ENAME : 1, SAL : 1, COMM :1});

2. db.emp.aggregate([ {$match: {COMM: {$exists: true}}}, { $project: { ENAME : 1, SAL : 1, COMM :1, 'CALSAL' : { $multiply : [ '$SAL', 1.10 ]} } }, { $project: { ENAME : 1, SAL : 1, COMM :1, cmp_value: {$cmp: ['$COMM', '$CALSAL']} }} ,{$match : { cmp_value : 1} } ]);

answered 27 secs ago

Suhas Khulpe

Let's block ads! (Why?)

How to compare calculated field vs collection field in MongoDB?