Parse-server: Aggregation $lookup problem

[joshkopecek]

Here we go again with my aggregation issues…

Issue Description

I want to do an aggregation query with a $lookup against a separate table, joining by _id/objectId, but because of the way Parse stores the relations (_User$0034nkjdfn) I'm not able to do an equality match between localField and foreignField.

Steps to reproduce

Collection MyPlant has a field user (_p_user) which is a pointer to the _User table
When I do an aggregate with the following pipeline:

myPlantQuery.aggregate([
        {
          lookup: {
            from: '_User',
            localField: '_p_user',
            foreignField: '_id',
            as: 'users'
          }
        }
], { useMasterKey: true })

it's trying to match the _p_user: "_User$20gruknw98g4n" with _id: "20gruknw98g4n" which of course is not going to match.

Is there any way around this?

Expected Results

Return a set of users in the 'users' field in the next pipeline stage.

Actual Outcome

Returns no users in the next pipeline stage.

Environment Setup

• Server

  • parse-server version: 2.8.1
  • Operating System: MacOS
  • Hardware: MacBook Pro 13 Inch Mid 2012
  • Localhost or remote server? (AWS, Heroku, Azure, Digital Ocean, etc): localhost

• Database

  • MongoDB version: 3.6
  • Storage engine: WiredTiger
  • Hardware: Same as above
  • Localhost or remote server? (AWS, mLab, ObjectRocket, Digital Ocean, etc): localhost

Logs/Trace

n/a

[joshkopecek]

I also discovered a second error with aggregation:
If you specify an object for match

{
   match: {
      user: {
        $in: myPlantUsers.map(myPlantUser => myPlantUser.id)
      }
   }
},

Parse ends up making "_User$[object Object]" as the pipeline for $match

[dplewis]

check this out https://github.com/parse-community/parse-server/issues/1908#issuecomment-371589760

hope it helps

[dplewis]

@flovilmart on a side note. This is kinda related to my comment https://github.com/parse-community/parse-server/pull/4743#issuecomment-386004113

Handling all paths will be a lot of work (recursive I dislike you). For instance $lookup will be up to the user.

https://github.com/parse-community/parse-server/pull/4743 needs some clean up but it does handle the 3 main paths which is a high priority. Can you look at it?

[oallouch]

You can also check my example at https://github.com/parse-community/parse-server/issues/4682

[joshkopecek]

@dplewis @oallouch amazing, thanks so much for this!

[joshkopecek]

@dplewis @oallouch I can confirm this is working. Got a reasonable aggregation pipeline here:

const pipeline = [
  {
    $project: {
      userPointer: { $substr: ["$_p_user", 6, -1] },
      _p_plant: 1
    }
  },
  {
    $lookup: {
      from: "_User",
      localField: "userPointer",
      foreignField: "_id",
      as: "tempUserPointer"
    }
  },
  { $unwind: "$tempUserPointer" },
  {
    $match: {
      "tempUserPointer._p_currentRetailersGroup": /irtuvhei7dn$/
    }
  },
  {
    $group: {
      _id: "$_p_plant",
      count: { "$sum": 1 }
    }
  },
  { $sort: { count: -1 } },
  { $limit: 20 }
]

Thanks for all the help!

[oallouch]

Just so you know, if you had a second $project after your $lookup (for instance) that wouldn't work (see https://github.com/parse-community/parse-server/issues/4682 )