Searching for sum value of array information - MongoDB

Question

im passing part of a SQL databe do MongoDB to improve efficiency in intensive querys. I've read the docs from mongo, but i'm still struggling implementing one query.

I've a collection with the costumers where they have the list of all their factures. Im trying to search who has bought over 4 times a certain medicine, but i have no idea how can i reach that information since it is an array, inside an array and my mongo knowladge is very limited atm.

{
"_id" : ObjectId("5c3c71f2760c4f47c701fe14"),
"email" : "[email protected]",
"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"

} This is the json file from one costumer only, how can i sum a certain medicine - "medicamenos.des"- from all his factures? In this case if i searched for "Sinvastatina" it would be positive since it counts 5 times. My expected result is something like "Cliente name: ---" "Medicine name ---" "Count ---". Thx for your time

mickl · Accepted Answer · 2019-01-14 16:33:41Z

1

You can try following aggregation:

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
        }
    }
])

which outputs:

{ "count" : 5, "clientName" : "[email protected]", "medicineName" : "Sinvastatina" }

The key thing is that you need to include both medicine and client in your $group-ing condition. Then you can use $project to rename that fields. Additionally you need $match to check if count is $gte 4.

edited Jan 14, 2019 at 16:33

answered Jan 14, 2019 at 16:21

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ashh · Accepted Answer · 2019-01-14 16:25:54Z

1

You need to double $unwind the nested arrays and then $group to count the specific 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 }
  }}
])

edited Jan 14, 2019 at 16:25

answered Jan 14, 2019 at 16:17

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

3 Comments

Pedro Lima Over a year ago

It is counting 7 when it appears to only have 5 :/ Btw, the print is kinda messed up, printing "_id" : "faturas.medicamentos.des"

Ashh Over a year ago

What I am getting mongoplayground.net/p/tlnPWPMZszB. Have a look

Pedro Lima Over a year ago

Oh i get it, here i only posted one cliente, when i have vary on my DB. It should separate each cliente, and not sum them all. But the output should be like what you got on the playground

Collectives™ on Stack Overflow

Searching for sum value of array information - MongoDB

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related