Home > Blockchain >  Avoiding Empty Curly Braces in MongoDB Aggregation
Avoiding Empty Curly Braces in MongoDB Aggregation

Time:11-05

i'm trying to display a mongodb aggregation result via react chartjs. part of my problem is i'm not achieving the correct aggregation output in the backend.

current aggregation output which contains unwanted empty curly braces

[{
  "_id":"Fubar",
  "A_set":[{"A":"Y"},{"A":"N"},{},{}],
  "A_count_set":[{"A_count":1},{"A_count":1},{},{}],
  "B_set":[{},{},{"B":"N"},{"B":"Y"}],
  "B_count_set":[{},{},{"B_count":1},{"B_count":1}]},
{
"_id":"Fubar2",
  "A_set":[{"A":"Y"},{"A":"N"},{},{}],
  "A_count_set":[{"A_count":1},{"A_count":1},{},{}],
  "B_set":[{},{},{"B":"N"},{"B":"Y"}],
  "B_count_set":[{},{},{"B_count":1},{"B_count":1}]
}]

i'm trying to achieve this target aggregation output which lacks the empty curly braces

[{
  "_id":"Fubar",
  "A_set":[{"A":"Y"},{"A":"N"}],
  "A_count_set":[{"A_count":1},{"A_count":1}],
  "B_set":[{"B":"N"},{"B":"Y"}],
  "B_count_set":[{"B_count":1},{"B_count":1}]},
{
"_id":"Fubar2",
  "A_set":[{"A":"Y"},{"A":"N"}],
  "A_count_set":[{"A_count":1},{"A_count":1}],
  "B_set":[{"B":"N"},{"B":"Y"}],
  "B_count_set":[{"B_count":1},{"B_count":1}]
}]

pipeline manipulation

{$facet: {
      A_branch: [
        {$group: {
          _id: {
            Q_id: "$A_id",
            A_id: "$A_id"
          },
          A_count: {$sum: 1}
        }}
      ],
      B_branch: [
        {$group: {
          _id: {
            Q_id: "$Q_id",
            B_id: "$B_id"
          },
          B_count: {$sum: 1}
        }}
      ]
    }},
    {$project: {
      combined_group: {$setUnion: ['$A_branch','$B_branch']}
    }},
    {$unwind: '$combined_group'},
    {$lookup:
      {
        from: "Q",
        localField: "combined_group._id.Q_id",
        foreignField: "_id",
        as: "QRef" 
      }
    },
    {$unwind: "$QRef" },
    {$lookup:
      {
        from: "A",
        localField: "combined_group._id.A_id",
        foreignField: "_id",
        as: "ARef" 
      }
    },
    {$unwind: {path:"$ARef", preserveNullAndEmptyArrays: true} },
    {$lookup:
      {
        from: "B",
        localField: "combined_group._id.B_id",
        foreignField: "_id",
        as: "BRef" 
      }
    },
    {$unwind: {path:"$BRef", preserveNullAndEmptyArrays: true} },
    {$group: {
      _id: "$QRef.text",
      A_set: {
        $push: {
          A: "$ARef.value"
        }
      },
      A_count_set: {
        $push: {
          A_count: "$combined_group.A_count"
        }
      },
      B_set: {
        $push: {
          B: "$BRef.value"
        }
      },
      B_count_set: {
        $push: {
          B_count: "$combined_group.B_count"
        }
      }
    }}

aggregation input

{
        "_id" : ObjectId("618..."),
        "Q_id" : ObjectId("618..."),
        "B_id" : ObjectId("618..."),
        "A_id" : ObjectId("618...")
}
{
        "_id" : ObjectId("618..."),
        "Q_id" : ObjectId("618..."),
        "B_id" : ObjectId("618..."),
        "A_id" : ObjectId("618...")
}
{
        "_id" : ObjectId("618..."),
        "Q_id" : ObjectId("618..."),
        "B_id" : ObjectId("618..."),
        "A_id" : ObjectId("618...")
}
{
        "_id" : ObjectId("618..."),
        "Q_id" : ObjectId("618..."),
        "B_id" : ObjectId("618..."),
        "A_id" : ObjectId("618...")
}

CodePudding user response:

use $filter in the end of pipeline

db.collection.aggregate([
  {
    "$set": {
      "A_set": {
        "$filter": {
          "input": "$A_set",
          "as": "x",
          "cond": { "$ne": [ "$$x", {}] }
        }
      }
    }
  },
  {
    "$set": {
      "A_count_set": {
        "$filter": {
          "input": "$A_count_set",
          "as": "x",
          "cond": { "$ne": [ "$$x", {}] }
        }
      }
    }
  },
  {
    "$set": {
      "B_set": {
        "$filter": {
          "input": "$B_set",
          "as": "x",
          "cond": { "$ne": [ "$$x", {}] }
        }
      }
    }
  },
  {
    "$set": {
      "B_count_set": {
        "$filter": {
          "input": "$B_count_set",
          "as": "x",
          "cond": { "$ne": [ "$$x", {}] }
        }
      }
    }
  }
])

mongoplayground

  • Related