【Question】

Hi,

I am trying to use MongoDB aggregate query using $setUnion, $project and also $group to group documents. The structure of documents is:

{

“_id” : ObjectId(“55014006e4b0333c9531043e”),

“acls” : {

“append” : {

“users” : [ObjectId(“54f5bfb0336a15084785c393”) ],

“groups” : [ ]

},

“edit” : {

“groups” : [ ],

“users” : [

ObjectId(“54f5bfb0336a15084785c392”)

]

},

“fullControl” : {

“users” : [ ],

“groups” : [ ]

},

“read” : {

“users” : [ObjectId(“54f5bfb0336a15084785c392”), ObjectId(“54f5bfb0336a15084785c398”)],

“groups” : [ ]

}

},

name: “ABC”

}

{

“_id” : ObjectId(“55014006e4b0333c9531043f”),

“acls” : {

“append” : {

“users” : [ObjectId(“54f5bfb0336a15084785c365”) ],

“groups” : [ ]

},

“edit” : {

“groups” : [ ],

“users” : [

ObjectId(“54f5bfb0336a15084785c392”)

]

},

“fullControl” : {

“users” : [ ],

“groups” : [ ]

},

“read” : {

“users” : [ObjectId(“54f5bfb0336a15084785c392”), ObjectId(“54f5bfb0336a15084785c370”)],

“groups” : [ ]

}

},

name: “ABC”

}

I need to query based on the name, then I need to use $setUnion for “acls.read.users, acls.edit.users, acls.append.users and acls.fullControl.users” and after that I need to group the documents by “name” and another field having the list of users in “user field” like below:

{

 result : [

{

_id: “ABC”,

readUsers : [

ObjectId(“54f5bfb0336a15084785c393”),

ObjectId(“54f5bfb0336a15084785c392”),

ObjectId(“54f5bfb0336a15084785c398”),

ObjectId(“54f5bfb0336a15084785c365”),

ObjectId(“54f5bfb0336a15084785c370”)

]

}

 ]

}

The query I tried is like below:

db.abc.aggregate([

     {$match:{“name”:“ABC”}},

{$project:{users : {$setUnion:[“$acls.read.users”,“$acls.edit.users”,“$acls.append.users”,“$acls.fullControl.users”]}}},

{$group: {_id: “$owner”,“readuser”: “$user”}}

])

When I run the query I am getting error like:

aggregate failed: {

“errmsg” : “exception: invalid operator ‘$setUnion’”,

“code” : 15999,

“ok” : 0

}

Can anyone tell me is it possible to get the result like above and how to achieve it?

 

【Answer】

You can use MongoDB API to hardcode the concatenation of subdocuments. The problem is the logic is zigzag and the coding is difficult. In this case an easier and more intuitive way is using esProc. Here’s the esProc script for doing this:

	A
1	=mongo_open(“mongo://localhost:27017/local?user=test&password=test”)
2	=mongo_shell(A1,”test37.find()”)
3	=A2.group(name)
4	=A3.new(name,~.(acls.read.users|acls.append.users|acls.edit.users|acls.fullControl.users).(~.id()).union():readUser)
5	>mongo_close(A1)

A2:

A3: Group A2’s records by name.

A4: Concatenate values in each group into a sequence and store all these sequences under column readUser.