I've a mongodb collection customer_details. Whenever new record is created in a collection, create_timestamp and modified_timestamp got the same values. Whenever new furniture or equipemnt is added for a customer then modified_timestamp updates.

For example "create_timestamp": 1641560954619 is January 7, 2022 and "modified_timestamp": 1641826530378 is January 10, 2022.

I do not have create_timestamp and modified_timestamp for each asset under furniture and equipment.

Here's the mongoplayground link

I'm getting an error "query failed: (Location16006) PlanExecutor error during aggregation :: caused by :: can't convert from BSON type double to Date"

I want to count total number of furniture and equipment (separately) added on each day (based on available timestamp values).

For example

[
  {
    "_id": "2022-01-07",
    "furniture": 10,
    "equipment": 2
  },
  {
    "_id": "2022-01-08",
    "furniture": 5,
    "equipment": 0
  }
]



   [
  {
    "_id": {
      "$oid": "61d83b7a7235cd1fc20317fd"
    },
    "customer_id": "8440731e-f939-4e46-a1ed-8b5892c2a708",
    "customer_assets": {
      "furniture": [
        {
          "id": "97ab067b-b300-4d57-86c3-92c0577b1e85",
          "name": "table"
        }
      ],
      "equipments": [
        {
          "id": "94b16e00-dbf3-4e65-ae77-b4ad33ada9fe",
          "name": "washing machine"
        },
        {
          "id": "c389c7f3-06f0-4cf6-94b5-50b717d3c8bf",
          "name": "microwave"
        }
      ]
    },
    "create_timestamp": 1641560954619,
    "modified_timestamp": 1641826530378
    
  },
  {
    "_id": {
      "$oid": "61d86c387235cd1fc20317fe"
    },
    "customer_id": "47b21796-c8c3-4d0c-9389-5041b167e69c",
    "customer_assets": {
      "furniture": [
        {
          "id": "6c4eb059-76a5-491d-a2d0-3711ec0bd605",
          "name": "desk"
        },
        {
          "id": "50439272-2196-459e-88c7-c941830befa4",
          "name": "sofa"
        },
        {
          "id": "a171e54f-897d-4470-9324-cb5fa41c0f8b",
          "name": "dining table"
        }
      ]
    },
    "create_timestamp": 1641573432778,
    "modified_timestamp": 1649668575689
  },
  {
    "_id": {
      "$oid": "61d86d087235cd1fc20317ff"
    },
    "customer_id": "f1175fbc-ff2c-467f-ade8-e2efe03a5e8f",
    "customer_assets": {
      "furniture": [
        {
          "id": "757a6503-9c77-4eb8-89d4-240c8d51e12f",
          "name": "chair"
        }
      ]
    },
    "create_timestamp": 1641573640237,
    "modified_timestamp": 1641573640237
  },
  {
    "_id": {
      "$oid": "61d86dfa7235cd1fc2031800"
    },
    "customer_id": "7943f0bc-63b1-4d75-9c1f-fc4969ce1458",
    "customer_assets": {
      "furniture": [
        {
          "id": "6a55ff3e-d163-4152-b2a7-9369751b74ae",
          "name": "couch"
        }
      ]
    },
    "create_timestamp": 1641573882891,
    "modified_timestamp": 1641573882891
  },
  {
    "_id": {
      "$oid": "61d86f7039e3e24864ad03fa"
    },
    "customer_id": "76fe3abf-9e7b-44fb-b02a-1bdde6e01a09",
    "customer_assets": {
      "furniture": [
        {
          "id": "9e3a4b27-4958-4240-a536-d263ad8a1ad1",
          "name": "mirror"
        }
      ]
    },
    "create_timestamp": 1641574256463,
    "modified_timestamp": 1641574256463
  },
  {
    "_id": {
      "$oid": "61d870b739e3e24864ad03fb"
    },
    "customer_id": "8f6e31f2-6a2f-474d-9481-8703b14c90ae",
    "customer_assets": {
      "furniture": []
    },
    "create_timestamp": 1641574583470,
    "modified_timestamp": 1641574583470
  },
  {
    "_id": {
      "$oid": "61d8728439e3e24864ad03fc"
    },
    "customer_id": "76b34683-b834-4fcc-bb73-39fac2b7333b",
    "customer_assets": {
      "furniture": [
        {
          "id": "cfec41fb-5e54-4fec-a27c-9c969f220394",
          "name": "bed"
        }
      ]
    },
    "create_timestamp": 1641575044544,
    "modified_timestamp": 1641575044544
  },
  {
    "_id": {
      "$oid": "61d873877235cd1fc2031801"
    },
    "customer_id": "16c1d037-b3b8-4c88-bbdf-f4b00cae30ad",
    "customer_assets": {
      "furniture": [
        {
          "id": "aaa5ed97-fe8d-496a-941f-a2b0743926ad",
          "name": "porch swing"
        }
      ]
    },
    "create_timestamp": 1641575303104,
    "modified_timestamp": 1641575303104
  },
  {
    "_id": {
      "$oid": "61d874197235cd1fc2031802"
    },
    "customer_id": "e1a5c5e6-f0e8-4d6b-a444-27ad1e972ab3",
    "customer_assets": {
      "furniture": [
        {
          "id": "b2129de0-e179-4d4e-a3a9-f7b6f6812bc7",
          "name": "table"
        }
      ]
    },
    "create_timestamp": 1641575449114,
    "modified_timestamp": 1641575449114
  },
  {
    "_id": {
      "$oid": "61d8765b7235cd1fc2031803"
    },
    "customer_id": "65c86f1e-e3c3-426d-83f0-af1d280342d1",
    "customer_assets": {
      "furniture": [
        {
          "id": "171acf27-db23-482a-9a18-1cba9fe3ea10",
          "name": "chair"
        }
      ]
    },
    "create_timestamp": 1641576027176,
    "modified_timestamp": 1641576027176
  },
  {
    "_id": {
      "$oid": "61d877517235cd1fc2031804"
    },
    "customer_id": "bb1b87c5-1847-466e-b0f9-f46f34806a11",
    "customer_assets": {
      "furniture": [
        {
          "id": "51949000-e86f-49cb-b47b-97299d757f97",
          "name": "dining table"
        }
      ]
    },
    "create_timestamp": 1641576273244,
    "modified_timestamp": 1641576273244
  },
  {
    "_id": {
      "$oid": "61d877ec7235cd1fc2031805"
    },
    "customer_id": "ae471e4b-22d4-487d-84a5-e8a4f5a740dc",
    "customer_assets": {
      "furniture": [
        {
          "id": "e53ef2b4-ba13-44db-870b-0054431044e6",
          "name": "kitchen cart"
        }
      ]
    },
    "create_timestamp": 1641576428626,
    "modified_timestamp": 1641576428626
  },
  {
    "_id": {
      "$oid": "61d879757235cd1fc2031806"
    },
    "customer_id": "7c88ef4b-52f6-4824-8ea1-11e7b4878a5c",
    "customer_assets": {
      "furniture": [
        {
          "id": "ac011617-f5df-4995-b2dd-494c1b2d6f20",
          "name": "bed"
        }
      ]
    },
    "create_timestamp": 1641576821100,
    "modified_timestamp": 1641576821100
  },
  {
    "_id": {
      "$oid": "61d87c0039e3e24864ad03fd"
    },
    "customer_id": "c7d3501d-8fb7-4c69-91b7-10a0f31c2877",
    "customer_assets": {
      "furniture": [
        {
          "id": "83b403cd-5ed1-4ea0-9c36-b2041f96e223",
          "name": "table"
        }
      ]
    },
    "create_timestamp": 1641577472883,
    "modified_timestamp": 1641577472883
  },
  {
    "_id": {
      "$oid": "61d887fa7235cd1fc2031807"
    },
    "customer_id": "10b7bb02-079b-4a52-be17-a1ba4d3dc807",
    "customer_assets": {
      "furniture": [
        {
          "id": "45cb046c-da15-4287-804a-2df3eea0c2d4",
          "name": "desk"
        },
        {
          "id": "2a45a654-2e27-4752-89d7-8aaa2f994fdc",
          "name": "chair"
        }
      ]
    },
    "create_timestamp": 1641580538467,
    "modified_timestamp": 1641580739223
  },
  {
    "_id": {
      "$oid": "61d8a7c27a588838756e8215"
    },
    "customer_id": "157a68ef-e71c-4f32-8137-5d5a82d8ef93",
    "customer_assets": {
      "furniture": [
        {
          "id": "896a4c4e-8676-46c5-9097-acdf7452252b",
          "name": "desk",
          "expiry_month": "03"
        },
        {
          "id": "2d6d3053-d6cd-4d4b-a70f-62477b1d4013",
          "name": "chair"
        }
      ]
    },
    "create_timestamp": 1641588674344,
    "modified_timestamp": 1641610299388
  }
]

CodePudding user response：

You first need to convert the timestamp to date with $toDate:

db.collection.aggregate([
  {
    $group: {
      _id: {
        $dateToString: {
          format: "%Y-%m-%d",
          date: {
            "$toDate": "$create_timestamp"
          }
        }
      },
      count: {
        $sum: 1
      },
      furniture: {
        $sum: {
          "$add": [
            {
              "$size": {
                $ifNull: [
                  "$customer_assets.furniture",
                  []
                ]
              }
            },
            {
              "$size": {
                $ifNull: [
                  "$customer_payment_tokens.furniture",
                  []
                ]
              }
            }
          ]
        }
      }
    }
  },
  {
    $sort: {
      count: 1
    }
  }
])