Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-7528

Order of responses to a MongoDB $in query

    Details

    • Backwards Compatibility:
      Fully Compatible

      Description

      I have a use-case where I am querying an array of ObjectIDs. For example:

      db.things.find({
      _id:

      { $in: [ ObjectID(...), ObjectID(...), ObjectID(...), ObjectID(...) ] }

      })

      I'd like the results to be ordered by `_id.$in`. Right now I'm resorting to client-side sorting, which kind of sucks since it requires converting between ObjectIDs and strings among other hacks.

      A special sorting option like this would be awesome (similar to $natural):

      db.things.find({}).sort({
      $in:

      { '_ids' }

      })

      Although the attribute being `$in`ed need not be `_id`, it should be unique (and thus indexed).

      Some related questions on StackOverflow:

      http://stackoverflow.com/questions/3142260/order-of-responses-to-mongodb-in-query
      http://stackoverflow.com/questions/11839515/comparing-and-sorting-mongodb-objectids-in-node-convert-to-string

        Issue Links

          Activity

          Hide
          jonathanong Jonathan Richard Ong added a comment -

          What's the recommended way to do this now?

          Show
          jonathanong Jonathan Richard Ong added a comment - What's the recommended way to do this now?
          Hide
          vivekgounder Vivek Gounder added a comment -

          Doing it in the client makes it very odd and strange indeed. It would be lovely if we had the server side implementation for the clients to have a sorting option while using the in query.

          Show
          vivekgounder Vivek Gounder added a comment - Doing it in the client makes it very odd and strange indeed. It would be lovely if we had the server side implementation for the clients to have a sorting option while using the in query.
          Hide
          amcgregor Alice Bevan-McGregor added a comment - - edited

          I, too, am looking forward to this. Sure, I could batch retrieve the document set and assign to a dictionary (in Python), then re-iterate the value I passed to $in, but this is a poor solution for anything but trivial numbers of results. A streaming solution (since I typically use generators for my $in values, too) is greatly superior.

          Due to the existing sorting syntax, I'd recommend the following form:

          {'$in': 1}
          

          With potentially mixed usage:

          {age: -1, '$in': 1}
          

          This would imply a limitation of only one $in filter to satisfy the query in this way, similar to the current $ limitation. This is acceptable to me, possibly not to others, but importantly, does conform to the existing syntax.

          Edited to add: explicit lookup of documents by ID in the pre-arranged order is a specialization of the general form I suggest. Because it's so specialized (single, automatic index, no other possible ordering) it may be worthwhile to implement as a distinct find command. findSpecific or findById or similar. Also likely easier to implement as such.

          Show
          amcgregor Alice Bevan-McGregor added a comment - - edited I, too, am looking forward to this. Sure, I could batch retrieve the document set and assign to a dictionary (in Python), then re-iterate the value I passed to $in , but this is a poor solution for anything but trivial numbers of results. A streaming solution (since I typically use generators for my $in values, too) is greatly superior. Due to the existing sorting syntax, I'd recommend the following form: { '$in' : 1} With potentially mixed usage: {age: -1, '$in' : 1} This would imply a limitation of only one $in filter to satisfy the query in this way, similar to the current $ limitation. This is acceptable to me, possibly not to others, but importantly, does conform to the existing syntax. Edited to add: explicit lookup of documents by ID in the pre-arranged order is a specialization of the general form I suggest. Because it's so specialized (single, automatic index, no other possible ordering) it may be worthwhile to implement as a distinct find command. findSpecific or findById or similar. Also likely easier to implement as such.
          Hide
          rahbari Ali Rahbari added a comment - - edited

          This is really a necessary feature. For example in a system where user can feature items, they must see them in the order they featured them. In SQL days this was done using a join so you wouldn't actually care about in. I believe Mongodb must preserve the $in order unless an order by clause is present. Doing this client side would be so frustrating considering we must do the sort as well as paging!

          Show
          rahbari Ali Rahbari added a comment - - edited This is really a necessary feature. For example in a system where user can feature items, they must see them in the order they featured them. In SQL days this was done using a join so you wouldn't actually care about in. I believe Mongodb must preserve the $in order unless an order by clause is present. Doing this client side would be so frustrating considering we must do the sort as well as paging!
          Hide
          asya Asya Kamsky added a comment -

          In upcoming 3.4 this is possible to do with an aggregation pipeline.

          I write it up here: http://www.kamsky.org/stupid-tricks-with-mongodb/using-34-aggregation-to-return-documents-in-same-order-as-in-expression

          Basically, it's a matter of adding a field with order of matched field in the array that specified sort order.

          Using initial example from this ticket:

          order=[ ObjectID(...), ObjectID(...), ObjectID(...), ObjectID(...) ];
          db.things.aggregate({$match:{_id:{$in:order}}}, {$addFields:{__order: { $indexOfArray : [ order, "$name" ]}}}, {$sort:{ "__order":1}});
          

          Show
          asya Asya Kamsky added a comment - In upcoming 3.4 this is possible to do with an aggregation pipeline. I write it up here: http://www.kamsky.org/stupid-tricks-with-mongodb/using-34-aggregation-to-return-documents-in-same-order-as-in-expression Basically, it's a matter of adding a field with order of matched field in the array that specified sort order. Using initial example from this ticket: order=[ ObjectID(...), ObjectID(...), ObjectID(...), ObjectID(...) ]; db.things.aggregate({$match:{_id:{$in:order}}}, {$addFields:{__order: { $indexOfArray : [ order, "$name" ]}}}, {$sort:{ "__order":1}});

            People

            • Votes:
              21 Vote for this issue
              Watchers:
              22 Start watching this issue

              Dates

              • Created:
                Updated: