Having issue with aggregation in MongoDB-CodePudding

I'm using MongoDb with Node.js. And I'm having trouble with aggregation.This is the Example of data in Collection in Database:

{
 "name": "abcdef",
 "address": ghijk,
 "reli":"A",
 "prov:"a" ,
}

{
"name": "xyz",
 "address": "vwz",
 "reli":"B",
 "prov:"b" ,
}

{
"name": "qwe",
 "address": "rty",
 "reli":'C',
 "prov:"c" ,
},
 {
 "name": "abcdef",
 "address": ghijk,
 "reli":"A",
 "prov:"a" ,
}
{
"name": "hat",
 "address": "ate",
 "reli":'C',
 "prov:"c" ,
},

This is my query to count:

const count = await db.aggregate([
    {
      $facet: {
        "reli": [
          { $group: { _id: '$reli', count: { $sum: 1 } } }
        ],
        "prov": [
          { $group: { _id: '$prov', count: { $sum: 1 } } }
        ],

This is my result of the query:

  [
    {
        "reli": [
            {
                "_id": "A",
                "count": 2
            },
            {
                "_id": "B",
                "count": 1
            },
            {
            "_id": "C",
            "count": 2
             }
    "prov": [
            {
                "_id": "a",
                "count": 2
            },
            {
                "_id": "b",
                "count": 1
            },
            {
            "_id": "c",
            "count": 2
             }

}]

I want to aggregate this data. And only want values for every reli and prov and it's count in my results.

Expecting Output:

    [
    
    "reli":{
    
    A:2  // As my Collection has 2 "A" And 2 is it's count
    C:2  // As there is 2 "C" in Collection.

    }

    "prov":{
    c:2 //As there are 2 "c" in Collection. 
    b:1 //As there are 1 "b" in Collection
     }
]

CodePudding user response：

You can use $arrayToObject to get what you want here, creating keys from your values. For example:

db.collection.aggregate([
  {
    $facet: {
      reli: [
        {$group: { _id: "$reli", v: {$sum: 1}}},
        {$project: {v: 1, k: "$_id", _id: 0}}
      ],
      prov: [
        {$group: {_id: "$prov", v: {$sum: 1}}},
        {$project: {v: 1, k: "$_id", _id: 0}}
      ]
    }
  },
  {
    $project: {
      prov: {$arrayToObject: "$prov"},
      reli: {$arrayToObject: "$reli"}
    }
  }
])

Playground example

If there is a correlation between the reli and prov group, as in your example, you can avoid the $facet, group once, count once and only project twice.

CodePudding user response：

You may try to use $group like this, the output isn't exactly like expected, but it may help you :

const reli = await db.collection.aggregate([
  {
    '$group': {
      '_id': '$reli', 
      'count': {
        '$sum': 1
      }
    }
  }, {
    '$project': {
      '_id': 0, 
      'reli': '$_id', 
      'count': 1
    }
  }
])

const prov = await db.collection.aggregate([
  {
    '$group': {
      '_id': '$prov', 
      'count': {
        '$sum': 1
      }
    }
  }, {
    '$project': {
      '_id': 0, 
      'prov': '$_id', 
      'count': 1
    }
  }
])

const res = {
  reli,
  prov
};

output :

{
  "prov": [
    { "count": 2, "prov": "a" },
    { "count": 1, "prov": "b" },
    { "count": 2, "prov": "c" }
  ],
  "reli": [
    { "count": 2, "reli": "A" },
    { "count": 2, "reli": "C" },
    { "count": 1, "reli": "B" }
  ]
}

From this data, if you need it, you could re write key:value, for exact data model as you asked.