How can I calculate the active setting?-CodePudding

I have a MongoDB with 3 collections. Here are the 3 collections each with an example of a document it contains:

tag

_id: ObjectId('61b873ec6d075801f7a97e18')
name: 'TestTag'
category: 'A'

computer

_id: ObjectId('6098c5ab615d9e23543d0f6f')
name: 'TestComputer'
category: 'A'
tags: [
    'TestTag'
]

setting

_id: ObjectId('61e56339b528bf009feca149')
name: 'FirstSetting'
category: 'A'
priority: 1
tags: [
    ObjectId('61b873ec6d075801f7a97e18')
]

_id: ObjectId('61e56339b528bf009feca150')
name: 'SecondSetting'
category: 'A'
priority: 2
tags: [
    ObjectId('61b873ec6d075801f7a97e18')
]

The idea is that you can create a tag and tag your computers and settings, but only within the same category:

Example: A tag of category 'A' can only exist in computers and settings of category 'A'

A computer can only have one active setting at a time. The active setting is the one that shares the same tag. And if the device share tags with multiple settings, the one with the highest priority wins.

So based on those rules, I would like to add a new activeSetting property to each of my devices, like this:

computer

_id: ObjectId('6098c5ab615d9e23543d0f6f')
name: 'TestComputer'
category: 'A'
tags: [
    'TestTag'
]
activeSetting: ObjectId('61e56339b528bf009feca149')

What I've tried:

I've tried to use lookup to get a list of computers and a list of settings of each tag, but I don't really know where to go from there.

CodePudding user response：

One option is:

db.computer.aggregate([
  {$match: {_id: ObjectId("6098c5ab615d9e23543d0f6f")}},
  {$lookup: {
      from: "settings",
      localField: "category",
      foreignField: "category",
      pipeline: [
        {$sort: {priority: 1}},
        {$limit: 1},
        {$project: {_id: 1}}
      ],
      as: "activeSetting"
  }},
  {$set: {activeSetting: {$first: "$activeSetting._id"}}}
])

See how it works on the playground example