Skip to content

Instantly share code, notes, and snippets.

@ruslanguns

ruslanguns/query.js

Last active January 12, 2020 03:36
Show Gist options
  • Save ruslanguns/ccded8982e24b28896e6f450cc82f3d0 to your computer and use it in GitHub Desktop.
Save ruslanguns/ccded8982e24b28896e6f450cc82f3d0 to your computer and use it in GitHub Desktop.
Download ZIP
Get stats from MongoDB using Aggregation Pepiline
Raw
query.js
var pastMonth = new Date();
pastMonth.setMonth(pastMonth.getMonth() - 1); // 1 month ago
var ultimasCargas = [
{ $match: { createdAt: { $exists: true } } },
{ $sort: { createdAt: -1 } },
{ $limit: 5 },
{ $project: { brand: 1, productId: 1, price: 1, status: 1, createdAt: 1 } }
];
var ultimasVentas = [
{ $match: { status: 'vendido' } },
{ $match: { soldAt: { $exists: true } } },
{ $sort: { soldAt: -1 } },
{ $limit: 5 },
{ $project: { brand: 1, productId: 1, price: 1, status: 1, createdAt: 1 } }
];
var cargaMesAnterior = [
{
$redact: {
$cond: [
{ $and: [ { $eq: [ { $month: "$createdAt" }, { $month: pastMonth } ] }, { $eq: [ { $year: "$createdAt" }, { $year: pastMonth } ] }] },
"$$KEEP",
"$$PRUNE"
]
},
},
{
$group: {
_id: null,
cantidad: { $sum: '$price' },
unidades: { $sum: 1 },
// datos: { $push: '$$CURRENT'}
}
},
{ $project: { _id: 0, cantidad: 1, unidades: 1 } },
];
var cargaEsteMes = [
{
$redact: {
$cond: [
{ $and: [ { $eq: [ { $month: "$createdAt" }, { $month: new Date() } ] }, { $eq: [ { $year: "$createdAt" }, { $year: new Date() } ] }] },
"$$KEEP",
"$$PRUNE"
]
},
},
{
$group: {
_id: null,
cantidad: { $sum: '$price' },
unidades: { $sum: 1 },
// datos: { $push: '$$CURRENT'}
}
},
{ $project: { _id: 0, cantidad: 1, unidades: 1 } },
];
var cargadas = [
{
$group: {
_id: null,
cantidad: { $sum: '$price' },
unidades: { $sum: 1 },
// datos: { $push: '$$CURRENT'}
}
},
{ $project: { _id: 0, cantidad: 1, unidades: 1 } },
];
var ventasMesAnterior = [
{
$redact: {
$cond: [
{ $and: [ { $eq: [ { $month: "$soldAt" }, { $month: pastMonth } ] }, { $eq: [ { $year: "$soldAt" }, { $year: pastMonth } ] }] },
"$$KEEP",
"$$PRUNE"
]
},
},
{
$group: {
_id: null,
cantidad: { $sum: '$price' },
unidades: { $sum: 1 },
// datos: { $push: '$$CURRENT'}
}
},
{ $project: { _id: 0, cantidad: 1, unidades: 1 } },
];
var ventasEsteMes = [
{ $match: { status: 'vendido' } },
{
$redact: {
$cond: [
{ $and: [ { $eq: [ { $month: "$soldAt" }, { $month: new Date() } ] }, { $eq: [ { $year: "$soldAt" }, { $year: new Date() } ] }] },
"$$KEEP",
"$$PRUNE"
]
},
},
{
$group: {
_id: null,
cantidad: { $sum: '$price' },
unidades: { $sum: 1 },
// datos: { $push: '$$CURRENT'}
}
},
{ $project: { _id: 0, cantidad: 1, unidades: 1 } },
];
var vendidos = [
{ $match: { status: 'vendido' } },
{
$group: {
_id: null,
cantidad: { $sum: '$price' },
unidades: { $sum: 1 },
// datos: { $push: '$$CURRENT'}
}
},
{ $project: { _id: 0, cantidad: 1, unidades: 1 } },
];
var enVenta = [
{ $match: { status: 'a la venta' } },
{
$group: {
_id: null,
cantidad: { $sum: '$price' },
unidades: { $sum: 1 },
// datos: { $push: '$$CURRENT'}
}
},
{ $project: { _id: 0, cantidad: 1, unidades: 1 } },
];
var visitas = [
{
$project: {
settingsUsername: 'admin',
},
},
{ $limit: 1 },
{
$lookup: {
from: 'settings',
localField: 'settingsUsername',
foreignField: 'username',
as: 'settings',
}
},
{
$project: {
general: { $arrayElemAt: [ "$settings.totalViews", 0 ] },
}
},
];
var query = {
$facet: {
ultimasCargas,
ultimasVentas,
cargaMesAnterior,
cargaEsteMes,
cargadas,
ventasMesAnterior,
ventasEsteMes,
vendidos,
enVenta,
visitas,
}
};
var projectData = {
$project: {
visitas: { $arrayElemAt: [ "$visitas.general", 0 ] },
enVenta: { $arrayElemAt: [ "$enVenta", 0 ] },
carga: {
general: { $arrayElemAt: [ "$cargadas", 0 ] },
cargaMesAnterior: { $arrayElemAt: [ "$cargaMesAnterior", 0 ] },
cargaEsteMes: { $arrayElemAt: [ "$cargaEsteMes", 0 ] },
ultimasCargas: "$ultimasCargas"
},
ventas: {
general: { $arrayElemAt: [ "$vendidos", 0 ] },
ventasMesAnterior: { $arrayElemAt: [ "$ventasMesAnterior", 0 ] },
ventasEsteMes: { $arrayElemAt: [ "$ventasEsteMes", 0 ] },
ultimasVentas: "$ultimasVentas"
}
},
}
db.getCollection('bikes').aggregate([query, projectData])
@ruslanguns
Copy link
Author

