CODEWITHSUNDEEP
arraysmongodbmongooseaggregation-frameworklookup
Niccolò Caselli
Niccolò Caselli

Reputation: 882

Array match in MongoDB lookup pipeline

I have two collections. One of artworks, the other of collections (album of multiple artworks). Each artwork has a "collections" field that is an array in which the id of the collections to which it belongs is saved.

What I would like to do is get all the collections of a user and for each collection the first 4 artworks (to show a preview in the app).

However, this thing, which I thought was simple, turned out to be more complicated.

In a normal query I would do this:

artworkModel.find({
    collections: collection
})

However, in the aggregation pipeline, this does not work and so the solution I implemented was this:

[
  {$match: {
      author: author,
    },
  },
  {
    $lookup: {
      from: 'artworks',
      as: 'previews',
      let: { collection: '$_id' },
      pipeline: [
        {
          $match: {
            collections: { $exists: true },
            $expr: { $in: ['$$collection', '$collections'] }, 
          },
        },
        { $sort: { date: -1 } },
        { $limit: 4 },
        { $addFields: { id: '$_id' } },
      ],
    },
  }
]

The problem is that I cannot accept this solution because it does not use, unlike the query above, the index on the "collections" field and therefore performs a full scan of the collection. Help is appreciated.

EDIT: sample data

artworks:

[
    {
      "_id": { "$oid": "612fa9541121d06014e7d9bc" },
      "collections": [
        { "$oid": "612fa9481121d06014e7d9b5" },
        { "$oid": "612fa9f6b2cc520a84e83dde" }
      ],
      "author": { "$oid": "5fe8fe53ff8d9dc25c9e3277" },
      "text": "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Etiam vestibulum dolor id justo condimentum ",
      "type": "Text"
    },
    {
      "_id": { "$oid": "60eec90e787a0b320c514446" },
      "author": { "$oid": "5fe8fe53ff8d9dc25c9e3277" },
      "type": "Image",
      "asset": { "$oid": "60eec90e787a0b320c514441" },
      "collections": [{ "$oid": "612fa9f6b2cc520a84e83dde" }]
    },
    {
      "_id": { "$oid": "612fa9f2b2cc520a84e83db1" },
      "collections": [{ "$oid": "612fa9f6b2cc520a84e83dde" }],
      "author": { "$oid": "5fe8fe53ff8d9dc25c9e3277" },
      "text": "Lorem ipsum dolor sit amet",
      "type": "Text"
    },
    {
      "_id": { "$oid": "612549d4778270001862472f" },
      "author": { "$oid": "5fe8fe53ff8d9dc25c9e3277" },
      "type": "Video",
      "asset": { "$oid": "612549d4778270001862472a" }
    }
  ]

collections:

[
  {
    "_id": { "$oid": "612fa9f6b2cc520a84e83dde" },
    "name": "Name",
    "author": { "$oid": "5fe8fe53ff8d9dc25c9e3277" },
    "itemsCount": 3,
  }
]

Upvotes: 0

Views: 514

Answers (1)

Takis
Takis

Reputation: 8695

The bellow query does i think the same as your query. Before running it create an index on artworks.collections (multikey index)

Query

  • filters collections based on author
  • joins with normal lookup using the index
  • does a dummy join with 1 collection [{}] only to apply the pipeline that you want, its like a trick

Test it if you can and tell me if it worked and if its fast enough

Test code here

db.collections.aggregate([
  {
    "$match": {
      "author": {
        "$eq": "5fe8fe53ff8d9dc25c9e3277"
      }
    }
  },
  {
    "$lookup": {
      "from": "artworks",
      "localField": "_id",
      "foreignField": "collections",
      "as": "previews"
    }
  },
  {
    "$lookup": {
      "from": "dummy",
      "let": {
        "previews": "$previews"
      },
      "pipeline": [
        {
          "$set": {
            "previews": "$$previews"
          }
        },
        {
          "$unwind": {
            "path": "$previews"
          }
        },
        {
          "$replaceRoot": {
            "newRoot": "$previews"
          }
        },
        {
          "$sort": {
            "date": -1
          }
        },
        {
          "$limit": 4
        },
        {
          "$set": {
            "id": "$_id"
          }
        },
        {
          "$unset": [
            "_id"
          ]
        }
      ],
      "as": "previews"
    }
  }
])

Upvotes: 1

Related Questions