Aggregation between a MongoDB collection and an external array

Question

I'm looking for a method for join a collection and a external array of strings (with codes), that returns another string array with all codes of first array that aren't included in the collection.

The collection sample:

[{
    _id:ObjectId("61bf57bc9d1f93b7ae5fa785"),
    "Movie": {
        "Code": "123",
        "OriginalTitle": "Title 1",
        "Year": 2021
     }},{
    _id:ObjectId("61bf57bc9d1f93b7ae5fa786"),
    "Movie": {
        "Code": "124",
        "OriginalTitle": "Title 2",
        "Year": 2021
     }},{
    _id:ObjectId("61bf57bc9d1f93b7ae5fa787"),
    "Movie": {
        "Code": "125",
        "OriginalTitle": "Title 3",
        "Year": 2021
     }},{
    _id:ObjectId("61bf57bc9d1f93b7ae5fa788"),
    "Movie": {
        "Code": "126",
        "OriginalTitle": "Title 4",
        "Year": 2021
     }
 }]

the external array:

const codes = ["125", "127", "128", "129"];

the aggregation must compare "Movie.Code" with the array and returns another array with the next values:

returnCodes = ["127", "128", "129"];

How can I make it?

Not clear to me what you mean by "the next value". Anyway, in Aggregation Pipeline Operators you can use external variables the same way like field names, e.g. { $filter: { input: codes, cond: { $in: ["$$this", "$Movie.code"] } } } — Wernfried Domscheit
– Wernfried Domscheit, Commented Jan 19, 2022 at 21:15
In the example, "127", "128" & "129" are the values that not are in the array of the collection (Movie.Code). If you see the sample collection, Movie.Code have four values ("123", "124", "125" & "126"). The only value include in the collection and in the external array is "125", due to, the return array (returnCodes in the example) must contains the other values ("127", "128" & "129") — JulioC
– JulioC, Commented Jan 19, 2022 at 21:22

Wernfried Domscheit · Accepted Answer · 2022-01-20 06:43:43Z

1

Maybe this:

db.collection.aggregate([  
  {
    $group: {
      _id: null,
      Code: { $push: "$Movie.Code" }
    }   
  },   
  {
    $project: {
      returnCodes: {
        $filter: {
          input: codes ,
          cond: { $not: { $in: [ "$$this", "$Code" ] } }
        }
      }
    }   
  } 
]).toArray().shift().returnCodes

Of course, you could do it also in Javascript:

const codes = ["125", "127", "128", "129"];
const coll = db.collection.find({}, { Code: "$Movie.Code" }).toArray().map(x => x.Code);

returnCodes = codes.filter(x => !coll.includes(x));

edited Jan 20, 2022 at 6:43

answered Jan 19, 2022 at 21:38

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

JulioC Over a year ago

Tested here: mongoplayground.net/p/Yt1GBF6GVu_

JulioC Over a year ago

Is it possible to do this in other way (without group)? $group is too much hard for a collectión with +200000 documents.

Wernfried Domscheit Over a year ago

I don't think so, because your collection has 4 documents (in your example) but as result you want one array with 3 elements. So, at some point there must be a $group.

Wernfried Domscheit Over a year ago

See my update. Maybe add { $match: { "Movie.Code": { $nin: codes } } } as first stage which may reduce the amount of documents significantly.

JulioC Over a year ago

Your last method returns in const coll all $Movie.Code from the collection. +200000 codes (millions in the furure) and then compare if const codes are include in that. I think it's cheaper find in database one by one if all the codes in the first const are in the collection. The latter is what i'm doing now. I think your first solution is closer to solving the problem, as long as the grouping can be removed and I don't know how to do it.

|

Collectives™ on Stack Overflow

Aggregation between a MongoDB collection and an external array

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related