Unable to find out the error in the mongo DB union with query-CodePudding

I am new to mongo db, here I am trying to convert mysql query to mongo db query.

Here, I have to do just union of some fields from 2 collections in mongodb without any conditions. No common fields are present in both collections. Below mentioned my query, I am able to see output of only "account_id","account_name" and concatination. I am unable to see output of which I am trying to project inside union query is not showing in output. Can anyone please help me on this.

db.accounts.aggregate(
        {
          "$unionWith": {
            "coll": "parent_child", "pipeline": [
              {
                "$project": {
                  "child_id":"$CHILD_ID",
                  "name":"$NAME"
              }
            }
            ]
          }
        },
        {
          "$project":{
            "account_id":"$ACCOUNT_ID",
            "name":"$ACCOUNT_NAME"
          }
        }
      )

For your understanding, I am posting mysql query.

select p.CHILD_ID as account_id,p.NAME as name from parent_child p union select a.ACCOUNT_ID as account_id,a.ACCOUNT_NAME as name from account a

Providing sample records of both collections:

1.accounts

[{account_id:1,
  name:"abc"},
  {account_id:2,
   name:"test"}]

2. parent_child:

[{CHILD_ID:23
  NAME:"test child"},
 [CHILD_ID:34,
  NAME:"test1]}

Expected Output:

[{account_id:1,
  name:"abc"},
 {account_id:2,
  name:"test"},
 {account_id:21,
  name:"test child"},
 {account_id:34,
  name:"test2"}]

CodePudding user response：

If I understand correctly, it can be done, but it is not recommended, as it groups all the documents from two collections into one big document:

db.accounts.aggregate([
  {
    $group: {
      _id: 0,
      accounts: {$push: {account_id: "$account_id", name: "$name"}}
    }
  },
  {
    $lookup: {
      from: "parent_child",
      let: {},
      pipeline: [
        {$project: {account_id: "$CHILD_ID", name: "$NAME", _id: 0}}
      ],
      as: "parent_child"
    }
  },
  {
    $project: {
      res: {$concatArrays: ["$accounts", "$parent_child"]},
      _id: 0
    }
  }
])

See how it works on the playground example