Aggregate Querry with Array field

Below is structure of our collection with 2 array fields rms and ams in each collection (showed 2 instances of collection here)

associations: [
{
   "_id":{
      "$oid":"60db82fbce48b83d522bf2f4"
   },
   "name":"AS1",
   "rms":[
      {
         "name":"rms1",
         "_id":"rmsid1"
      },
      {
         "name":"rms2",
         "_id":"rmsid2"
      },
   ]
}

{
   "_id":{
      "$oid":"60db82fbce48b83d522bf2f4"
   },
   "name":"AS2",
   "rms":[
      {
         "name":"rms1",
         "_id":"rmsid1"
      },
      {
         "name":"rms4",
         "_id":"rmsid4"
      }
   ]
}
]

We are trying to get distinct array elements across all collection

db.associations.aggregate([{"$group":{"_id":{"_id":"$rms._id",“name”:"$rms.name"}}}])

We are getting as below:

        [
           {
                 "name":["rms1","rms2"]
                 "_id":["rmsid1","rmsid2"]
            },
           {
                 "name":["rms2","rms4"]
                 "_id":["rmsid2","rmsid4"]
            }
        ]

we would like to get result as below

    [
           {
                 "name":"rms1",
                 "_id":"rmsid1"
            },
           {
                 "name":"rms2",
                 "_id":"rmsid2"
            },
           {
                 "name":"rms4",
                 "_id":"rmsid4"
            }
        ]

how to get unique objects across all arrays in collection?

I don’t think this question is related to the course M121 from MongoDB University.

I have redirected your post to a section that seems more appropriate and will give you better luck in getting a better answer.

can you send me link, thanks

Hello and welcome : )

Your group is fine , but you need an unwind first.
One solution tested is the bellow

Tested on

Docs(your docs,with small changes,for example you had the same _id)

[
           {
            "_id" {
                   "oid" "60db82fbce48b83d522bf2f4"
                   },
            "name" "AS1",
            "rms" [
                   {
                    "name" "rms1",
                    "_id" "rmsid1"
                    },
                   {
                    "name" "rms2",
                    "_id" "rmsid2"
                    },
                   ]
            }

           {
            "_id" {
                   "oid" "60db82fbce48b83d522bf2f5"
                   },
            "name" "AS2",
            "rms" [
                   {
                    "name" "rms1",
                    "_id" "rmsid1"
                    },
                   {
                    "name" "rms4",
                    "_id" "rmsid4"
                    }
                   ]
            }
           ]

Query-pipeline(add some commas and : to make it valid json)

[{"$unwind" {"path" "$rms"}}
  {"$group" {"_id" {"name" "$rms.name", "_id" "$rms._id"}}}
  {"$replaceRoot" {"newRoot" "$_id"}}]

Results (add some : to make it valid json)

{"name" "rms1", "_id "rmsid1"}
{"name" "rms4", "_id "rmsid4"}
{"name" "rms2", "_id "rmsid2"}
1 Like

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