merge array in to objects into MongoDB

Question

Explanation: I have to arrays TV and values I want to merge as one array in side the object. TV_values will be the output as one array.

{
   "TV":[
      {
         "T":"2018-05-05T09:00:00.000Z",
         "V":1.3
      },
      {
         "T":"2018-05-05T09:00:00.000Z",
         "V":2.21
      }
   ],
   "values":[
      100,
      200
   ]
}

Expected output document

{
   "TV_values":[
      {
         "T":"2018-05-05T09:00:00.000Z",
         "date":2018-05-05,
         "hour": 09, 
         "V":1.3,
         "values":100
      },
      {
         "T":"2018-05-05T09:00:00.000Z",
         "date":2018-05-05,
         "hour": 09, 
         "V":2.21,
         "values":200
      }
   ]
}

Takis · Accepted Answer · 2021-08-25 16:18:01Z

Solution using map on indexes.
$map on range of count (map on indexes), takes elements of both arrays and merge them. (requires same size of TV and VALUES)

It ignores the V field, merge by indexes TV index 0 with Value index 0,TV index 1 with Value index 1 , i guess this is what you wanted.

Test code here

db.collection.aggregate([
  {
    "$project": {
      "TV_VALUES": {
        "$map": {
          "input": {
            "$range": [0,{"$size": "$TV"}]
          },
          "as": "i",
          "in": {
            "$let": {
              "vars": {
                "tv": {"$arrayElemAt": ["$TV","$$i"]
                }
              },
              "in": {
                "$mergeObjects": [
                  "$$tv",
                  {
                    "date": {
                      "$dateToString": {
                        "date": {"$toDate": "$$tv.T"},
                        "format": "%Y-%m-%d"
                      }
                    },
                    "hour": {"$hour": {"$toDate": "$$tv.T"}}
                  },
                  {
                    "values": {
                      "$arrayElemAt": ["$values","$$i"]
                    }
                  }
                ]
              }
            }
          }
        }
      }
    }
  }
])

J.F. · Accepted Answer · 2021-08-25 11:43:41Z

You can use this aggregation query:

First $unwind to deconstruct the array.
Then set each value according to the index - 1 from the V position. (V 1 is index 0 (position 1 in the array) and V 2 is index 1)
And regroup again to get the final object.

db.collection.aggregate([
  {
    "$unwind": "$TV"
  },
  {
    "$set": {
      "TV.values": {
        "$arrayElemAt": [
          "$values",
          {
            "$subtract": [
              "$TV.V",
              1
            ]
          }
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "TV": {
        "$push": "$TV"
      }
    }
  }
])

Example here

turivishal · Accepted Answer · 2021-08-25 14:09:22Z

$map to iterate loop of TV array
$indexOfArray to get index of current element from TV array
$arrayElemAt to get specific index element from values array
$toDate to convert string date to date type
$dateToSting to get formated date
$hour to get hour from T date
$mergeObjects merge current object properties with new values` property

db.collection.aggregate([
  {
    $project: {
      "TV_values": {
        $map: {
          input: "$TV",
          in: {
            $mergeObjects: [
              "$$this",
              {
                hour: { $hour: { $toDate: "$$this.T" } },
                date: {
                  $dateToString: {
                    date: { $toDate: "$$this.T" },
                    format: "%Y-%m-%d"
                  }
                },
                values: {
                  $arrayElemAt: [
                    "$values",
                    { $indexOfArray: ["$TV.V", "$$this.V"] }
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

Thanks for the answer : I am getting error message : '$arrayElemAt's second argument must be representable as a 32-bit integer: 1.2' on server . . I am usign mongo db 4.2 version.
just need the values inside the TV_values. what do you mean by the first position?
you can take any field inside the TV for reference. no there is no unique filed
yes, it's fine but I have the V as float, not an integer, I posted the question with integer values [mongoplayground.net/p/vozZgALpUPQ] (playground) is also working with float.
no, it's not perfectly working, but if I do the round V then it is working, but I want the values as float

Collectives™ on Stack Overflow

merge array in to objects into MongoDB

3 Answers 3

Comments

Comments

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

7 Comments

Linked

Related