Mongodb run $in on an array

Question

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?

Could you please add one doc that shows what fieldA looks like? Also: are you in general trying to match any book in the books array to the array that is fieldA? If so, what fields inside fieldA do you want to match against the books array? All fields? Exact and only name + author (driven by the shape of the books data)? Any name or any author? — Buzz Moschetti
– Buzz Moschetti, Commented Feb 1, 2022 at 22:56

Buzz Moschetti · Accepted Answer · 2022-02-02 01:01:12Z

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.

Collectives™ on Stack Overflow

Mongodb run $in on an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related