mongodb - 搜索数组信息的总和值 - MongoDB

Question

我将部分 SQL 数据传递给 MongoDB 以提高密集查询的效率。我已经阅读了来自 mongo 的文档，但我仍在努力实现一个查询。

我有一个与客户的集合，其中有他们所有产品的 list 。我试图搜索谁购买了某种药物超过 4 次，但我不知道如何获得该信息，因为它是一个数组，在一个数组内，而我的 mongo knowladge atm 非常有限。

{
"_id" : ObjectId("5c3c71f2760c4f47c701fe14"),
"email" : "jcc@gmail.pt",
"faturas" : [
    {
        "pontos_r" : NumberLong(25),
        "id_c" : NumberLong(1),
        "data_f" : ISODate("2017-12-10T08:41:01Z"),
        "pontos_u" : NumberLong(0),
        "medicamentos" : [
            {
                "categoria" : "Analg�sico",
                "receita" : "N",
                "des" : "Ben-U-Ron 500",
                "qt" : 20,
                "formato" : "granulado",
                "qt_v" : NumberLong(2),
                "pos" : "A12",
                "lab" : "Laborat�rio do Rio Ave",
                "preco_l" : 5.02,
                "un" : "un",
                "preco" : 2.51,
                "preco_v" : 2.51,
                "id" : NumberLong(1),
                "stock" : NumberLong(21)
            },
            {
                "categoria" : "Estatina",
                "receita" : "S",
                "des" : "Sinvastatina",
                "qt" : 30,
                "formato" : "comprimido",
                "qt_v" : NumberLong(1),
                "pos" : "K23",
                "lab" : "Mylan",
                "preco_l" : 16.45,
                "un" : "un",
                "preco" : 16.45,
                "preco_v" : 16.45,
                "id" : NumberLong(6),
                "stock" : NumberLong(25)
            }
        ],
        "data_s" : ISODate("2017-12-10T08:07:00Z"),
        "funcionario" : {
            "tlmv" : "910851961",
            "nome" : "Mariana Pereira Fernandes",
            "ordenado" : 750.27,
            "iban" : "PT 50 2732 5162 12345678909",
            "pass" : "608e01334f5575f10813efa40ce0102f2dc0a75e",
            "niss" : "12345678909",
            "id" : NumberLong(1),
            "cedula" : "12345"
        },
        "id_func" : NumberLong(1),
        "total" : 21.47,
        "id" : NumberLong(3),
        "desconto" : 0
    },
    {
        "pontos_r" : NumberLong(0),
        "id_c" : NumberLong(1),
        "data_f" : ISODate("2018-01-31T19:59:04Z"),
        "pontos_u" : NumberLong(0),
        "medicamentos" : [
            {
                "categoria" : "Estatina",
                "receita" : "S",
                "des" : "Sinvastatina",
                "qt" : 30,
                "formato" : "comprimido",
                "qt_v" : NumberLong(1),
                "pos" : "K23",
                "lab" : "Mylan",
                "preco_l" : 16.45,
                "un" : "un",
                "preco" : 16.45,
                "preco_v" : 16.45,
                "id" : NumberLong(6),
                "stock" : NumberLong(25)
            }
        ],
        "data_s" : ISODate("2018-01-31T19:45:29Z"),
        "funcionario" : {
            "tlmv" : "963789245",
            "nome" : "Jos� Pedro Milhazes Carvalho Pinto",
            "ordenado" : 800.62,
            "iban" : "PT 50 6227 3251 87637898710",
            "pass" : "679951a24ba997db7355e6b63d66ea51c4a1a5ba",
            "niss" : "18746783601",
            "id" : NumberLong(4),
            "cedula" : "76912"
        },
        "id_func" : NumberLong(4),
        "total" : 16.45,
        "id" : NumberLong(7),
        "desconto" : 0
    },
    {
        "pontos_r" : NumberLong(10),
        "id_c" : NumberLong(1),
        "data_f" : ISODate("2018-02-16T11:59:01Z"),
        "pontos_u" : NumberLong(0),
        "medicamentos" : [
            {
                "categoria" : "Estatina",
                "receita" : "S",
                "des" : "Sinvastatina",
                "qt" : 30,
                "formato" : "comprimido",
                "qt_v" : NumberLong(1),
                "pos" : "K23",
                "lab" : "Mylan",
                "preco_l" : 16.45,
                "un" : "un",
                "preco" : 16.45,
                "preco_v" : 16.45,
                "id" : NumberLong(6),
                "stock" : NumberLong(25)
            }
        ],
        "data_s" : ISODate("2018-02-16T11:53:29Z"),
        "funcionario" : {
            "tlmv" : "963789245",
            "nome" : "Jos� Pedro Milhazes Carvalho Pinto",
            "ordenado" : 800.62,
            "iban" : "PT 50 6227 3251 87637898710",
            "pass" : "679951a24ba997db7355e6b63d66ea51c4a1a5ba",
            "niss" : "18746783601",
            "id" : NumberLong(4),
            "cedula" : "76912"
        },
        "id_func" : NumberLong(4),
        "total" : 16.45,
        "id" : NumberLong(9),
        "desconto" : 0
    },
    {
        "pontos_r" : NumberLong(25),
        "id_c" : NumberLong(1),
        "data_f" : ISODate("2018-03-13T13:54:39Z"),
        "pontos_u" : NumberLong(0),
        "medicamentos" : [
            {
                "categoria" : "Antibi�tico",
                "receita" : "S",
                "des" : "Cefotaxima",
                "qt" : 20,
                "formato" : "comprimido",
                "qt_v" : NumberLong(1),
                "pos" : "D26",
                "lab" : "Laborat�rio Ahrro",
                "preco_l" : 8.51,
                "un" : "un",
                "preco" : 8.51,
                "preco_v" : 8.51,
                "id" : NumberLong(4),
                "stock" : NumberLong(22)
            },
            {
                "categoria" : "Estatina",
                "receita" : "S",
                "des" : "Sinvastatina",
                "qt" : 30,
                "formato" : "comprimido",
                "qt_v" : NumberLong(1),
                "pos" : "K23",
                "lab" : "Mylan",
                "preco_l" : 16.45,
                "un" : "un",
                "preco" : 16.45,
                "preco_v" : 16.45,
                "id" : NumberLong(6),
                "stock" : NumberLong(25)
            }
        ],
        "data_s" : ISODate("2018-03-13T13:50:42Z"),
        "funcionario" : {
            "tlmv" : "963789245",
            "nome" : "Jos� Pedro Milhazes Carvalho Pinto",
            "ordenado" : 800.62,
            "iban" : "PT 50 6227 3251 87637898710",
            "pass" : "679951a24ba997db7355e6b63d66ea51c4a1a5ba",
            "niss" : "18746783601",
            "id" : NumberLong(4),
            "cedula" : "76912"
        },
        "id_func" : NumberLong(4),
        "total" : 24.96,
        "id" : NumberLong(11),
        "desconto" : 0
    },
    {
        "pontos_r" : NumberLong(10),
        "id_c" : NumberLong(1),
        "data_f" : ISODate("2018-04-05T23:21:53Z"),
        "pontos_u" : NumberLong(0),
        "medicamentos" : [
            {
                "categoria" : "Estatina",
                "receita" : "S",
                "des" : "Sinvastatina",
                "qt" : 30,
                "formato" : "comprimido",
                "qt_v" : NumberLong(1),
                "pos" : "K23",
                "lab" : "Mylan",
                "preco_l" : 16.45,
                "un" : "un",
                "preco" : 16.45,
                "preco_v" : 16.45,
                "id" : NumberLong(6),
                "stock" : NumberLong(25)
            }
        ],
        "data_s" : ISODate("2018-04-05T22:59:23Z"),
        "funcionario" : {
            "tlmv" : "934567123",
            "nome" : "Pedro Jorge Rito Lima",
            "ordenado" : 800.32,
            "iban" : "PT 50 2751 3262 76598707612",
            "pass" : "3cfa1c281281ffe4f5db2ccfbe7a17f8a9479808",
            "niss" : "14385639201",
            "id" : NumberLong(2),
            "cedula" : "54321"
        },
        "id_func" : NumberLong(2),
        "total" : 16.45,
        "id" : NumberLong(13),
        "desconto" : 0
    }
],
"id" : NumberLong(1),
"nif" : "123456789",
"nome" : "Jo�o da Costa e Campos",
"pass" : "d1925c0ef3f6c8c16ab291510383617148a53979",
"pontos" : NumberLong(0),
"tlmv" : "912345678"

}这是仅来自一位客户的 json 文件，我如何从他的所有产品中总结出某种药物 - “medicamenos.des”？在这种情况下，如果我搜索“Sinvastatina”，它将是肯定的，因为它计数了 5 次。我的预期结果类似于“客户姓名:---”“药品名称---”“计数---”。谢谢你的时间

Answer 1

您可以尝试以下聚合:

db.col.aggregate([
    {
        $unwind: "$faturas"
    },
    {
        $unwind: "$faturas.medicamentos"
    },
    {
        $group: {
            _id: {
                medicine: "$faturas.medicamentos.des",
                client: "$email"
            },
            count: { $sum: 1 }
        }
    },
    {
        $match: {
            count: { $gte: 4 }
        }
    },
    {
        $project: {
            _id: 0,
            clientName: "$_id.client",
            medicineName: "$_id.medicine",
            count: 1
        }
    }
])

哪些输出:

{ "count" : 5, "clientName" : "jcc@gmail.pt", "medicineName" : "Sinvastatina" }

关键是您需要在 $group 中包含 medicine 和 client -ing 条件。然后您可以使用 $project 重命名该字段。另外你需要 $match检查计数是否为 $gte 4.