I'm trying to get data from 2 collections, and return one array with merge data of both collection.

The best solution for me was :

const bothValues = await ValueA.aggregate([
      { $unionWith: { coll: 'valueB' } },
      { $sort: { rank: -1, _id: -1 } },
      {
        $match: {
          isAvailable: true,
        },
      },
      { $skip: skip },
      { $limit: 30 },
    ]);

which work perfectly. But.. $unionWith was not implemented my MongoDB version (4.0.X) so I can't use it.

const bothValues = await ValueA.aggregate(
      [
        { $limit: 1 },
        {
          $lookup: {
            from: 'valueB',
            pipeline: [{ $limit: 15 }],
            as: 'valueB',
          },
        },
        {
          $lookup: {
            from: 'ValueA',
            pipeline: [{ $limit: 15 }, { $sort: { rank: -1, _id: -1 } }],
            as: 'ValueA',
          },
        },
        {
          $project:
          {
            Union: { $concatArrays: ['$valueB', '$ValueA'] },
          },
        },
        { $unwind: '$Union' },
        { $replaceRoot: { newRoot: '$Union' } },
      ],
    );

but now, I got 2 problems :

• I can't use a $skip, which is important, where use it ?
• How to use $match ?

Thanks

CodePudding user response：

Query

• your query made with some changes to work like the first query
• match in both pipelines, sort in both, (limit limitN skipN) (this way we make sure that we always have enough documents even if all are taken from valueA or valueB)
• Take sorted 70 from each, so in all ways we will have the 70 needed in the final sort/skip/limit after the union.
• concat,unwind,replace-root like in your query
• sort again (to sort the union now), skip, limit
• no matter we always have enough documents to skip
• this example query is made for skip=40 and limit=30 so in the first 2 pipelines we limit=70

db.ValueA.aggregate([
  {
    "$limit": 1
  },
  {
    "$lookup": {
      "from": "valueB",
      "pipeline": [
        {
          "$match": {
            "isAvailable": true
          }
        },
        {
          "$sort": {
            "rank": -1,
            "_id": -1
          }
        },
        {
          "$limit": 70
        }
      ],
      "as": "valueB"
    }
  },
  {
    "$lookup": {
      "from": "valueA",
      "pipeline": [
        {
          "$match": {
            "isAvailable": true
          }
        },
        {
          "$sort": {
            "rank": -1,
            "_id": -1
          }
        },
        {
          "$limit": 70
        }
      ],
      "as": "valueA"
    }
  },
  {
    "$project": {
      "union": {
        "$concatArrays": [
          "$valueA",
          "$valueB"
        ]
      }
    }
  },
  {
    "$unwind": {
      "path": "$union"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$union"
    }
  },
  {
    "$sort": {
      "rank": -1,
      "_id": -1
    }
  },
  {
    "$skip": 40
  },
  {
    "$limit": 30
  }
])