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
}