MongoDB Groupby与数组对象字段

Question

我已经搜索了很多基于数组字段值的GroupBy，但是我在Google中没有得到正确的结果，因此我在此处发布。

我已经尽力了，它有效的50％需要纠正我的查询，任何人都可以帮助我解决这个

数据库值，就像

{"_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",
    }

预期的输出

{
            "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"
        },

一样，这就是我在查询中的

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
      }
    }
  ])

尝试

{
        "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.

Answer 1

一个选项是在$ group之后使用$降低。重要的是不要按seatseletion进行分组，因为这些字段的值对这些电影不是共同的：

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
    }
  }
])

查看其在游乐场示例

另一个选项是使用$ utind而不是$ ydive

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
    }
  }
])

在

Answer 2

您几乎可以查询更多

{
  "_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
    }
  ]
}

输出

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