MongoDB query slow in join using $ne to look for not empty arrays-CodePudding

I'm new to Mongo and I have a slow query when using $ne in a match pipeline (to get the records that match only and not all the ones where the array is empty)

The query is as follow:

 db.EN.aggregate([
 {
     $lookup: {
  from: 'csv_import',
  let: {pn:'$ICECAT-interface.Product.@Prod_id'},
  pipeline: [{
      $match: {
          $expr: {
              $eq: ["$$pn","$part_no"]
          }
      }
  }],
  as: 'part_number_info'
     }
 }, { $match: { part_number_info: { $ne: [] } } }
 ]).pretty();

When I remove the { $match: { part_number_info: { $ne: [] } } } the query executes in 21 seconds, vs almost 2 hours when executed using the $ne clause.

There's an index already on ICECAT-interface.Product.@Prod_id, and here are the 2 collections structure sample:

csv_import:

{
  "_id": "ObjectId(\"6348339cc6e5c8ce0b7da5a4\")",
  "index": 23679,
  "product_id": 4019734,
  "part_no": "CP-HAR-EP-ADVANCED-REN-1Y",
  "vendor_standard": "Check Point"
}

EN:

[{
  "_id": "1414",
  "ICECAT-interface": {
    "@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
    "@xsi:noNamespaceSchemaLocation": "https://data.icecat.biz/xsd/ICECAT-interface_response.xsd",
    "Product": {
      "@Code": "1",
      "@HighPic": "https://images.icecat.biz/img/norm/high/1414-HP.jpg",
      "@HighPicHeight": "400",
      "@HighPicSize": "43288",
      "@HighPicWidth": "400",
      "@ID": "1414",
      "@LowPic": "https://images.icecat.biz/img/norm/low/1414-HP.jpg",
      "@LowPicHeight": "200",
      "@LowPicSize": "17390",
      "@LowPicWidth": "200",
      "@Name": "C6614NE",
      "@IntName": "C6614NE",
      "@LocalName": "",
      "@Pic500x500": "https://images.icecat.biz/img/gallery_mediums/img_1414_medium_1480667779_072_2323.jpg",
      "@Pic500x500Height": "500",
      "@Pic500x500Size": "101045",
      "@Pic500x500Width": "500",
      "@Prod_id": "C6614NE",

SOLUTION

I did add an index on part_no field in csv_import and I changed the order of the query to be smaller to large (EN is 27GB and csv_import is a few MB)

Final query: (includes the suggestion made by nimrod serok

db.csv_import.aggregate([
 {
     $lookup: {
  from: 'EN',
  let: {pn:'$part_no'},
  pipeline: [{
      $match: {
          $expr: {
              $eq: ["$$pn","$ICECAT-interface.Product.@Prod_id"]
          }
      }
  }],
  as: 'part_number_info'
     }
 },{$match: {"part_number_info.0": {$exists: true}}}
 ])

CodePudding user response：

A better option is to use:

{$match: {"part_number_info.0": {$exists: true}}}

See how it works on the playground example