CODEWITHSUNDEEP
mongodbinner-joinintersection
Udhayha Karthik
Udhayha Karthik

Reputation: 115

Inner join within same collection mongodb

I have a document for each feature (eg:xxx) in a software (eg:opac) release (eg:1.0)

{
"software":"opac",
"release":"1.0",
"feature":"xxx",
"featureVersion":"3.2"
},
{
"software":"opac",
"release":"1.0",
"feature":"yyy",
"featureVersion":"1.2"
},
{
"software":"opac",
"release":"1.0",
"feature":"zzz",
"featureVersion":"8.9"
},
{
"software":"opac",
"release":"3.0",
"feature":"xxx",
"featureVersion":"6.1"
},
{
"software":"opac",
"release":"3.0",
"feature":"zzz",
"featureVersion":"10.0"
}

Now I want to retrieve all common features from software opac, release 1.0 and 3.0 and the corresponding featureVersion too. So I need, feature, 1.0 featureVersion and 3.0 featureVersion

Expected output:

{
"software":"opac",
"common feature":"xxx",
"Opac 1.0 featureVersion":"3.2",
"Opac 3.0 featureVersion":"6.1"
},
{
"software":"opac",
"common feature":"zzz",
"Opac 1.0 featureVersion":"8.9",
"Opac 3.0 featureVersion":"10.0"
}

Please help me frame the query.

Upvotes: 0

Views: 186

Answers (1)

nimrod serok
nimrod serok

Reputation: 16033

One option is:

  1. $group by the feature and software.
  2. keep only documents with both releases.
  3. $cocnat the values and $arrayToObject use to format wanted data.
db.collection.aggregate([
  {$group: {
      _id: {feature: "$feature",  software: "$software"},
      data: {$push: {release: "$release", featureVersion: "$featureVersion"}}
    }
  },
  {$match: {data: {$size: 2}}},
  {$project: {
      _id: 0,
      data: {
        $mergeObjects: [
          {$arrayToObject: {
              $map: {
                input: "$data",
                in: {
                  k: {
                    $concat: ["$_id.software", " ", "$$this.release", " featureVersion"]
                  },
                  v: "$$this.featureVersion"
                }
              }
            }
          },
          {
            software: "$_id.software",
            "common feature": "$_id.feature"
          }
        ]
      }
    }
  },
  {$replaceRoot: {newRoot: "$data"}}
])

See how it works on the playground example

Upvotes: 1

Related Questions