MongoQuery for many-many

[Supriya_Bansal]

I am having trouble writing a mongo query to achieve the following:

    {

    "highLevel":{

        "hl_code": 123,

        "text" : "headache"

        },

        "highLevel":{

            "hl_code": 124,

            "text" : "breathing"

            }

    }

    {

        "lowLevel":{

            "ll_Code": 345,

            "text" : "aspirin"

        },

        "lowLevel":{

            "ll_Code": 243,

            "text" : "advil"

        }

    }

    {

        "high_low":{

          "hl_code" : 123,

          "ll_code" : 345,

        },

        

        "high_low":{

            "hl_code" : 123,

            "ll_code" : 243,

          },

          "high_low":{

            "hl_code" : 124,

            "ll_code" : 243,

          }

    }

result:

    {

        "highLevel":{

            "hl_code": 123,

            "text" : "headache",

            "lowLevel":[{

                "ll_Code": 243,

                "text" : "advil"

            },{

                "ll_Code": 345,

                "text" : "aspirin"

            }]

        }

    }

[PBeliy (Pavel Beliy)]

This just needs a double $lookup stage. For example

/*
 * Requires the MongoDB Node.js Driver
 * https://mongodb.github.io/node-mongodb-native
 */

// aggregation pipeline definition
// runs on highLevel collection
const agg = [
  {
    '$lookup': { // first join with high_low to get all the ll_codes
      'from': 'high_low', 
      'localField': 'hl_code', 
      'foreignField': 'hl_code', 
      'as': 'lowLevels' // temporary field
    }
  }, {
    '$lookup': { // now join with lowLevel
      'from': 'lowLevel', 
      'localField': 'lowLevels.ll_code', 
      'foreignField': 'll_Code', 
      'as': 'lowLevel'
    }
  }, { // remove temporary field
    '$unset': [
      'lowLevels'
    ]
  }
];

// sample code
MongoClient.connect(
  'mongodb://localhost:27017/?readPreference=primary',
  { useNewUrlParser: true, useUnifiedTopology: true },
  function(connectErr, client) {
    assert.equal(null, connectErr);
    const coll = client.db('testDb').collection('highLevel');
    coll.aggregate(agg, (cmdErr, result) => {
      assert.equal(null, cmdErr);
    });
    client.close();
  });

[Supriya_Bansal]

@PBeliy

aggregate

This was really helpful!! thank you!

[michael_hoeller (Michael Höller)]

Hello, @Supriya_Bansal

based on the data you provided the solution from @PBeliy is great an works fine.
If you like you can describe your use case here, since which a lager amount of document this will not be a super fast query. There are various methods to get this fixed by changing you data model. A suggestion depends on you use case and how you access your data. Depending on this there is a good chance to completely get rid of the $lookups.
This video provides a short overview on many-to-many relations. Potential Patterns could be external reference, subset, … The blog post Building with Patterns provides a quick overview about standard patterns.

Cheers,
Michael

[Supriya_Bansal]

Thank you for the response!!
My use case is as follows:
I have been given around 10 raw json files that are either related to each other in a many-may or in a one-many relationship. Hierarchy is something as follows:

highlevelgroup.json
highlevelgroup_highlevel.json
highlevel.json
highlevel_preferredlevel.json
preferredlevel.json

Requirement is : Browse through the hierarchy, perform quick searches and support multiple search criterias.

I am planning to remove the intermediary files and have the following structure:

highlevelgroup(contains nested high level)
highlevel(contains nested preferred level)

In this way I can sort of achieve hierarchy and if I need to perform searches I can look into the aggregate document.

I would love to heard ideas on how to deal with this scenario.

[michael_hoeller (Michael Höller)]

Hello @Supriya_Bansal

the actual use case is still unclear to me, however your notes trigger some signals. The most important thing to do BEFORE you apply any schema is to identify your workload (what is accessed how and how often incl. the amounts of data) than you want to define the references and in the final step you apply a pattern. So I am a little bit reluctant to suggest a solution since we miss quite a bit of the basics. But I like to support you to find the best approach.

To get deeper into the data modeling I suggest to attend the free MongoDB University Class: M320 Data Modeling . You may also can checkout:

• Data Modeling Introduction
• Data Model Design
• great blog post from @Ken_Alger and @Daniel_Coupal Building with patterns: A summary
  This overview is copied out of their post:

Some more thoughts on your use case:
I understand that you have different levels which relate to each other. There might also be a potential tree pattern. This can work well together with a recursive browse through the level → $graphLookup
Depending on the type of data even embedding of e.g. a group can be a pattern combined with external references (ideally you make the reference value the _id of the referenced collection.

Plenty ideas, but as mentioned this is step 3. First get the workload and the references, best take the M320 class. Then you will look at that this from a different perspective.

Cheers
Michael