Mongo db aggregate query using $project,$setUnion,$group

[Soorya Prakash]

Hi, 

 i am trying to use mongodb aggregate query using $setUnion, $project and also $group to group the documents. The structure of document 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 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 possiible to get the result like above and how to achieve it?

[Asya Kamsky]

What version are you using?

There is no problem with $setUnion except it was introduced in 2.6 -
are you on 2.4 or older?

You do have a problem in your $group in that your grouping isn't
grouped quite right - do you mean something like:

{$group: { _id: {owner: "$owner","readuser": "$users"}}}

Asya

> --
> You received this message because you are subscribed to the Google Groups
> "mongodb-user"
> group.
>
> For other MongoDB technical support options, see:
> http://www.mongodb.org/about/support/.
> ---
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to mongodb-user...@googlegroups.com.
> To post to this group, send email to mongod...@googlegroups.com.
> Visit this group at http://groups.google.com/group/mongodb-user.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/mongodb-user/fcbfe828-f4d7-4835-a726-4583c3f5e76a%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
MongoDB World is back! June 1-2 in NYC. Use code ASYA for 25% off!

[Soorya Prakash]

Yes, i am using version 2.4. It was not working as expected.

[Asya Kamsky]

Unfortunately you won't be able to use $setUnion till you upgrade to 2.6 then.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/cba2a179-8cd2-4728-bb75-ad882f125451%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Soorya Prakash]

ok thanks. I will upgrade and test it

To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.

To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/cba2a179-8cd2-4728-bb75-ad882f125451%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Soorya Prakash]

If i upgrade to 2.6 , what are the possibilities of breaking the existing functionality in older mongodb version.

[Stephen Steneker]

On Tuesday, 17 March 2015 16:33:03 UTC+11, Soorya Prakash wrote:

If i upgrade to 2.6 , what are the possibilities of breaking the existing functionality in older mongodb version.

Hi Soorya,

You should always test any major upgrade thoroughly in a representative staging/QA environment.

For compatibility changes and upgrade instructions see the documentation:
 http://docs.mongodb.org/manual/release-notes/3.0-compatibility/

 http://docs.mongodb.org/manual/release-notes/3.0-upgrade/

Regards,

Stephen

[Machine Calculate]

You need to hardcode to merge the subdocuments with MongoDB API. The code is unnatural and difficult to write. But you can solve the problem in esProc. The following is the code:

A1=MongoDB("mongo://localhost:27017/local?user=test&password=test")

A2=A1.find("c1",,"{_id:0}";"{name:1}")

A3= for A2;name

B3= A3.(acls.read.users|acls.append.users|acls.edit.users|acls.fullControl.users)

B4=new(A3.name:_id,B3.union().id():readUsers)

B5=B2=B2|B4

See code explanation in https://dataccelerator.wordpress.com/2015/04/13/merge-mongodb-documents-in-esproc/ .