by mango怎么group,带条件group by语句的MongoDB查询

I need to export customer records from database of mongoDB. Exported customer records should not have duplicated values. "firstName+lastName+code" is the key to DE-duped the record and If there are two records present in database with same key then I need to give preference to source field with value other than email.

customer (id,firstName,lastName,code,source) collection is this.

If there are record 3 records with same unique key and 3 different sources then i need to choose only one record between 2 sources(TV,internet){or if there are n number of sources i need the one record only}not with the 'email'(as email will be choosen when only one record is present with the unique key and source is email)

query using:

`db.customer.aggregate([

{ "$match" : { "active" : true , "dealerCode" : { "$in" : [ "111391"]} , "source" : { "$in" : [ "email","TV","internet"]}}},

{$group:{"_id":{

"firstName":"$personalInfo.firstName",

"lastName":"$personalInfo.lastName",

"code":"$vehicle.code"},

"source":{

$addToSet:{"source":"$source"}

}

}

},

{$redact:

{$cond:[{$eq:[{$ifNull:["$source","other"]},"email"]},"$$PRUNE","$$DESCEND"]}

},

{$project:

{"source":

{$map:

{"input":

{$cond:[

{$eq:[{$size:"$source"},0]},[{"source":"email"}],"$source"]

},"as":"inp","in":"$$inp.source"}

},

"record":{"_id":1}

}

} ])`

sample output:

{ "_id" : { "firstName" : "sGI6YaJ36WRfI4xuJQzI7A==", "lastName" : "99eQ7i+uTOqO8X+IPW+NOA==", "code" : "1GTHK23688F113955" }, "source" : [ "internet" ] }

{ "_id" : { "firstName" : "WYDROTF/9vs9O7XhdIKd5Q==", "lastName" : "BM18Uq/ltcbdx0UJOXh7Sw==", "code" : "1G4GE5GV5AF180133" }, "source" : [ "internet" ] }

{ "_id" : { "firstName" : "id+U2gYNHQaNQRWXpe34MA==", "lastName" : "AIs1G33QnH9RB0nupJEvjw==", "code" : "1G4GE5EV0AF177966" }, "source" : [ "internet" ] }

{ "_id" : { "firstName" : "qhreJVuUA5l8lnBPVhMAdw==", "lastName" : "petb0Qx3YPfebSioY0wL9w==", "code" : "1G1AL55F277253143" }, "source" : [ "TV" ] }

{ "_id" : { "firstName" : "qhreJVuUA5l8lnBPVhMAdw==", "lastName" : "6LB/NmhbfqTagbOnHFGoog==", "code" : "1GCVKREC0EZ168134" }, "source" : [ "TV", "internet" ] }

This is a problem with this query please suggest :(

解决方案

Your code doesn't work, because $cond is not an accumulator operator. Only these accumulator operators, can be used in a $group stage.

Assuming your records contain not more than two possible values of source as you mention in your question, you could add a conditional $project stage and modify the $group stage as,

Code:

db.customer.aggregate([

{$group:{"_id":{"id":"$id",

"firstName":"$firstName",

"lastName":"$lastName",

"code":"$code"},

"sourceA":{$first:"$source"},

"sourceB":{$last:"$source"}}},

{$project:{"source":{$cond:[{$eq:["$sourceA","email"]},

"$sourceB",

"$sourceA"]}}}

])

In case there can be more that two possible values for source, then you could do the following:

Group by the id, firstName, lastName and code. Accumulate

the unique values of source, using the $addToSet operator.

Use $redact to keep only the values other than email.

Project the required fields, if the source array is empty(all the elements have been removed), add a

value email to it.

Unwind the source field to list it as a field and not an array.

(optional)

Code:

db.customer.aggregate([

{$group:{"_id":{"id":"$id",

"firstName":"$firstName",

"lastName":"$lastName",

"code":"$code"},

"sourceArr":{$addToSet:{"source":"$source"}}}},

{$redact:{$cond:[{$eq:[{$ifNull:["$source","other"]},"email"]},

"$$PRUNE",

"$$DESCEND"]}},

{$project:{"source":{$map:{"input":

{$cond:[{$eq:[{$size:"$sourceArr"},

0]},

[{"source":"item"}],

"$sourceArr"]},

"as":"inp",

"in":"$$inp.source"}}}}

])