nodejs/mongodb - Aggregate items by substring

Question

I have two related collections that contain documents as follows:

/* heroes */
{ id: "HID_1", name: "A" }
{ id: "HID_2", name: "B" }

/* weapons */
{ name: "WHID_1", weapon: "Sword" }
{ name: "WHID_2", weapon: "Lance" }

How can I aggregate them so I get a single document where I know "A" uses a Sword and "B" uses a Lance? I can't directly join them by id and name because their value isn't exactly the same, but Weapon has a W-prefix on it.

I made some attempts with $substr but no success so far.

db.heroes.aggegate( [
  {
    $lookup: {
      from: 'weapons',
      let: { heroId: '$id' },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [ '$$heroId', { $substr: [ '$name', 1, -1 ] } ]
            }
          }
        }
      ],
      as: 'weapon'
    }
  }
] )

For reference, I also tried just hard-coding an ID with { $match: { $expr: { $eq: [ '$$heroId', 'HID_1' ] } } } and it didn't work. I could just rename all WHID to HID, but I am curious about whether it is possible or not.

I dont see any problem you are facing https://mongoplayground.net/p/-aZcXbidTzT — varman, Aug 05 '21 at 02:40
It was a silly mistake of mine while copying the query, thank you for pointing it out. — bitomic, Aug 05 '21 at 03:17

score 0 · Answer 1 · answered Aug 05 '21 at 02:33

0

Use $project to append the "W" to the heroID and then do a regular lookup like described here: https://stackoverflow.com/a/46969468

answered Aug 05 '21 at 02:33

alfonso

1

score 0 · Answer 2 · answered Aug 05 '21 at 03:16

I am laughing so hard right now, the query I posted is not the same I have in my code, and apparently I fixed it without knowing while I was copying it into the question. My let was wrong and defined weapons.name instead of heroes.id.

For anyone having a similar issue, the aggregate in the original post works as it should. I didn't notice it until @varman pointed it out, so thank you! And sorry for the silly mistake.

Selva Mary · Answer 3 · 2021-08-05T04:14:40.090

Try this...

db.heroes.aggregate([
  {
    $project: {
      _id: 1,
      name: 1,
      newID: {
        $concat: [
          "W",
          "$_id"
        ]
      }
    }
  },
  {
    "$lookup": {
      "from": "weapons",
      localField: "newID",
      foreignField: "name",
      "as": "data"
    }
  },
  {
    $unwind: "$data"
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$data",
          "$$ROOT"
        ]
      }
    }
  },
  {
    $project: {
      data: 0,
      newID: 0
    }
  }
])

or

db.heroes.aggregate([
  {
    $lookup: {
      from: "weapons",
      let: {
        heroId: "$id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$heroId",
                {
                  $substr: [
                    "$name",
                    1,
                    -1
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "data"
    }
  },
  {
    $unwind: "$data"
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$data",
          "$$ROOT"
        ]
      }
    }
  },
  {
    $project: {
      data: 0
    }
  }
])

output:

[
  {
    "_id": "HID_1",
    "name": "A",
    "weapon": "Sword"
  },
  {
    "_id": "HID_2",
    "name": "B",
    "weapon": "Lance"
  }
]

Mongoplayground

nodejs/mongodb - Aggregate items by substring

3 Answers3