MongoDb: $elemMatch with convert string to number-CodePudding

I use MongoDB and I have such a collection. First I select all documents with tableId = 1, then I group them by itemId - I get 2 groups of 2 documents. And in conclusion, I need to leave such groups where there are such documents: 1) city = London, 2) year is greater than or equal to some value. And to do this, I need to convert the value from a string to a number. How can this be done?

In my query below, the part where the year is compared is an error.

    [
      {
        "tableId": 1,
        "itemId": 10,
        "name": "City",
        "value": "London"
      },
      {
        "tableId": 1,
        "itemId": 10,
        "name": "StartYear",
        "value": "2017"
      },
      {
        "tableId": 1,
        "itemId": 20,
        "name": "City",
        "value": "Paris"
      },
      {
        "tableId": 1,
        "itemId": 20,
        "name": "StartYear",
        "value": "2018"
      },
      {
        "tableId": 2,
        "itemId": 30,
        "name": "City",
        "value": "Madrid"
      },
      {
        "tableId": 2,
        "itemId": 30,
        "name": "StartYear",
        "value": "2016"
      }
    ]

My query is:

    db.collection.aggregate([
      {
        "$match": {
          tableId: 1
        }
      },
      {
        "$group": {
          _id: {
            itemId: "$itemId"
          },
          result: {
            $push: "$$ROOT"
          }
        }
      },
      {
        "$match": {
          $and: [
            {
              "result": {
                $elemMatch: {
                  "name": "City",
                  "value": "London"
                }
              }
            },
            {
              "result": {
                $elemMatch: {
                  $and: [
                    {
                      "name": "StartYear"
                    },
                    {
                      $lte: [
                        {
                          $toDouble: "$value"
                        },
                        2018
                      ]
                    }
                  ]
                }
              }
            }
          ]
        }
      }
    ])

CodePudding user response：

Query

group and count if name/value is found and if startYear/value is found
keep those that both are found
unset the 2 temp fields cityFound,yearFound

Test code here

aggregate(
[{"$match":{"$expr":{"$eq":["$tableId", 1]}}},
 {"$group":
  {"_id":"$itemId",
   "cityFound":
   {"$sum":
    {"$cond":
     [{"$and":
       [{"$eq":["$name", "City"]}, {"$eq":["$value", "London"]}]},
      1, 0]}},
   "yearFound":
   {"$sum":
    {"$cond":
     [{"$and":
       [{"$eq":["$name", "StartYear"]},
        {"$lte":[{"$toInt":"$value"}, 2018]}]},
      1, 0]}},
   "result":{"$push":"$$ROOT"}}},
 {"$match":
  {"$expr":
   {"$and":[{"$gt":["$cityFound", 0]}, {"$gt":["$yearFound", 0]}]}}},
 {"$unset":["cityFound", "yearFound"]}])

The above is fast because it does the check while grouping.

To convert a string to number we can do $toInt or toDouble. But those are aggregate operators, and in match they should be under $expr.

The problem with your query is that you are using aggregate expressions inside a query operator, this doesn't work, for example $value is aggregate expression, also $toDouble you cant use both.

Instead of elem-match, you could use $filter that allows aggregation expressions inside, and check if after filter array would be empty.

CodePudding user response：

You can do a conditional mapping first to convert the year to a numeric value using $map first. Then perform your $elemMatch criteria.

db.collection.aggregate([
  {
    "$match": {
      tableId: 1
    }
  },
  {
    "$group": {
      _id: {
        itemId: "$itemId"
      },
      result: {
        $push: "$$ROOT"
      }
    }
  },
  {
    "$addFields": {
      "result": {
        "$map": {
          "input": "$result",
          "as": "r",
          "in": {
            "$cond": {
              "if": {
                $eq: [
                  "$$r.name",
                  "StartYear"
                ]
              },
              "then": {
                "_id": "$$r._id",
                "itemId": "$$r.itemId",
                "name": "StartYear",
                "tableId": "$$r.tableId",
                "value": {
                  $toInt: "$$r.value"
                }
              },
              "else": "$$r"
            }
          }
        }
      }
    }
  },
  {
    "$match": {
      $and: [
        {
          "result": {
            $elemMatch: {
              "name": "City",
              "value": "London"
            }
          }
        },
        {
          "result": {
            $elemMatch: {
              "name": "StartYear",
              "value": {
                $lte: 2018
              }
            }
          }
        }
      ]
    }
  }
])

Here is the Mongo playground for your reference.