Agak kesulitan dengan kueri mongodb. Basis data mongo saya memiliki struktur berikut:

name: String
test: String
competences: [{name: String, code: String, value: Float}]
subcompetences: [{name: String, code: String, value: Float}]

Permintaan saya terlihat seperti ini:

async function getAggregatedDataForCompetences(filter, category) {
    return await getCollection('competences').aggregate([
    {$match: { $or: filter }},
    {$unwind:  "$competences" },
    {$group: {
        _id: "$_id",
        code:  { $first: "$competences.code" },
        name:  { $first: "$competences.name"},
        avgValue: { $avg: "$competences.value" },
        subcompetences:  { $first: "$subcompetences"},
        }
    },
    {$unwind:  "$subcompetences" },
    {$group: {
        _id: "$subcompetences.code",
        code:  { $first: "$subcompetences.code" },
        name:  { $first: "$subcompetences.name"},
        avgValue: { $avg: "$subcompetences.value" },
    }}
    ]).toArray();
}

Apa yang saya coba lakukan adalah melepas larik (kompetensi) pertama untuk semua elemen, mengelompokkannya berdasarkan dan menghitung nilai rata-rata untuk setiap item. Prosedur yang sama diulangi untuk larik objek subkompetensi berikut. Akibatnya saya hanya mendapatkan nilai rata-rata untuk larik subkompetensi terakhir. Apakah Anda tahu bagaimana saya bisa mencapai hasil berikut:

{
   competences: [{name: String, code: String, avgValue: Float}],
   subcompetences: [{name: String, code: String, avgValue: Float}]
}
1
user2188452 30 Oktober 2019, 23:00

1 menjawab

Jawaban Terbaik

$facet untuk menyelamatkan -- operator "multigrup". Diberikan masukan seperti ini:

var r =
[
{
    "_id" : 0,
    "name": "N1",
    "competences": [
{name: "AAA", code: "A", value: 1.1},
{name: "BBB", code: "B", value: 2.2},
{name: "CCC", code: "C", value: 3.3}
                    ],
    "subcompetences": [
{name: "DDD", code: "D", value: 4.4},
{name: "EEE", code: "E", value: 5.5},
{name: "FFF", code: "F", value: 6.6}
]
        }

,{
    "_id" : 1, "name": "N2",
    "competences": [
{name: "AAA", code: "A", value: 9.9},
{name: "BBB", code: "B", value: 8.8},
{name: "KKK", code: "K", value: 11.11}
                    ],
    "subcompetences": [
{name: "FFF", code: "F", value: 4.9},
{name: "GGG", code: "G", value: 6.7}
]
        }
 ];

Maka $facet akan memungkinkan Anda melakukan dua grup "secara paralel". Memang, Anda dapat melakukan dua atau lebih pipa secara bersamaan (beberapa batasan berlaku):

db.foo.aggregate([
{$facet: {
  "avg_competences": [
      {$unwind: "$competences"}
      ,{$group: {_id: "$competences.code",
           name: {$first: "$competences.name"},
           count: {$sum: 1},
           avgval: {$avg: "$competences.value"},
        }}
           ]

  ,"avg_subcompetences": [
      {$unwind: "$subcompetences"}
      ,{$group: {_id: "$subcompetences.code",
           name: {$first: "$subcompetences.name"},
           count: {$sum: 1},
           avgval: {$avg: "$subcompetences.value"},
        }}
           ]
    }
}

// The output of the stage above will be a *single* doc with two fields, 
// avg_competence and avg_subcompetences.  Let's add more fields to this doc!
,{$addFields: {N: {$reduce: {
                input: {$concatArrays: ["$avg_competences","$avg_subcompetences"]},
                initialValue: 0,
                in:{$sum: [ "$$value", "$$this.count"]}
            }}
    }}


   ]);

Untuk menghasilkan:

{
    "avg_competences" : [
        {
            "_id" : "K",
            "name" : "KKK",
            "count" : 1,
            "avgval" : 11.11
        },
        {
            "_id" : "C",
            "name" : "CCC",
            "count" : 1,
            "avgval" : 3.3
        },
        {
            "_id" : "B",
            "name" : "BBB",
            "count" : 2,
            "avgval" : 5.5
        },
        {
            "_id" : "A",
            "name" : "AAA",
            "count" : 2,
            "avgval" : 5.5
        }
    ],
    "avg_subcompetences" : [
        {
            "_id" : "G",
            "name" : "GGG",
            "count" : 1,
            "avgval" : 6.7
        },
        {
            "_id" : "F",
            "name" : "FFF",
            "count" : 2,
            "avgval" : 5.75
        },
        {
            "_id" : "E",
            "name" : "EEE",
            "count" : 1,
            "avgval" : 5.5
        },
        {
            "_id" : "D",
            "name" : "DDD",
            "count" : 1,
            "avgval" : 4.4
        }
    ],
    "N" : 11
}

2
Buzz Moschetti 6 November 2019, 19:35