MongoDB aggregate query - How to make array of array list in to single array

Question

I have a document like this: this is my example data is attached below,

[
    {
        "_id": ObjectId("6218b836405919280c209f7e"),
        "projectId": ObjectId("6218a31f405919280c209e18"),
        "accountId": ObjectId("621888e852bd8836c04b8f82"),
        "personalIdRoot": [
            {
                "_id": ObjectId("6221e7514195b43f24c9953f"),
                "personalId": ObjectId("6218b48c405919280c209f6c"),
            },
            {
                "_id": ObjectId("6221e7514195b43f24c99540"),
                "personalId": ObjectId("621ef1e40bd3a220f487cd96"),
            }
        ],
        "personalIdFill": [
            {
                "_id": ObjectId("6221e7514195b43f24c9953d"),
                "personalId": ObjectId("6218b48c405919280c209f6c"),
            },
            {
                "_id": ObjectId("6221e7514195b43f24c9953e"),
                "personalId": ObjectId("621ef1e40bd3a220f487cd96"),
            }
        ],
        "personalIdCap": [
            {
                "_id": ObjectId("6221e7514195b43f24c9953b"),
                "personalId": ObjectId("6218b48c405919280c209f6c"),
            },
            {
                "_id": ObjectId("6221e7514195b43f24c9953c"),
                "personalId": ObjectId("621ef1e40bd3a220f487cd96"),
            }
        ],
        "aps": ObjectId("6218bc18405919280c209f8e"),
    }
]

i used this example data for my aggregate query. my aggregate query is attached below: please find below code:

db.getCollection('funds').aggregate([
    {
        $match: {
            accountId: ObjectId("621888e852bd8836c04b8f82"),
            projectId: ObjectId("6218a31f405919280c209e18"),
            aps: {
                $in: [
                    ObjectId("6218bc18405919280c209f8e")
                ]
            }

        }
    },
    {
        $facet: {
            "results": [
                {
                    $group: {
                        _id: 0,
                        values: { "$addToSet": "$personalIdRoot.welderId" },
                        values: { "$addToSet": "$personalIdFill.welderId" },
                        values: { "$addToSet": "$personalIdCap.welderId" }
                    }
                },
            ],
        }
    }
])

my result: 

/* 1 */
{
    "results" : [ 
        {
            "_id" : 0.0,
            "values" : [ 
                [ 
                    ObjectId("6218b48c405919280c209f6c")
                ], 
                [ 
                    ObjectId("6218b2e4405919280c209f68")
                ], 
                [ 
                    ObjectId("6218b48c405919280c209f6c"), 
                    ObjectId("621ef1e40bd3a220f487cd96")
                ]
            ]
        }
    ]
}

But i need my result in a single query:


/* 1 */
{
    "results" : [ 
        {
            "_id" : 0.0,
            "values" : [ 
                    ObjectId("6218b48c405919280c209f6c"),
                    ObjectId("621ef1e40bd3a220f487cd96")
            ]
        }
    ]
}

I have a result of array of array collections. but i need it in a single array. like above result

Thanks in advance.

nimrod serok · Accepted Answer · 2022-06-16 06:44:27Z

1

It sounds like what you want is:

db.collection.aggregate([
  {
    $match: {
      accountId: ObjectId("621888e852bd8836c04b8f82"),
      projectId: ObjectId("6218a31f405919280c209e18"),
      aps: {
        $in: [
          ObjectId("6218bc18405919280c209f8e")
        ]
      }
    }
  },
  {
    $project: {
      values: {
        $setUnion: [
          "$personalIdCap.personalId",
          "$personalIdFill.personalId",
          "$personalIdRoot.personalId"
        ]
      }
    }
  }
])

See how it works on the playground example

answered Jun 16, 2022 at 6:44

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rohit tdr Over a year ago

Thanks for the reply. But i need every id in a single array with unioque values. But your code shows in seperate ids

nimrod serok Over a year ago

Your question is unclear. Your data does not match your expected result. Please update your question. There is no ObjectId("6218b2e4405919280c209f68") in your sample data

Rohit tdr Over a year ago

okay i edit the result . I dont need this ObjectId

Collectives™ on Stack Overflow

MongoDB aggregate query - How to make array of array list in to single array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related