Mongodb find distinct value with range

Question

I have a mongodb database where I have two collections - elements and properties. The elements look something like this -

{ _id: "someElementId", name: "Iron", type: "metal" }

And the properties look like this -

{ _id: "somePropertyId", propName: "molecular weight", propType: "number", unit: null }

Each element can have multiple properties and a value corresponding to the property. For example iron can have properties molecular weight, color, atomic weight etc.

For that I created another collection where I stored the element id and corresponding property id and its value -

{ elementId: "someElementId", propId:  "somePropertyId", value: 55.845 }

Now I want to find the names of all the unique properties and the range of their values that occurs in the database. So for example, if 1 is the lowest value corresponding the above property and 100 is the highest value, I want something like -

[ { name: "molecular weight", range: { min: 1, max: 100 } } ]

I can get the distinct properties and iterate over them to get the range, but I was wondering if there's a better way. Or maybe this table structure is not efficient enough?

Answer 1

Please check if this would work for you:

db.collection.aggregate([
  {
    $group: {
      _id: "$propId",
      min: {
        $min: "$value"
      },
      max: {
        $max: "$value"
      }
    }
  },
  {
    $lookup: {
      from: "properties",
      localField: "_id",
      foreignField: "_id",
      as: "name"
    }
  },
  {
    $unwind: "$name"
  },
  {
    $project: {
      _id: 0,
      "name": "$name.propName",
      "range": {
        "min": "$min",
        "max": "$max"
      }
    }
  }
])

Mongo Playground