Referencing another collection may lead to huge queries

Question

I have two collections:

Annotations

{
  annotations: ["lion", "zebra", "cobra"],
  imagesId: 1234
}

{
  annotations: ["lion", "zebra", "cobra", "dog"],
  imagesId: 12345
}

Images

{_id:1234,
 name: "africa"
}
{_id:12345,
 name: "america"
}

I want to find all image names annotated with for example zebra.

This is my pseudo approach:

let annotations = Annotations.find({annotations: {'$in': 'zebra'}});  

getOnlyUniqueItems(annotations);  

Images.find({_id: {'$in': annotations}});

It will work, problem is when annotations contain for example 1M ids, then query itself will be more then 1MB of text, it will probably kill my database.

Question: what is the best approach to this problem?
Thank you! :-)

score 1 · Accepted Answer · answered Mar 08 '19 at 20:51

1

If we won't take in consideration that save annotations as a free text separated by , is a bad idea, and performance/response time are not issue for the specific use case you might give a try to $lookup, something like:

Annotations.aggregate([
  { $match: { annotations: { '$in': 'zebra' } } },
  {
      $lookup:
         {
            from: "Images",
            localField: "imagesId",
            foreignField: "_id",
            as: "image"
        }
   }
]);

answered Mar 08 '19 at 20:51

zooblin

2,172
2
27
33

`annotations` is not a free text, it is a collection of {annotations, imagesid} objects. – Jacek Wojcik Mar 08 '19 at 21:46
Yeah, but `annotations` property inside `annotations` collection is a free text. – zooblin Mar 09 '19 at 09:40
my mistake, it should be an array of strings: `annotations: ['zebra', 'lion', 'cobra']` – Jacek Wojcik Mar 09 '19 at 09:56
Does it change anything in your answer? – Jacek Wojcik Mar 10 '19 at 09:10
Not really, in anyway you have to use `$lookup` – zooblin Mar 10 '19 at 17:22

Referencing another collection may lead to huge queries

1 Answers1