MongoDB - handle non-existent arrays in $in query

Question

I have the following query:

db.getCollection('authors').aggregate([
  { $match: {} }, 
  { $sort: { name: -1 } }, 
  { $addFields: { x_id___as___string: { $toString: "$_id" } } }, 
  { $lookup: {
      from: "books",
      let: { fkField:"$x_id___as___string" },
      pipeline: [{ $match: { $expr: { $in: ["$$fkField", "$authorIds"] } } }],
      as: "books"
    } 
  }
])

which works fine, unless the authorIds array is missing from some of the joined documents, in which case I get the error of

"errmsg" : "$in requires an array as a second argument, found: missing", "code" : 40081, "codeName" : "Location40081" } : aggregate failed

I've confirmed that manually adding authorIds as an empty array to the documents missing it fixes the query. My question is:

How can I adjust this query to work with a non-existent authorIds field?

Should I change my $expr to be an $and query, with the first value a check for array existence? Does Mongo guarantee short-circuiting? Is there a simpler check I can just add to the $in query?

Adam Rackis · Accepted Answer · 2019-10-14 13:26:22Z

1

add $cond in lookup pipeline $expr

db.getCollection('authors').aggregate([
  { $match: {} }, 
  { $sort: { name: -1 } }, 
  { $addFields: { x_id___as___string: { $toString: "$_id" } } }, 
  { $lookup: {
      from: "books",
      let: { fkField:"$x_id___as___string" },
      pipeline: [{ $match: { $expr: { $in: ["$$fkField", {$cond: {if: {$gt: ["$authorIds", null]}, then: "$authorIds", else: []}}] } } }],
      as: "books"
    } 
  }
])

that will check if authorIds exists. To make sure it's an actual array, you can use $isArray

db.getCollection('authors').aggregate([
  { $match: {} }, 
  { $sort: { name: -1 } }, 
  { $addFields: { x_id___as___string: { $toString: "$_id" } } }, 
  { $lookup: {
      from: "books",
      let: { fkField:"$x_id___as___string" },
      pipeline: [{ 
        $match: { 
          $expr: { 
            $in: [
              "$$fkField", 
              { $cond: { if: { $isArray: "$authorIds" }, then: "$authorIds", else: [] } }
            ] 
          } 
        } 
      }],
      as: "books"
    } 
  }
])

edited Oct 14, 2019 at 13:26

Adam Rackis

83.5k57 gold badges281 silver badges402 bronze badges

answered Oct 14, 2019 at 12:57

sushant mehta

1,2841 gold badge7 silver badges13 bronze badges

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

4 Comments

Adam Rackis Over a year ago

Yeah! But wouldn't isArray be better, ie { $cond: { if: { $isArray: "$authorIds" }, then: "$authorIds", else: [] } }? Would you be ok with me editing that into your answer?

Adam Rackis Over a year ago

I added it in as a second example - hope you don't mind. Thanks a ton!

prasad_ Over a year ago

Why is the { $match: {} }, stage required in the pipeline?

Adam Rackis Over a year ago

@prasad_ it's not doing anything in this particular example, you're right

Collectives™ on Stack Overflow

MongoDB - handle non-existent arrays in $in query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related