Can’t convert from BSON type array to Date

[Malika_Taouai]

I have 2 collections (orders containing products Id and the date of the order (ISODate)) and customers that include the order as a field reference to the order collection.
At first, I made a lookup operator to link between the two collections, then I wanted to group the result by month or maybe year, and an error msg:“PlanExecutor error during aggregation:: caused by:: can’t convert from BSON type array to Date” So, I tried with a date as a Date type but I have the same error

[Jack_Woehr]

Post simple example code

[Malika_Taouai]

db.customers.aggregate({$lookup:{
  from: "orders",
  localField: "orders",
  foreignField: "_id",
  as: "orders",
}})
{ _id: ObjectId("63728124290f7a2159df21e5"),
  name: 'Jay.K',
  age: 32,
  gender: 'male',
  address: ObjectId("6372838c290f7a2159df21f0"),
  contact: ObjectId("637284a8290f7a2159df21fa"),
  paymentMethod: 
   [ ObjectId("63761403f8fe05d9db646186"),
     ObjectId("63762a01beab2f5d33b4980b") ],
  orders: 
   [ { _id: 1, date: 2021-07-09T00:00:00.000Z, pId: [ 12, 46 ] },
     { _id: 2, date: 2021-10-05T00:00:00.000Z, pID: [ 152, 87, 100 ] },
     { _id: 3, date: 2022-01-10T00:00:00.000Z, pId: [ 212, 646 ] } ] }

[Malika_Taouai]

$project : {
  _id: 0,
  Day: {
    $dayOfMonth: "$orders.date",
  },
  Month: {
    $month: "$orders.date",
  },
  Year: {
    $year: "$orders.date",
  }
}

[Malika_Taouai]

Same result: PlanExecutor error during aggregation:: caused by:: can’t convert from BSON type array to Date.

I tried to work on the date as a collection and use references to the link between orders and date, and then link with customers to group my data by date, but it didn’t work too.

[Malika_Taouai]

@ Pavel_Duchovny

[Leandro_Domingues]

@Malika_Taouai

_id: ObjectId("63728124290f7a2159df21e5"),
  name: 'Jay.K',
  age: 32,
  gender: 'male',
  address: ObjectId("6372838c290f7a2159df21f0"),
  contact: ObjectId("637284a8290f7a2159df21fa"),
  paymentMethod: 
   [ ObjectId("63761403f8fe05d9db646186"),
     ObjectId("63762a01beab2f5d33b4980b") ],
  orders: 
   [ { _id: 1, date: 2021-07-09T00:00:00.000Z, pId: [ 12, 46 ] },
     { _id: 2, date: 2021-10-05T00:00:00.000Z, pID: [ 152, 87, 100 ] },
     { _id: 3, date: 2022-01-10T00:00:00.000Z, pId: [ 212, 646 ] } ] }

Assuming you have the order_date field in the orders array in the customers collection, you don’t need the $lookup. Correct me if I’m wrong…

db.customers.aggregate(
  [
    {
      $match: {
        name: "Jay.K",
      },
    },
    {
      $unwind: "$orders",
    },
    {
      $group: {
        _id: {
          name: "$name",
          orderDate: {
            $dateToString: {
              format: "%Y",
              date: "$orders.date",
            },
          },
        },
        total: {
          $sum: 1,
        },
      },
    },
  ]
)

[Malika_Taouai]

@Leandro_Domingues

db.customers.aggregate(
  [
    {
      $match: {
        name: "Jay.K",
      },
    },
    {
      $unwind: "$orders",
    },
    {
      $group: {
        _id: {
          name: "$name",
          orderDate: {
            $dateToString: {
              format: "%Y",
              date: "$orders.date",
            },
          },
        },
        total: {
          $sum: 1,
        },
      },
    },
  ]
)

It works!
Thank you ^^

[Yilmaz_Durmaz (Yılmaz Durmaz)]

Hi, @Malika_Taouai, in your posts, can you please try to create a stripped version of your actual data with both collections having all required fields, but with only 1-2 unrelated fields. this will help us as well as yourself to see the problem.

it is nice to hear that the assumption @Leandro_Domingues has made did work.

Here is a remark on your problem:

$lookup stage returns an array of objects even when you match a single value with a single document. so a single orders:[1] will be orders: [{ _id: 1,date:"pudatehere"}].

this is why your “$orders.date” does not work as you would expect because it expected an object, instead got an array of objects. depending on the situation you will need an $unwind operation to unpack this array to individual objects, such as grouping on a field of the objects in the array.