Search code examples
mongodbmongodb-atlasmongodb-charts

Mongodb Atlas Charts - Show grouped count in different columns

I have a collection of "ofertas" in my DB and the data has this structure:

{
    "_id" : ObjectId("6057a995e5a26c119d254f35"),
    "empresa" : {
        "direccion" : {
            "direccion" : "concepcion arenal",
            "cp" : "36950",
            "provincia" : "pontevedra",
            "poblacion" : "Moaña",
            "pais" : "espana"
        },
        "nombre" : "caru sl"
    },
    "nombre_oferta" : "Offer Name",
    "referencia_interna" : "111222",
    "fecha_publicacion" : ISODate("2021-03-21T23:00:00.000Z"),
    "fecha_caducidad" : ISODate("2021-04-19T22:00:00.000Z"),
    "descripcion" : "Offer description",
    "estado" : "caducada",
    "pagada" : true,
    "userId" : "XXX",
    "candidatos" : [],
    "createdAt" : ISODate("2021-03-21T20:16:21.438Z"),
    "updatedAt" : ISODate("2021-04-20T22:08:14.221Z"),
    "__v" : 0
}

And I have this MongoDB query:

[
    { $sortByCount: "$empresa.nombre" },
    {$group: {
         _id: 0,
         empresas_ofertas:{$push: {
             empresa:"$_id",
             ofertas:"$count",
         }},
    }},
    { $unwind: "$empresas_ofertas"}
]

That groups the number of "ofertas", like this:

query result

And I want to show data in MongoDB Atlas Charts as separated columns with the count of "ofertas" for each object of the "empresas_ofertas" array, but the chart just show one column with the total amount of "ofertas":

atlas chart

I changed the Y axis Aggregate from SUM to COUNT BY VALUE but it groups the data with the items that have the same amount of "ofertas". What I'm trying to do is to show all the objects in the "empresas_ofertas" array with the count of ofertas: first column should be 60 (IXAS Value SL), second columns should be 53 (caru sl)...

What am I doing wrong?

Solution

  • STEP 1 - CREATING A QUERY

    Your query is good. It will return the data that you need to create a requested chart. I tested it, and you will got as a response data in this format:

    [
  {
    "_id": 0,
    "empresas_ofertas": {
      "empresa": "caru sl 2",
      "ofertas": 2
    }
  },
  {
    "_id": 0,
    "empresas_ofertas": {
      "empresa": "caru sl 1",
      "ofertas": 1
    }
  }
]

    Here is the working example: https://mongoplayground.net/p/Gpn9trcgfsK

    STEP 2 - DEFINING AXES

    As it can be seen in your picture, in the left menu list you have the _id field and empresas_ofertas object field with nested empresa and ofertas fields. That is the data structure that is returned from the query.

    On the right of that list, there a section where you should define X Axis and Y Axis from the available data (_id, empresas_ofertas.empresa and empresas_ofertas.ofertas). Note that right now, you didn't specify the X Axis at all, as you can see in the image.

    All you have to do is to Drag-and-Drop empresa nested field to the X Axis, and ofertas to the Y Axis. That will display your requested chart.