Uploaded image for project: 'Documentation'
  1. Documentation
  2. DOCS-382

Explain in $or query operation documentation when using $in might be optimal

    XMLWordPrintableJSON

Details

    • Icon: Improvement Improvement
    • Resolution: Done
    • Icon: Minor - P4 Minor - P4
    • Server_Docs_20231030
    • None
    • manual
    • None

    Description

      This is being created due to the linked ticket. I feel the problem is common (and simple) enough to warrant a small docs update.

      People will sometimes want to literally translate the SQL clause:

      WHERE a = 5 OR a = 6

      into

      {$or: [{a: 5}, {a: 6}]}

      instead of using the `$in` operator. Furthermore they'll follow the same logic to translate:

      WHERE (a = 5 OR a = 6) AND (b = 7 OR b = 8)

      into

      {$and: [{$or: [{a: 5}, {a: 6}]},
              {$or: [{b: 7}, {b: 8}]}]}

      because they know queries are (generally implemented as) a map and there can't be two distinct values for the same key.

      Using the `$in` operator really shines in this case to simplify the query to:

      {a: {$in: [5, 6]},
       b: {$in: [7, 8]}}

      Specifically speaking, the `$in` operator should be used as a special case of `$or` where the key being checked is always the same.

      Speaking more generally, there may be enough oddities trying to translate directly from SQL to Mongo's query language that warrants a quick page of tips for those with an existing strong SQL querying experience.

      Attachments

        Activity

          People

            kay.kim@mongodb.com Kay Kim (Inactive)
            daniel.gottlieb@mongodb.com Daniel Gottlieb (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              11 years, 22 weeks, 6 days ago