ruslanguns commented Jan 12, 2020 •
edited
Loading

El resultado es:

/* 1 */
{
    "visitas" : 319,
    "enVenta" : {
        "cantidad" : 10067.5,
        "unidades" : 14.0
    },
    "carga" : {
        "general" : {
            "cantidad" : 10416.5,
            "unidades" : 15.0
        },
        "cargaMesAnterior" : {
            "cantidad" : 2860,
            "unidades" : 4.0
        },
        "cargaEsteMes" : {
            "cantidad" : 7207.5,
            "unidades" : 10.0
        },
        "ultimasCargas" : [ 
            {
                "_id" : ObjectId("5e17c082a93c2631f02d3711"),
                "status" : "a la venta",
                "productId" : "E178143-0",
                "brand" : "TYPO",
                "price" : 230,
                "createdAt" : ISODate("2020-01-10T00:08:34.539Z")
            }, 
            {
                "_id" : ObjectId("5e149cfd58f2f64258fa73cb"),
                "status" : "a la venta",
                "productId" : "E204123",
                "brand" : "Xpro",
                "price" : 900,
                "createdAt" : ISODate("2020-01-07T15:00:13.502Z")
            }, 
            {
                "_id" : ObjectId("5e149cc658f2f64258fa73ca"),
                "status" : "a la venta",
                "productId" : "E204543-0",
                "brand" : "HEDENESS",
                "price" : 35,
                "createdAt" : ISODate("2020-01-07T14:59:18.389Z")
            }, 
            {
                "_id" : ObjectId("5e149caa58f2f64258fa73c9"),
                "status" : "a la venta",
                "productId" : "E210123-1",
                "brand" : "GIGANTE !!!",
                "price" : 300,
                "createdAt" : ISODate("2020-01-07T14:58:50.857Z")
            }, 
            {
                "_id" : ObjectId("5e149c6858f2f64258fa73c8"),
                "status" : "a la venta",
                "productId" : "E208103-0",
                "brand" : "Fippe",
                "price" : 650,
                "createdAt" : ISODate("2020-01-07T14:57:44.330Z")
            }
        ]
    },
    "ventas" : {
        "general" : {
            "cantidad" : 349,
            "unidades" : 1.0
        },
        "ventasEsteMes" : {
            "cantidad" : 349,
            "unidades" : 1.0
        },
        "ultimasVentas" : [ 
            {
                "_id" : ObjectId("5e0394bf275ced4c3cb13946"),
                "status" : "vendido",
                "productId" : "E189212-0",
                "brand" : "BPRO",
                "price" : 349,
                "createdAt" : ISODate("2017-12-25T16:56:31.473Z")
            }
        ]
    }
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment