Using $regex in $cond to sum values based on key

Working with Data
1

Hi Team,

Want to know is there facility in $cond can use the $regex to match if condition rather using $eq operation.

For Example collection is as below,

**[**
**	{name: 'A', city: 'Mumbai',status:'Yes'},**
**	{name: 'B', city: 'Tokyo',status:'No'},**
**	{name: 'C', city: 'New York',status:'Yes'},**
**	{name: 'D', city: 'Mumbai',status:'Yes'},**
**	{name: 'E', city: 'Mumbai',status:'No'},**
**	{name: 'F', city: 'Mumbai',status:'yes'},**
**]**

Need to find out the city wise status count for Yes and No
Expected output:

[
	{city: 'Mumbai', statusYes: 3, statusNo: 1},
	{city: 'Tokyo', statusYes: 0, statusNo: 1},
	{city: 'New York', statusYes: 1, statusNo: 0}
]

Tried using aggregation of $group operation by,

$group: {
	_id: {city: "$city"},
	city: { $last: "$city"},
  	statusYes: {
		$sum: {
			$cond: {
				if: {$eq:["$status","Yes"]},
				then: 1,
				else: 0
			}
		}
	},
	statusNo: {
		$sum: {
			$cond: {
				if: {$eq:["$status","No"]},
				then: 1,
				else: 0
			}
		}
	},
}

And then it results,

[
	{city: 'Mumbai', statusYes: 2, statusNo: 1},
	{city: 'Tokyo', statusYes: 0, statusNo: 1},
	{city: 'New York', statusYes: 1, statusNo: 0}
]

Since count getting wrong the I’ve tried using $regex but it not able to find correct result.
Used:
if: { $eq: ["$status", /yes/i] } shows 0 count.

And if I used $regex like,
if: { status: {$regex: 'yes', $options: 'i'}} then shows an expression must have exactly one field.

Can anyone please suggest what is missing here.

Regards,
Jitendra.

2

Hello @Jitendra_Patwa,

It is due to your data inconsistency. Try this query and see what is the result:

db.collection.find({city: "Mumbai", status: "Yes"})

The query is returning only two documents. The third one has a status of "yes".

3

Hi @Prasad_Saya,

Yes you are right, there is data inconsistency in document, because of that I need to evaluate using $regex.
Since, when I used below,

$sum: {
$cond: {
if: {$eq:[“$status”,“Yes”]},
then: 1,
else: 0
}
}
Then its appropriately fetch status of value “Yes” and count is 2, but I need including “yes” to be rendered using regex pattern and then it results count 3, but yet not succeeded.

Thanks,
Jitendra.

4

Hi @Prasad_Saya,

Yes you are right, there is data inconsistency in document, because of that I need to evaluate using $regex.
Since, when I used below,

$sum: {
$cond: {
if: {$eq:[“$status”,“Yes”]},
then: 1,
else: 0
}
}

Then its appropriately fetch status of value “Yes” and count is 2, but I need including “yes” to be rendered using regex pattern and then it results count 3, but yet not succeeded.

Thanks,
Jitendra.

5

Hello @Jitendra_Patwa,

You don’t need to use regex for this as there is an aggregation operator to compare in a case-insensitive manner. The following will work fine for your use case:

db.collection.aggregate([
{ 
  $group: {
      _id: "$city",
      statusYes: {
          $sum: {
              $cond: {
                  if: { $eq:[ { $strcasecmp: [ "$status", "yes" ] }, 0 ] },
                  then: 1,
                  else: 0
              }
          }
      },
      statusNo: {
          $sum: {
              $cond: {
                  if: { $eq:[ { $strcasecmp: [ "$status", "no" ] }, 0 ] },
                  then: 1,
                  else: 0
              }
          }
      }
  } 
}
])

The output (as you had expected):

{ "_id" : "Tokyo", "statusYes" : 0, "statusNo" : 1 }
{ "_id" : "Mumbai", "statusYes" : 3, "statusNo" : 1 }    // <== statusYes: 3, for Mumbai
{ "_id" : "New York", "statusYes" : 1, "statusNo" : 0 }
6

Hi Prasad,

Thanks it works using $strcasecmp.

1 Like
7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.