6

I face this challenge:

Retrieve documents sorted by field A if field B exists/is not null. Otherwise sort by field C.

In a SQL world, I would do two queries and create a UNION SELECT, but I have no idea how to start with Mongo.

Is map/reduce the correct way to go? Or should I focus on "computed field" and use this one. I am relatively new to MongoDB and I am asking for directions.

Edit: As requested, here some sample data:

Given:

|     ID     | FieldA | FieldB | FieldC |
|------------|--------|--------|--------|
| Document 1 |     10 | X      |     40 |
| Document 2 |     20 | <null> |     50 |
| Document 3 |     30 | Z      |     60 |

Expected result (the order) including column with calculation as comment

|     ID     | FieldA | FieldB | FieldC | "A" if "B" !=<null> else "C" |
|------------|--------|--------|--------|------------------------------|
| Document 1 |     10 | X      |     40 |                           10 |
| Document 3 |     30 | Z      |     60 |                           30 |
| Document 2 |     20 | <null> |     50 |                           50 |

Thank you, schube

schube
  • 652
  • 5
  • 18
  • Please provide some test data and code of what you have tried so far. – mbuechmann Feb 21 '19 at 15:34
  • I am using two queries now and I am collecting the documents in an array in Java. In MongoDB, I read about map/reduce and computed fields, but I am not sure which of these two routes (or maybe a third route) I should follow. This is the reason I am asking. I have no mongo query code yet. – schube Feb 21 '19 at 16:00
  • Some test data and the expected output would be helpful, though. Could you provide that in your question? – mbuechmann Feb 21 '19 at 17:43
  • I added sample data, as requested. Hope, my question is clearer now. Thanks! – schube Feb 21 '19 at 19:10

1 Answers1

13

Given the following documents:

{ "a": 10, "b": "X",  "c" : 40 }
{ "a": 20, "b": null, "c" : 50 }
{ "a": 30, "b": "Z",  "c" : 60 }

One way of doing this would be like so:

db.collection.aggregate({
    $addFields: {
        "sortField": { // create a new field called "sortField"
            $cond: { // and assign a value that depends on
                if: { $ne: [ "$b", null ] }, // whether "b" is not null
                then: "$a", // in which case our field shall hold the value of "a"
                else: "$c" // or else it shall hold the value of "c"
            }
        }
    }
}, {
    $sort: {
        "sortField": 1 // sort by our computed field
    }
}, {
    $project: {
        "sortField": 0 // remove "sort" field if needed
    }
})

If you had a document without a b field as in:

{ "a": 20, "c" : 50 }

then you'd need to apply one of the techniques mentioned here.

So your if part inside the $cond could e.g. look like this:

if: { $ne: [ "$b", undefined ] }, // whether "b" is null or doesn't exist at all
dnickless
  • 10,733
  • 1
  • 19
  • 34