时间戳

首页 > 解决方案 > 搜索数组信息的总和值 - MongoDB

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

问题描述

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

我和客户有一个收藏，他们有他们所有的制造清单。我试图搜索谁购买了超过 4 次某种药物，但我不知道我如何才能获得该信息，因为它是一个数组，在一个数组内，而且我的 mongo 知识非常有限。

{
"_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 次。我的预期结果类似于“客户名称：---”“药物名称 ---”“计数 ---”。谢谢你的时间

标签： mongodbmongodb-queryaggregation-framework

解决方案

您需要$unwind将嵌套数组加倍，然后$group计算特定的faturas.medicamentos.des

db.collection.aggregate([
  { "$match": { "faturas.medicamentos.des": "Sinvastatina" }},
  { "$unwind": "$faturas" },
  { "$unwind": "$faturas.medicamentos" },
  { "$match": { "faturas.medicamentos.des": "Sinvastatina" }},
  { "$group": {
    "_id": "faturas.medicamentos.des",
    "clientName": { "$first": "$email" },
    "count": { "$sum": 1 }
  }}
])

推荐阅读