mongoDb groupby with array object field-CodePudding

I have searched a lot to use groupby based on the array field value, but I didn't get proper results in google, so I'm posting here.

I have tried my best, it works 50% need to correct my query can anyone help me with this

I have a database value like

{"_id": "62b0bec8922dc767f8b933b4",
        "seatSeletion": [{
            "rowNo":  0,
            "columnNo": 0,
            "seatNo":  3
        }, {
            "rowNo": 0,
            "columnNo": 1,
            "seatNo": 4
        }],
        "movieId": "62af1ff6cb38656a4ffe36aa",
        "movieDate": "2022-06-20T18:14:38.133 00:00",
        "movieTiming": "1:30 p.m",
    },
    {"_id": "62b0b91560f57e0cb220db02","seatSeletion": [{
            "rowNo":  0,
            "columnNo": 0,
            "seatNo":  1
        }, {
            "rowNo": 0,
            "columnNo": 1,
            "seatNo": 2
        }],
        "movieId": "62af1ff6cb38656a4ffe36aa",
        "movieDate": "2022-06-20T18:14:38.133 00:00",
        "movieTiming": "1:30 p.m",
    }

expected output

{
            "seatSeletion": [
                {
                    "rowNo": 0,
                    "columnNo": 0,
                    "seatNo": 1
                },
                {
                    "rowNo": 0,
                    "columnNo": 1,
                    "seatNo": 2
                },
 {
                    "_id": "62b0b90e60f57e0cb220db00",
                    "rowNo": 0,
                    "columnNo": 0,
                    "seatNo": 3
                },
                {
                    "_id": "62b0b90e60f57e0cb220db01",
                    "rowNo": 0,
                    "columnNo": 1,
                    "seatNo": 4
                }
            ],
            "movieTiming": "1:30 p.m",
            "movieId": "62af1ff6cb38656a4ffe36aa",
            "movieDate": "2022-06-20T11:03:37.000Z"
        },

this is how I tried in my query

Bookings.aggregate([
    {
      $match: {
        $and: [{ movieId: ObjectId(bookingParam.movieId) },
        { movieTiming: bookingParam.movieTiming },
        { movieDate: dateQuery },
        ]
      }
    },
    {
      $group: {
        _id: {
          seatSeletion: '$seatSeletion', movieTiming: '$movieTiming',
          movieId: '$movieId', movieDate: '$movieDate', createdBy: "$createdBy", updatedBy: "$updatedBy", movies: "$movies"
        }
      }
    },
    {
      $project: {
        seatSeletion: '$_id.seatSeletion', movieTiming: '$_id.movieTiming',
        movieId: '$_id.movieId', movieDate: '$_id.movieDate', movies: "$_id.movies",
        _id: 0
      }
    }
  ])

but i got it like this

{
        "seatSeletion": [
            {
                "_id": "62b0b91560f57e0cb220db03",
                "rowNo": 0,
                "columnNo": 0,
                "seatNo": 1
            },
            {
                "_id": "62b0b91560f57e0cb220db04",
                "rowNo": 0,
                "columnNo": 1,
                "seatNo": 2
            }
        ],
        "movieTiming": "1:30 p.m",
        "movieId": "62af1ff6cb38656a4ffe36aa",
        "movieDate": "2022-06-20T11:03:37.000Z"
    },
    {
        "seatSeletion": [
            {
                "_id": "62b0b90e60f57e0cb220db00",
                "rowNo": 0,
                "columnNo": 0,
                "seatNo": 3
            },
            {
                "_id": "62b0b90e60f57e0cb220db01",
                "rowNo": 0,
                "columnNo": 1,
                "seatNo": 4
            }
        ],
        "movieTiming": "1:30 p.m",
        "movieId": "62af1ff6cb38656a4ffe36aa",
        "movieDate": "2022-06-20T11:03:37.000Z"
    }

can anyone help me to fix this issue.

CodePudding user response：

One option is using $reduce after the $group. It is important NOT to group by the seatSeletion as the value of this field is not common to these movies:

db.collection.aggregate([
  {
    $match: {
      $and: [
        {movieId: "62af1ff6cb38656a4ffe36aa"},
        {movieTiming: "1:30 p.m"},
        {movieDate: "2022-06-20T18:14:38.133 00:00"},
        
      ]
    }
  },
  {
    $group: {
      _id: {movieTiming: "$movieTiming", movieId: "$movieId", movieDate: "$movieDate"},
      seatSeletion: {$push: "$seatSeletion"}
    }
  },
  {
    $project: {
      seatSeletion: {
        $reduce: {
          input: "$seatSeletion",
          initialValue: [],
          in: {$concatArrays: ["$$value", "$$this"]}
        }
      },
      movieTiming: "$_id.movieTiming",
      movieId: "$_id.movieId",
      movieDate: "$_id.movieDate",
      _id: 0
    }
  }
])

See how it works on the playground example

Another option is using $unwind instead of $reduce, but it is generally considered slower:

db.collection.aggregate([
  {
    $match: {
      $and: [
        {movieId: "62af1ff6cb38656a4ffe36aa"},
        {movieTiming: "1:30 p.m"},
        {movieDate: "2022-06-20T18:14:38.133 00:00"},
        
      ]
    }
  },
  {$unwind: "$seatSeletion"},
  {
    $group: {
      _id: {movieTiming: "$movieTiming", movieId: "$movieId", movieDate: "$movieDate"},
      seatSeletion: {$push: "$seatSeletion"}
    }
  },
  {
    $project: {
      seatSeletion: 1,
      movieTiming: "$_id.movieTiming",
      movieId: "$_id.movieId",
      movieDate: "$_id.movieDate",
      _id: 0
    }
  }
])

See how it works on the playground example - unwind

CodePudding user response：

more output nearly you expect

{
  "_id": {
    "movieId": "62af1ff6cb38656a4ffe36aa",
    "movieDate": "2022-06-20T18:14:38.133 00:00",
    "movieTiming": "1:30 p.m"
  },
  "seatSeletion": [
    { "rowNo": 0,"columnNo": 0,"seatNo": 3
    },
    { "rowNo": 0,"columnNo": 1,"seatNo": 4
    },
    { "rowNo": 0,"columnNo": 0,"seatNo": 1
    },
    { "rowNo": 0,"columnNo": 1,"seatNo": 2
    }
  ]
}

query

db.collection.aggregate(
    {
        $match: {}
    },
    {
        $unwind: {
            path: '$seatSeletion'
        }
    },
    {
        $group: {
            _id:
            {
                movieId: '$movieId',
                movieDate: '$movieDate',
                movieTiming: '$movieTiming'
            },
            seatSeletion:
                { $push: '$seatSeletion' }
        }
    }
)