How to use a variable defined in let as property name of an object field in the pipeline in a lookup?

Working with Data
,
#1 
user schema:

{
  _id: "OjectId",
}

interaction schema:

{
  blocked: {
   // properties with names from user._id and value of true
  }
}

                db.user.aggregate([{
                  $lookup: {
                    from: "interaction",
                    as: "remove",
                    let: { tar_id: "$_id" },
                    pipeline: [
                      {
                        $match: {
                          [`blocked[$$tar_id]`]: true,
                        },
                      },
                      {
                        $limit: 1,
                      },
                      {
                        $project: {
                          _id: 0,
                          remove: "true",
                        },
                      },
                    ],
                  },
                }]}

Notice the line:

  [`blocked[$$tar_id]`]: true,

Does the above lookup work? If not, how can I get it to work as intended?

EDIT:

tested. It does not work. The problem remains, how do I get to work as desired?

#2

Because you’re trying to use an aggregation variable (expression) inside $match you need to use $expr and fully aggregation syntax inside. I’m not sure I have your schema correct but if you are trying to find documents in interaction collection where the field name is based on user id then you would probably need to use something like $getField which unfortunately does not accept a variable expression, only a string for field names.

There is a workaround for this but just to confirm, the interaction collection would have documents structured like this:

{
    blocked:  { 
        user1: true, 
        user2: true 
     }
}

If that’s not the case, can you provide an example document for that collection?

Asya

#3 
{
    blocker_id: user_id_x
    blocked:  { 
        user_id_y: true, 
        user_id_z: true,
        etc...
     }
}

Yes, that is the correct schema for the interaction collection. The blocked field is an object where the properties are the user ids from the user collection. The purpose of this is so that the property names can be used in the $lookup pipeline.

#4

I’m not sure I’m following this part - field names normally would be fixed (sort of like column names in relational tables would be). While it’s not impossible to query dynamic fields, it’s somewhat tricky… as you can see here. Most of the query language supposes the field name is going to be a string literal. Here also you cannot query if blocked is {someid: true} because presence of other fields in the subdocument means the query will not match.

Is the schema “final” or is this an app in development? I think you can see that $lookup in fact does not allow constructing the equality condition dynamically (at least not easily) so I’m wondering if this is the schema you are stuck with or if it can be changed.

Asya

#5

It is in development. The reason for this is because looking up the value of an object’s property is O(1), while looking up an element of an array is O(N). Huge difference, so unless proven impossible, I would prefer to explore this schema until that point. Please share your work around, thx.

EDITED:

However, if the workaround does not have the O(1), then it would defeat the purpose.

#6

Sorry to be impatient if you are currently writing up the workaround. But can you first confirm whether or not you think there is a work around that will maintain the O(1) time complexity?

#7

It wouldn’t be O(1) no matter what - there’s no shortcut to find a named field in a flexible schema database either.

Asya

#8

So can you confirm then that to find an element in an array takes the same amount of time as finding a property name of an object?

#9

If you are searching for array element by index number it may be faster - if you are searching by some value it’ll likely be a little slower, but they are all at worst O(n) where n is the number of fields or number of elements in the array…

Why do you need to use $lookup? Can you just make two queries? What is the goal/purpose of the aggregation on user with $lookup on interaction here? Sometimes it’s simpler and faster to just make two separate queries…

Asya