Inner join within same collection mongodb

Question

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.

It's not clear to me, what exactly is the output you want? – Tom Slabbaert Jul 12 '22 at 09:42 — Tom Slabbaert, Jul 12 '22 at 09:42
I have added expected output too. Hope it is clear now. – Udhayha Karthik Jul 12 '22 at 10:03 — Udhayha Karthik, Jul 12 '22 at 10:03

nimrod serok · Answer 1 · 2022-07-12T10:21:27.023

One option is:

$group by the feature and software.
keep only documents with both releases.
$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

Inner join within same collection mongodb

1 Answers1