How to group by in mongoose for an inner populated field

Viewed 57

I have tried to search high and low for this but have had no luck on how to get this done.

I need to group by by a field that is a sub field of a sub field. This is my schema

const BoxSchema = new mongoos.Schema({
    name: {
    type: String,
    required: true,
  },

   // List of workflows inside one Box
   workflows: [{
    type: ObjectId,
    ref: 'Workflow',
  }],
})

const WorkflowSchema = new mongoose.Schema({
   name: {
    type: String,
    required: true,
   }, 

    // The box the workflow belongs to
    boxId: {
    type: ObjectId,
    required: true,
    ref: 'Box',
   },

    // List of cards inside the workflow
    cards: {
    type: [ObjectId],
    ref: 'Card',
  },

    // List of groups inside the workflow
    groups: {
    type: [ObjectId],
    ref: 'Group',
  },
})

const GroupSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
  },

  // The box the group belongs to
  boxId: {
    type: ObjectId,
    required: true,
    ref: 'Box',
  },

  // The workflow the group belongs to
  workflow: {
    type: ObjectId,
    required: true,
    ref: 'Workflow',
  },
})

const CardSchema = new mongoose.Schema({
  title: {
    type: String,
    required: true,
  },

  // The Workflow the card belongs to
  workflow: {
    type: ObjectId,
    ref: 'Workflow',
  },

  // The Group the card belongs to
  group: {
    type: ObjectId,
    ref: 'Group',
  },

  properties: [{
    name: {
      type: String,
      required: true,
    },
    items: [{
      value: { type: String, default: '' },
      isSelected: { type: Boolean, default: false },
    }],
  }],

  isTemplate: {
    type: Boolean,
  },
})

I would like to get the list of all Workflows inside a Box grouped by the field Group inside each card. Something like this maybe?

workflows: [
   {
        "id": "5e39384c7921940fa8b5500a",
        "name": "Input",
        "group": [
            "cards": [...]
         ],
        "group": [
            "cards": [...]
         ],
        "group": [
            "cards": [...]
         ],
   }
]

I tried a lot with $lookup, or $match, I only get empty arrays so I am finally resorting to asking here. Would appreciate any guidance.

P.S. I know the schema can be made a different way but the problem is that the Group schema is a new addition and thats why I am deciding to tag it inside each card because there's a possibility the card has no Group but can be part of a Workflow

Thankyou.

EDIT:

Sample data for each collection:

BOX

{
    "_id" : ObjectId("5e3934047921940fa8b54ffd"),
    "cards" : [
        ObjectId("5e4ff643a966a14d44e26485"), 
        ObjectId("5e4ff647a966a14d44e26494"), 
        ObjectId("5e4ff64ba966a14d44e264a3"), 
        ObjectId("5e4ff653a966a14d44e264ae"), 
    ],
    "workflows" : [ 
        ObjectId("5e39384c7921940fa8b5500a"), 
        ObjectId("5e39385f7921940fa8b5500c"), 
        ObjectId("5e39386a7921940fa8b5500e"), 
    ],
    "name" : "My Workplace",
    "createdAt" : ISODate("2020-02-04T09:06:12.686Z"),
    "updatedAt" : ISODate("2020-03-02T15:22:21.563Z"),
    "__v" : 0
}

WORKFLOW

{
    "_id" : ObjectId("5e39384c7921940fa8b5500a"),
    "cards" : [ 
        ObjectId("5e553667ed55fa44104d860b"), 
        ObjectId("5e5777e4ca0d275bc8cb880a"), 
        ObjectId("5e53c6a7bf63a0169c7fbdb6"), 
        ObjectId("5e393c7b7921940fa8b550d5"), 
    ],
    "name" : "Input",
    "boxId" : ObjectId("5e3934047921940fa8b54ffd"),
    "createdAt" : ISODate("2020-02-04T09:24:28.753Z"),
    "updatedAt" : ISODate("2020-03-04T11:04:47.716Z"),
    "__v" : 0
}

I have nothing for group since it is a new schema

CARD

{
    "_id" : ObjectId("5e3938c37921940fa8b55012"),
    "title" : "Main Article",
    "isTemplate" : true,
    "box" : ObjectId("5e3934047921940fa8b54ffd"),
    "properties" : [ 
        {
            "_id" : ObjectId("5e3939a57921940fa8b55031"),
            "name" : "Größe",
            "items" : [ 
                {
                    "_id" : ObjectId("5e3939c67921940fa8b55035"),
                    "value" : "Large - 1000 Wörter",
                    "isSelected" : true
                }, 
                {
                    "_id" : ObjectId("5e3939d27921940fa8b55036"),
                    "value" : "Medium - 650 Wörter",
                    "isSelected" : false
                }, 
                {
                    "_id" : ObjectId("5e3939da7921940fa8b55037"),
                    "value" : "Small - 250 Wörter",
                    "isSelected" : false
                }
            ]
        }
    ],
    "createdAt" : ISODate("2020-02-04T09:26:27.489Z"),
    "updatedAt" : ISODate("2020-02-04T09:31:40.182Z"),
    "__v" : 0
}
0 Answers
Related