Correctly use MongoDB composite primary key in $lookup pipeline

user_5163510 0
Viewed 502

I have a collection with a composite primary key as such

_id: {
  a: 'a',
  b: 'b'
}

Explaining the following query, reveals that it does a COLLSCAN and the index is not used

db.collection.find({'_id.a': '1', '_id.b': '2'}).explain()

Changing that query to the following, uses the IDHACK successfully.

db.collection.find({_id: {a: '1', b: '2'}}).explain()

The problem is that this doesn't seem to work if used in the pipeline of a $lookup in an aggregation.

This doesn't return any results:

$lookup: {
  from: 'collection',
  let: {
    pid: '1',
    sid: '2',
  },
  pipeline: [
    {
      $match: {
        _id: {
          a: '$$pid',
          b: '$$sid',
        },
      },
    },
  ],
  as: 'results',
},

while the format that is not using the index returns results as expected:

$lookup: {
  from: 'collection',
  let: {
    pid: '1',
    sid: '2',
  },
  pipeline: [
    {
      $match: {
        $expr: {
            $and: [
              {
                $eq: [
                  '$_id.a', '$$pid',
                ],
              }, {
                $eq: [
                  '$_id.b', '$$sid',
                ],
              },
            ],
        },
      },
    },
  ],
  as: 'results',
},

So my question is, how can I modify the lookup pipeline in order to use the index of the composite primary key?

aggregation-framework mongodb composite-primary-key
1 Answers

There appears to be 2 parts to this question: Why don't both _id queries use the index? and why don't both lookups return data?

Why isn't the index used for both queries?

When MongoDB compares two objects, the order of the fields matters. This means that all of these could be stored in the same collection, and each _id value is unique:

{_id:{ a:'1',b:'2'}},
{_id:{ b:'2',a:'1'}},
{_id:{ a:'1',b:'2',c:'3'}},
{_id:{ a:'1',c:'3',b:'2'}},
{_id:{ c:'3',a:'1',b:'2'}}

When an index includes a field such as {_id: 1}, the entire value of the field is used when building the index keys. Given the above documents, a query on {"_id.a":'1'} could not be serviced by the index on {_id:1}.

The query

db.collection.find({_id: {a: '1', b: '2'}})

would match only the first document in the above list, and since it is querying only on the whole _id field, it can be properly serviced by the index.

The query

db.collection.find({'_id.a': '1', '_id.b': '2'})

is matching on the values of fields in the subdocument contained in the _id field, not on the whole field, so it cannot be properly serviced by the index on {_id:}`
This query would match every document in the above list.

Why don't both lookups return data?

MongoDB query language and aggregation language are different.

One of those differences is the aggregation language expands variable values in strings while the query language does not.

It is a bit confusing sometimes because the query language is used inside of the $match aggregation stage, unless you use the $expr operator to enclose the aggregation expression.

This match stage is checking for a document whose _id field is exactly the document {a: '$$pid', b: '$$sid'}, (i.e. the _id.a field contains the string "$$pid", etc.)

{
      $match: {
        _id: {
          a: '$$pid',
          b: '$$sid',
        },
      },
    },

To use the variables, you would need to also use the $expr operator like in the other query.

{$lookup:{
    let: {pid:'1', sid:'2'},
    as:"lookedup",
    from:"collection",
    pipeline:[{
        $match:{
           $expr:{
               $eq:[ "$_id", {a:"$$pid", b:"$$sid"}]
           }
        }
    }]
}}
user_22826340
Related