Question 115628 in Mongodb Question posted in
The official documentation can be found here.

Finding documents in mongodb collection by order of elements index of array field

Array field in collection:

"fruits": [                    "fruits": [                    "fruits": [
    {"fruit1": "banana"},          {"fruit2": "apple"},           {"fruit3": "pear"},
    {"fruit2": "apple"},           {"fruit4": "orange"},          {"fruit2": "apple"}, 
    {"fruit3": "pear"},            {"fruit1": "banana"},          {"fruit4": "orange"},
    {"fruit4": "orange"}           {"fruit3": "pear"}             {"fruit1": "banana"} 
]

I need to find those documents in collections, where "banana" signed before "apple". Does mongodb allows to compare elements in array just like :

if (fruits.indexOf('banana') < fruits.indexOf('apple')) return true;

Or maybe there is any other method to get result i need?

Tags:

2

Answers


  1. 0

    MongoDB’s array query operations do not support any positional search as you want.

    You can, however, write a $where query to do what you want:

    db.yourCollection.find({
  $where: function() { 
    return (this.fruits.indexOf('banana') < this.fruits.indexOf('apple')) 
  }
})

    Be advised though, you won’t be able to use indexes here and the performance will be a problem.

    Another approach you can take is to rethink the database design, if you can specify what it is you’re trying to build, someone can give you specific advise.

    One more approach: pre-calculate the boolean value before persisting to DB as a field and query on true / false.

    Login or Signup to reply.
  2. 0

    Consider refactoring your schema if possible. The dynamic field names(i.e. fruit1, fruit2…) make it unnecessarily complicated to construct a query. Also, if you require frequent queries by array index, you should probably store your array entries in individual documents with some sort keys to facilitate sorting with index.

    Nevertheless, it is achievable through $unwind and $group the documents again. With includeArrayIndex clause, you can get the index inside array.

    db.collection.aggregate([
  {
    "$unwind": {
      path: "$fruits",
      includeArrayIndex: "idx"
    }
  },
  {
    "$addFields": {
      fruits: {
        "$objectToArray": "$fruits"
      }
    }
  },
  {
    "$addFields": {
      "bananaIdx": {
        "$cond": {
          "if": {
            $eq: [
              "banana",
              {
                $first: "$fruits.v"
              }
            ]
          },
          "then": "$idx",
          "else": "$$REMOVE"
        }
      },
      "appleIdx": {
        "$cond": {
          "if": {
            $eq: [
              "apple",
              {
                $first: "$fruits.v"
              }
            ]
          },
          "then": "$idx",
          "else": "$$REMOVE"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      fruits: {
        $push: {
          "$arrayToObject": "$fruits"
        }
      },
      bananaIdx: {
        $max: "$bananaIdx"
      },
      appleIdx: {
        $max: "$appleIdx"
      }
    }
  },
  {
    $match: {
      $expr: {
        $lt: [
          "$bananaIdx",
          "$appleIdx"
        ]
      }
    }
  },
  {
    $unset: [
      "bananaIdx",
      "appleIdx"
    ]
  }
])

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search