Join and filter

Working with Data
,
1

I have two tables:

productGroups
{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'
  }
]
2

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

3

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!

Regards,
Tarun

1 Like