Join and filter

I have two tables:

{number, name}

products (with nested subproduct)
{number, productGroupNumber, subProducts: {barred, showOnHomepage}}

I would like to extract all productGroups which have at least one product that has at least one subproduct that is “!barred && show”.

I started getting unique productGroupNumbers from products, but fails to join them to productGroups.

Any help appreciated

    '$match': {
      'subProducts.barred': false, 
      'subProducts.showOnHomepage': true
  }, {
    '$project': {
      'productGroupNumber': '$productGroupNumber'
  }, {
    '$group': {
      '_id': null, 
      'productGroupNumber': {
        '$addToSet': '$productGroupNumber'
  }, {
    '$unwind': '$productGroupNumber'

Couldn’t figure it out, so I made two queries to get around this.

Hello @Lasse_Johansen ,

Welcome to The MongoDB Community Forums! :wave:

Do you mean two collections?

In case you want to work with relevant fields from two different collections you can use $lookup

Please feel free to reach out in case of any more queries/issues, would be happy to help!


1 Like