Mongodb aggregate, convert subdocument array to a single object

Question

My document

    [
  {
    "_id": "5f3d0f13fd6fd6667f8f56d6",
    "items": [
      {
        "_id": "5f3d0f16fd6fd6667f8f5813",
        "d": "2019-09-20T00:00:00.000Z",
        "h": 197
      },
      {
        "_id": "5f3d0f16fd6fd6667f8f5814",
        "d": "2019-09-23T00:00:00.000Z",
        "h": 196,
      },
      {
        "_id": "5f3d0f16fd6fd6667f8f5815",
        "d": "2019-09-24T00:00:00.000Z",
        "h": 194,
      }
    ]
  }
]

Expected output

{
  "_id": "5f3d0f13fd6fd6667f8f56d6",
  t: [1386493512, 1386493572, 1386493632, 1386493692], //unix timestamps
  h: [197, 196, 194]
}

How do I do this conversion with MongoDB aggregate framework?

Probably something with $unwind $push

Answer 1

You don't need $unwind but can use $project.

Note you can't get a unix timestamp out-of-the-box, but you need to manage the operation by yourself using the mongo date expression operators.

EDIT: added $toLong step

In this example, you will get a Date object as output that will be easily transformed to a timestamp in the client who runs the query

db.collection.aggregate([
  {
    $project: {
      t: {
        $map: {
          input: "$items.d",
          as: "dateval",
          in: {
            $toLong: {
              $dateFromString: {
                dateString: "$$dateval"
              }
            }
          }
        }
      },
      h: {
        $map: {
          input: "$items.h",
          as: "intval",
          in: {
            $add: [
              "$$intval",
              0
            ]
          }
        }
      }
    }
  }
])

Output:

[
  {
    "_id": "5f3d0f13fd6fd6667f8f56d6",
    "h": [
      197,
      196,
      194
    ],
    "t": [
      NumberLong(1568937600000),
      NumberLong(1569196800000),
      NumberLong(1569283200000)
    ]
  }
]

https://mongoplayground.net/p/D1mDDNj5fJ1

Answer 2

You can try,

• $unwind deconstruct items array
• $group by _id and push h in to h
• convert string date to ISODate using $dateFromString and convert ISODate to timestamp using $toLong

db.collection.aggregate([
  { $unwind: "$items" },
  {
    $group: {
      _id: "$_id",
      h: { $push: "$items.h" },
      t: {
        $push: {
          $toLong: { $dateFromString: { dateString: "$items.d" } }
        }
      }
    }
  }
])

Playground