How to union from two different collections in Mongodb?

Question

I have two tables like this

Table 1:

CREATE TABLE `table_1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `status` int(11) NOT NULL,
  `date_added` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table_1` (`id`, `name`, `status`, `date_added`) VALUES
(1, 'CNN', 1, '2018-07-01 00:00:01'),
(2, 'BBC', 1, '2018-07-03 00:00:01'); 

Table 2:

CREATE TABLE `table_2` (
  `id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `status` int(11) NOT NULL,
  `date_added` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table_2` (`id`, `title`, `status`, `date_added`) VALUES
(1, 'Windows', 1, '2018-07-02 00:00:01'),
(2, 'Linux', 1, '2018-07-04 00:00:01'); 

My SQL Query is this

SELECT * FROM
( 
 SELECT id, name, date_added FROM `table_1` 
 UNION 
 SELECT id, title as name, date_added FROM `table_2` 
) 
as new_table order by date_added ASC

I want result as follows:

id  name    date_added Ascending 1 

1   CNN     2018-07-01 00:00:01
1   Windows 2018-07-02 00:00:01
2   BBC     2018-07-03 00:00:01
2   Linux   2018-07-04 00:00:01

I am not able to get the result like this in mongodb.

Answer 1

After research more i am able to find the solution with union in mongodb as follows:

db.table_1.aggregate([
    { 
    $lookup: {
        from: "table_2",
        pipeline: [],
        as: "table_2"
    }
    },
    {
    $addFields: {
        table_2: {
          $map: {
               input: "$table_2",
               as: "tbl2",
               in: { 
                "_id":"$$tbl2._id", 
                "type": "table_2", 
                "title": "$$tbl2.title", 
                "status": "$$tbl2.status",
                "date_added": "$$tbl2.date_added", 
               }
            }
        }
    }
    },
    {
    $group: {
        _id: null,
        table_1: {
            $push: {
                _id:"$_id",
                type: "table_1", 
                name: "$name",
                status: "$status",
                date_added: "$date_added"
            }
        },
        table_2: {
            $first: "$table_2"
        }
    }
    },
    {
    $project: {
        items: {
            $setUnion: ["$table_1", "$table_2"]
        }
    }
    },
    {
    $unwind: "$items"
    },
    {
    $replaceRoot: {
        newRoot: "$items"
    }
    },
    {
    '$match': { status: true} 
    },
    { '$sort': { date_added: -1 } },
    { '$limit': 10 } 
])

Answer 2

Since mongo 4.4, there is the unionWith operator which makes it as simple as

db.table_1.aggregate( [
   { $set: { _id: "table_1" } },
   { $unionWith: { coll: "table_2", pipeline: [ { $set: { _id: "table_2" } } ] } },
   { $unionWith: { coll: "table_3", pipeline: [ { $set: { _id: "table_3" } } ] } },
   { $unionWith: { coll: "table_4", pipeline: [ { $set: { _id: "table_4" } } ] } },
] )