[JAVA-4691] I have a problem with an aggregation that works in Compass and VScode Extension but not in Java Created: 28/Jul/22  Updated: 27/Oct/23  Resolved: 12/Aug/22

Status: Closed
Project: Java Driver
Component/s: Aggregation
Affects Version/s: None
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Joachim Hansen Assignee: Ross Lawley
Resolution: Gone away Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

Summary

I have a problem with an aggregation that works in Compass and VScode Extension but not in JAVA via spring-boot-starter-data-mongodb.
I have debugged all the way to the driver, and everything seemed normal.

Please provide the version of the driver. If applicable, please provide the MongoDB server version and topology (standalone, replica set, or sharded cluster).

Driver version: 4.6 and 4.7
Mongo Server: MongoDB Atlas version 6.0.0 

How to Reproduce

I have 3 collections:

  1. device

{
  _id: ObjecId,
  name: string,
  ...
}

  1. hardwareInventory

{
  _id: ObjectId,
  dictionaries: {
    Bios Information: [Object],
    Operating System: [
      {
        Name: String,
        OS StartUp: {
          value: Date,
          unit: String,
          ...
        },
        Service StartUp: {
          value: Date,
          unit: String,
          ...
        }
      }
    ]
    ...
  }
  ...
}

  1. softwareInventory

Steps to reproduce. If possible, please include a Short, Self Contained, Correct (Compilable), Example.

Additional Background

Please provide any additional background information that may be helpful in diagnosing the bug.



 Comments   
Comment by PM Bot [ 12/Aug/22 ]

There hasn't been any recent activity on this ticket, so we're resolving it. Thanks for reaching out! Please feel free to comment on this if you're able to provide more information.

Comment by Ross Lawley [ 28/Jul/22 ]

Hi jhh@capasystems.com

As this sounds like a support issue, I wanted to give you some resources to get this question answered more quickly:

  • Our MongoDB support portal, located at support.mongodb.com
  • Our MongoDB community portal, located here
  • If you are an Atlas customer, there is free support offered 24/7 in the lower right hand corner of the UI

Just in case you have already opened a support case and are not receiving sufficient help, please let me know and I can facilitate escalating your issue.

Alternatively, please provide a minimal reproducible example so I can replicate the issue and see if I can determine the cause.

All the best,

Ross

Comment by Joachim Hansen [ 28/Jul/22 ]

Hi Ross,
It doesn't return any results but the two others do, as expected.

You should be able to put together an example from what I've given you.
But I might be able to export some data for you, if needed.

As I wrote, I've tried to debug the code all the way to the driver, and everything before "fetching" of data seems to be as it should be, but again the resulting data is "missing" in Java, but not in the VScode extension or Compass.

Futhermore, the two aggregations are 99% the same, but nr. 2 actually give the same data back on all 3 platforms. So it's only the first aggregation that produces the "error", and it's only 2 values that has been changed.

Comment by Ross Lawley [ 28/Jul/22 ]

Hi jhh@capasystems.com,

When you say it doesn't work, do you mean it errors? or doesn't return any results?

The Java driver doesn't do anything special with the aggregation pipeline - just passes it to the server to perform. The main thing to understand is the actual aggregation pipeline the same and double check there has been no conversion errors? I think to be able to help further we'd need an reproducible example.

Ross

Comment by Joachim Hansen [ 28/Jul/22 ]

I pressed CTRL+ENTER instead of SHIFT+ENTER, and therefore created the issue before i was done.

so here is the rest of the info:

hardwareInventory also has a deviceId: ObjectId at the top level

  1. softwareInventory

{
  _id: ObjectId,
  deviceId: ObjectId,
  dictionary: [
    {
      Name: String,
      InstallDate: Date,
      ...
    }
  ]
  ....
}

The two aggregations starting from device:

  1. [{
     $match: {
      orgId: 1
     }
    }, {
     $lookup: {
      from: 'hardwareInventory',
      localField: '_id',
      foreignField: 'deviceId',
      as: 'hardwareInventory'
     }
    }, {
     $lookup: {
      from: 'softwareInventory',
      localField: '_id',
      foreignField: 'deviceId',
      as: 'softwareInventory'
     }
    }, {
     $replaceRoot: {
      newRoot: {
       $mergeObjects: [
        '$$ROOT',
        {
         hardwareInventory: {
          $arrayElemAt: [
           '$hardwareInventory.dictionaries',
           0
          ]
         },
         softwareInventory: {
          $arrayElemAt: [
           '$softwareInventory.dictionary',
           0
          ]
         }
        }
       ]
      }
     }
    }, {
     $match: {
      $expr: {
       $gt: [
        {
         $size: {
          $ifNull: [
           {
            $filter: {
             input: '$hardwareInventory.Operating System',
             as: 'item',
             cond: {
              $gt: [
               '$$item.Service StartUp.value',
               {
                $dateSubtract: {
                 startDate: '$$NOW',
                 unit: 'month',
                 amount: 2
                }
               }
              ]
             }
            }
           },
           []
          ]
         }
        },
        0
       ]
      }
     }
    }, {
     $project: {
      hardwareInventory: false,
      softwareInventory: false
     }
    }, {
     $facet: {
      result: [
       {
        $limit: 50
       },
       {
        $skip: 0
       }
      ],
      totalCount: [
       {
        $count: 'count'
       }
      ]
     }
    }, {
     $project: {
      result: 1,
      totalCount: {
       $max: '$totalCount.count'
      }
     }
    }]

  1. [{
     $match: {
      orgId: 1
     }
    }, {
     $lookup: {
      from: 'hardwareInventory',
      localField: '_id',
      foreignField: 'deviceId',
      as: 'hardwareInventory'
     }
    }, {
     $lookup: {
      from: 'softwareInventory',
      localField: '_id',
      foreignField: 'deviceId',
      as: 'softwareInventory'
     }
    }, {
     $replaceRoot: {
      newRoot: {
       $mergeObjects: [
        '$$ROOT',
        {
         hardwareInventory: {
          $arrayElemAt: [
           '$hardwareInventory.dictionaries',
           0
          ]
         },
         softwareInventory: {
          $arrayElemAt: [
           '$softwareInventory.dictionary',
           0
          ]
         }
        }
       ]
      }
     }
    }, {
     $match: {
      $expr: {
       $gt: [
        {
         $size: {
          $ifNull: [
           {
            $filter: {
             input: '$softwareInventory',
             as: 'item',
             cond: {
              $gt: [
               '$$item.InstallDate',
               {
                $dateSubtract: {
                 startDate: '$$NOW',
                 unit: 'month',
                 amount: 2
                }
               }
              ]
             }
            }
           },
           []
          ]
         }
        },
        0
       ]
      }
     }
    }, {
     $project: {
      hardwareInventory: false,
      softwareInventory: false
     }
    }, {
     $facet: {
      result: [
       {
        $limit: 50
       },
       {
        $skip: 0
       }
      ],
      totalCount: [
       {
        $count: 'count'
       }
      ]
     }
    }, {
     $project: {
      result: 1,
      totalCount: {
       $max: '$totalCount.count'
      }
     }
    }] 

Aggregation number 1 works in Compass and VScode extension but not in JAVA. Number 2 works on all 3

The only difference is in the second $match by the $filter.
I swap out input and $gt position 0 in cond

Generated at Thu Feb 08 09:02:43 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.