Mongodb run $in on an array-CodePudding

i am trying to do a simple query, which is based on array of objects. fieldA is an array of objects and my query looks something like this

const books = [
  {name: 'LOTR', author: 'J.R Tolkien'},
  {name: 'HP', author: 'JK. Rowling'},
  {name: 'Witcher', author: 'A. Sapkowski'}
];

const exists = await Book.exists({ fieldA: { $in: books } });

I am trying to see if the books in books array exist in database by implementing a search on fieldA which is an array of objects. The problem is that $in seems to take only one entity and it always returns false this way. Is there any way i can use $in but on array of objects?

CodePudding user response：

We achieve many-to-many matching using $setIntersection instead of $in.

Assume that fieldA has a shape similar to these as we might see in collection foo:

[
    {fieldA: [
        {name: 'LOTR', author: 'J.R Tolkien'},
        {name: 'LOTR', author: 'J.R Tolkien'}, // dupes treated as one in $setIntersection; see below        
        {name: 'The Prize', author: 'Daniel Someone'},
        {name: 'Corn', author: 'Dave'},
        {name: 'More Corn', author: 'Dave'},
        {name: 'HP', author: 'JK. Rowling'}
    ]},
    {fieldA: [
        {name: 'Dune', author: 'Herbert'},
        {name: 'HP', author: 'JK. Rowling'},
        {name: 'NOT Witcher', author: 'A. Sapkowski'}, // wrong name; no match
        {name: 'LOTR', author: 'NOT J.R Tolkien'} // wrong author; no match                         
    ]},
    {fieldA: []}, // empty array
    {yada: "nope"} // this doc has NO fieldA!
]

This agg pipeline:

var books = [
    {name: 'LOTR', author: 'J.R Tolkien'},
    {name: 'HP', author: 'JK. Rowling'},
    {name: 'Witcher', author: 'A. Sapkowski'}
];

c = db.foo.aggregate([
    {$project: {X: {$setIntersection: [ "$fieldA", books ]} }},
    {$addFields: {nX: {$size: {$ifNull:["$X",[] ]}} }}
]);

yields this output:

{
    "_id" : 0,
    "X" : [
        {
            "name" : "HP",
            "author" : "JK. Rowling"
        },
        {
            "name" : "LOTR",
            "author" : "J.R Tolkien"
        }
    ],
    "nX" : 2
}
{
    "_id" : 1,
    "X" : [
        {
            "name" : "HP",
            "author" : "JK. Rowling"
        }
    ],
    "nX" : 1
}
{ "_id" : 2, "X" : [ ], "nX" : 0 }
{ "_id" : 3, "X" : null, "nX" : 0 }

VERY IMPORTANT CAVEAT: For $setIntersection involving arrays of objects -- not scalars -- to work as intended, the order of the fields in the objects as returned by MongoDB must match the order of those in the objects in books array; in this case name must precede author. Swapping the order will cause a mismatch. To defend against this, you can force the desired order of fields (ostensibly the same order as constructed in the calling query i.e. the order seen in books) in each doc of array fieldA by running fieldA through a $map:

db.foo.aggregate([
    {$project: {X: {$setIntersection: [
        {$map: {
        input: "$fieldA",
            as: "z",
            in: {name: "$$z.name", author: "$$z.author"}
        }},
    books ]} }},

    {$match: {$expr: {$gt:[{$size: {$ifNull:["$X",[] ]}}, 0]}} }
]);

Note also that we show how to filter out those docs with no intersections by filtering out zero-length arrays. The $ifNull ensures that $size is passed an array (even one of zero length) because docs with no fieldA will consider fieldA to be null and that will produce an error in $size.