NEDB Query returning false positives with $nin on array

Question

I'm having an issue using the NEDB find() query structure.

The find() query below should return all new messages: sent to the email or the All group specified and that have not been read or sent by the email specified.

However, it is returning false positives. For example, the two items in the flat file (below) are returned in the results, despite the fact that the email IS in the array of the property being evaluated. I am using the $nin operator and I can't work out what I'm doing wrong? All suggestions welcomed :)

var email = '[email protected]';
var message_query = [];
    message_query.push({$and: [{to: 'All'}, {'data.readBy': {$nin: [email]}}]});
    message_query.push({$and: [{to: email}, {'data.read': {$exists: false}}]});

    message
        .find({
            $and: [
                {$not: {from: email}},
                {$or: message_query}
            ]
        })
        .exec(function (err, results) {

        });

Here's a snippet of what the flat file looks like. These two should not be returned, but they are :/

...

{ 
  to: 'All',
  toname: 'Some Name',
  from: '[email protected]',
  fromname: 'Some other Name',
  message: 'A message for all...',
  timestamp: 1502473320,
  imtype: 'msg',
  _id: 'K66iP3dZHbYTfGgK',
  data:
   { readBy:
      [ '[email protected]',
        '[email protected]' ] } 
},
{ 
  to: 'All',
  toname: 'Some Name',
  from: '[email protected]',
  fromname: 'Some other Name',
  message: 'A message for all...',
  timestamp: 1502473420,
  imtype: 'msg',
  _id: 'K66iP3dZNyNxfGgK',
  data:
   { readBy:
      [ '[email protected]',
        '[email protected]' ] } 
}

...

Thanks in advance

Answer 1

The issue is the $nin operator - it does not work as it does in mongo.

NEDB's $in operator works differently: Only one of the operands can be an array. Check out the definition of $in ($nin is just a negation of $in after all). When it checks if things are equal for arrays, it makes sure only one of the items is an array i.e. neither a or b[i] can be arrays. If they are, then return false. $nin hence returns true - explaining the presence of both the documents in the results.

You can verify this as follows: try changing [email] to something like ["[email protected]", "[email protected]", "[email protected]"], you'll notice that the two results disappear - proving that it doesn't check if each element in data.readBy is not in the list we provide, but check if the entire data.readBy is present in the list we provide.

Solution Use { $not: { elemMatch }

You can overcome the problem by changing your query to the following:

message_query.push({
  $and: [{ to: "All" }, { $not: { "data.readBy": { $elemMatch: email } } }]
});

Here's the code to verify:

const Datastore = require("nedb");

const db = new Datastore({
  inMemoryOnly: true,
  autoload: false
});

db.insert(
  [
    {
      to: "All",
      toname: "Some Name",
      from: "[email protected]",
      fromname: "Some other Name",
      message: "A message for all...",
      timestamp: 1502473320,
      imtype: "msg",
      data: {
        readBy: ["[email protected]", "[email protected]"]
      }
    },
    {
      to: "All",
      toname: "Some Name",
      from: "[email protected]",
      fromname: "Some other Name",
      message: "A message for all...",
      timestamp: 1502473420,
      imtype: "msg",
      data: {
        readBy: ["[email protected]", "[email protected]"]
      }
    },
    {
      to: "All",
      toname: "Some Name",
      from: "[email protected]",
      fromname: "Some other Name",
      message: "A message for all...",
      timestamp: 1502473420,
      imtype: "msg",
      data: {
        readBy: ["[email protected]" ]
      }
    },
    {
      to: "[email protected]",
      toname: "Some Name",
      from: "[email protected]",
      fromname: "Some other Name",
      message: "A message for all...",
      timestamp: 1502473420,
      imtype: "msg",
      data: {
        readBy: ["[email protected]", "[email protected]"]
      }
    },
    {
      to: "[email protected]",
      toname: "Some Name",
      from: "[email protected]",
      fromname: "Some other Name",
      message: "A message for all...",
      timestamp: 1502473420,
      imtype: "msg",
      data: {
        read: true
      }
    },
    {
      to: "[email protected]",
      toname: "Some Name",
      from: "[email protected]",
      fromname: "Some other Name",
      message: "A message for all...",
      timestamp: 1502473420,
      imtype: "msg",
      data: {
      }
    }
  ],
  (...args) => {
    var email = "[email protected]";

    var _list = ["[email protected]", "[email protected]", "[email protected]"];

    var message_query = [];

    message_query.push({
      $and: [{ to: "All" }, { $not: { "data.readBy": { $elemMatch: email } } }]
    });

    // message_query.push({
    //   $and: [{ to: "All" }, { "data.readBy": { $nin: [email] } }]
    // });

    // message_query.push({
    //   $and: [{ to: "All" }, { "data.readBy": { $nin: _list } }]
    // });

    message_query.push({
      $and: [{ to: email }, { "data.read": { $exists: false } }]
    });

    db.find({
      $and: [ { $not: { from: email } }, { $or: message_query } ]
    }).exec(function(err, results) {
      console.log(JSON.stringify(results, null, 2));
    });

  }
);