I have the following model in mongodb:

{
    sport: 'Football',
    site: 'Bet365',
    data: [
        {
            _id: '1',
            rating: 98.12,
            bets: [
                {
                    exchange: 'Smarkets',
                    odds1: 3.2,
                    odds2: 3.3
                },
                {
                    exchange: 'Matchbook',
                    odds1: 1.5,
                    odds2: 1.52
                },
                {
                    exchange: 'Betfair',
                    odds1: 2.0,
                    odds2: 2.1
                },
            ]
        },
        {
            _id: '2',
            rating: 99.50,
            bets: [
                {
                    exchange: 'Smarkets',
                    odds1: 2.1,
                    odds2: 2.12
                },
                {
                    exchange: 'Smarkets',
                    odds1: 1.4,
                    odds2: 1.4
                },
                {
                    exchange: 'Smarkets',
                    odds1: 2.0,
                    odds2: 2.05
                },
            ]
        },
    ]
}

Is there a query I can make to this to only get the embedded objects in the data array if every exchange in the bets array matches the one we're using as a filter.

For example, I want something like the query below, but I only want it to return the object with '_id' = '2' as that is the only object where every exchange is 'Smarkets'.

db.find('data.bets.exchange': 'Smarkets') 
// how do I change this to only get the object with _id '2'?

CodePudding user response：

You can try an aggregation query,

• $match stage to match your query
• $addFields to edit/add new fields
• $filter to iterate loop of data array
• $eq to match filtered exchange and current bets.exchange are equal then return result

db.collection.aggregate([
  { $match: { "data.bets.exchange": "Smarkets" } },
  {
    $addFields: {
      data: {
        $filter: {
          input: "$data",
          cond: {
            $eq: [
              "$$this.bets.exchange",
              {
                $filter: {
                  input: "$$this.bets.exchange",
                  cond: { $eq: ["Smarkets", "$$this"] }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

You can also write this filter operation in find query as well, aggregation projection starting from MongoDB 4.4,

db.collection.find(
  { "data.bets.exchange": "Smarkets" },
  {
    // write other recommended fields here for result
    data: {
      $filter: {
        input: "$data",
        cond: {
          $eq: [
            "$$this.bets.exchange",
            {
              $filter: {
                input: "$$this.bets.exchange",
                cond: { $eq: ["Smarkets", "$$this"] }
              }
            }
          ]
        }
      }
    }
  }
)

Playground

CodePudding user response：

Query

• from data keep only the members
• that set diffrerence [exchange1 exchange2 ...] - ["Smarkets"] = []
  (difference is zero only if all exchange1,.. are equal to "Smarkets")
• if data became empty(no member pass) filter out the document

Test code here

*if you have a multikey index on "data.bets.exchange" add this stage above the $set also, if you dont have this index, it will make the query slower, so dont add it
{ $match: { "data.bets.exchange": "Smarkets" } }

db.collection.aggregate([
  {
    "$set": {
      "data": {
        "$filter": {
          "input": "$data",
          "cond": {
            "$eq": [
              {
                "$setDifference": [
                  "$$this.bets.exchange",
                  [
                    "Smarkets"
                  ]
                ]
              },
              []
            ]
          }
        }
      }
    }
  },
  {"$match": {"data": {"$ne": []}}}
])