MongoDB split results and merge

help MongoDB
1

Let’s say I have a collection with a Boolean field isFoo.

I want to return ALL Docs - but, I want all isFoos sorted first. Then - all non-isFoos would be after.

That simple, merely sort:{ isFoo:1} would suffice.

However, things get tricky, because I want all isFoos (already at the top), to then be sorted in an order unique to just isFoos. Then - my second set of non-isFoos will also have their own unique sort.

I’ve solved this is JS by just getting all Docs, splitting the full collection by isFoo, sorting each array, and then merging into one final one.

    let docs = Docs.find({});
    const foos = [], nonFoos = [];
    docs.forEach(doc => (doc.isfoo ? foos : nonFoos).push(doc));

    docs = [..._.sortBy(foos, ['fooSortField1', 'fooSortField2']), ..._.sortBy(nonFoos, ['nonFooSortField1', 'nonFooSortField2'])];

How can I do this in a query? Thanks!

2

You can pass in multiple sorts:

sort: {
  isFoo: 1,
  secondSortField: 1
}

If you have a field (e.g., fooOnlyField) that is unique to isFoo == true and another (e.g., notFooOnlyField) field that is unique to isFoo == false - you could pass in all three fields to sort, then isFoo documents would be sorted by fooOnlyField and !isFoo documents would be sorted by notFooNlyField

3

This was the first thing I tried, but then realized it affects the sort of the nonFoos.

i.e.

sort: {
  isFoo: -1, //bc I want all Foos at top
  fooSortField1: 1,
  fooSortField2: 1,
   
  nonFooSortField1: 1,
  nonFooSortField2: 1

}

This doesn’t work bc then my nonFoos are sorted by fooSortField1&2. I just want the second set (nonFoos) to be sorted by nonFooSortField1&2. So it’s conditional unfortunately.

Fortunately, all fields exist on every document. I just want “All foos at top. And…within all foos…sort by fooSortField1 asc …then fooSortField2 asc. Then - all nonFoos. Sorted by nonFooSortField1 asc and then nonFooSortField2…”

I hope I’m making sense

4

In this case I don’t believe you can do it. Technically you could probably do it with an aggregation, but there isn’t a lot of point, and it wouldn’t be reactive. I’d probably issue two queries, one to get the sorted foos, one to get the sorted non-foos, then concat them. That way no _.sortBy is required.

docs = [
...Docs.find({ isFoo: true }, { sort: { fooField1: 1, fooField2: 1 } }),
...Docs.find({ isFoo: false }, { sort: { notFoo1: 1, notFoo2: 1 } })
]
1 Like
5

Figured I’d need an aggregate. Using rawCollection's aggregate isn’t reactive?

Since your solution still uses two cursors, I’d still get reactivity, right?

6

(Also to @znewsham)

You can use tunguska:reactive-aggregate. However, make sure you install version 1.3.3 rather than it’s current 1.3.5 due to a bug just recently discovered in that version.

7

v1.3.6 was released yesterday which reverts the change which introduced the bug. Normal service is resumed :slightly_smiling_face:

5 Likes
8

I think I may use this package. Any ideas on how to achieve this with an aggregate though? I spent some time fumbling around with MongoDB Compass GUI but can’t get what I need. I looked into $merge and $out but I think that’s overkill. I’m not trying to output to another collection. I basically just need something like SQL union.

Looks like $union is made for two separate collections though. I’m querying the same one.

9

What you are looking for is the $unionWith stage. It would look something like that:

const results = Docs.rawCollection().aggregate([
  { $match: { isFoo: true } },
  { $sort: { fooA: 1, fooB: 1 } },
  {
    $unionWith: {
      coll: Docs._name, // Collection name in the DB.
      pipeline: [
        { $match: { isFoo: false } },
        { $sort: { notFooA: 1, notFooB: 1 } },
      ],
    },
  },
]);

EDIT: It can be simplified (without performance hit):

const results = Docs.rawCollection().aggregate([
  // Make sure there will be no results.
  { $limit: 1 },
  { $skip: 1 },

  // $unionWith all queries.
  {
    $unionWith: {
      coll: Docs._name, // Collection name in the DB.
      pipeline: [
        { $match: { isFoo: true } },
        { $sort: { fooA: 1, fooB: 1 } },
      ],
    },
  },
  {
    $unionWith: {
      coll: Docs._name, // Collection name in the DB.
      pipeline: [
        { $match: { isFoo: false } },
        { $sort: { notFooA: 1, notFooB: 1 } },
      ],
    },
  },
]);
1 Like
10

$unionWith isn’t even an operator in MongoDB Compass lol. I’ll have to try this in the CLI (tends to be a pain bc it’s hard to type out )

Update: Meteor Mongo CLI gives me "errmsg" : "Unrecognized pipeline stage name: '$unionWith'",
Meteor instance is running on Mongo v4.0.2 btw

11

The $unionWith pipeline stage is fairly new - it was added in Mongo 4.4.

1 Like
12

Ah, that explains it. Any workarounds for Mongo 4.0.2?

13

You could do a $facet with $match+$sort in separate branches, but I’m pretty sure it’s going to be slower - do test it though, it may work for your case.

1 Like
14

I’ll look into. Any reason I shouldn’t just update Meteor Mongo to the latest? Any risk of regression ? Thanks !

15

You cannot upgrade the MongoDB used in Meteor development mode. At least as far as I know.

But if you’d use a dockerized one or the one in your staging/production environment - no, not at all. Always check the change log, but in general MongoDB updates are forward-compatible.

1 Like