CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
Manolait
Manolait

Reputation: 352

Forming an array with aggregation in MongoDB

I have a document in MongoDB 3.4 with the following structure:

{
    "_id" : ObjectId("5e3419e468d01013eadb83dc"),
    "id_station" : "62",
    "fiware_service" : null,
    "fiware_servicepath" : null,
    "id_fiware_name" : "CE_del_medio",
    "attrName" : "15",
    "attrType" : "float",
    "attrValue" : 0.33,
    "id_sensor_station_absolute" : "15_62",
    "recvTimeTs" : 1580387045,
    "recvTime" : "2020-01-30T12:24:05.00Z",
    "id_fiware" : "15",
    "sensor_type" : [ 
        {
            "name" : "id",
            "type" : "String",
            "value" : "15"
        }, 
        {
            "name" : "img",
            "type" : "String",
            "value" : "assets/img/contrast.png"
        }, 
        {
            "name" : "manufacturer",
            "type" : "String",
            "value" : "Hortisis"
        }, 
        {
            "name" : "medida",
            "type" : "String",
            "value" : "mS/cm"
        }, 
        {
            "name" : "name_comun",
            "type" : "String",
            "value" : "CE del medio"
        }, 
        {
            "name" : "place",
            "type" : "String",
            "value" : "interior"
        }, 
        {
            "name" : "timestamp",
            "type" : "DateTime",
            "value" : "2020-01-30T12:24:05.00Z"
        }, 
        {
            "name" : "type",
            "type" : "String",
            "value" : "fertigation"
        }
    ]
}

I need to convert the sensor_type field to an array with only one object, as follows:

{
   "_id":"15_62",
   "medidas":[
      {
         "_id":"5e3419e468d01013eadb83dc",
         "marca":"Hortisis",
         "modelo":"Estacion",
         "fabricante":"Hortisis",
         "id_station":"15",
         "sensor_type":[
            {
               "name":"15",
               "type":"fertigation",
               "place":"interior",
               "img":"assets/img/contrast.png",
               "name_comun":"Temp. Suelo",
               "medida":"mS/cm"
            }
         ],
         "attrName":"15",
         "attrValue":0.33,
         "recvTimeTs":1580387045,
         "recvTime":"2020-01-30T12:24:05.00Z",
         "id_sensor_station_absolute":"15_62"
      }
   ]
}

As you can really see it is formatting the sensor_type field = name : value.

I'm working with NODEJS and mongoose.

This is my query: (first I search, sort, only show the first value and then with the project I give format, the problem is that I don't know how to tell the project to put that format if I put "sensor_type": "$latest.attributes.name") it only shows the names and I don't know how to put it in the mentioned format.

Datagreenhouse.aggregate([
        { "$match": { "id_sensor_station_absolute": { "$in": array3 } } }, // "id_station": { "$in": id_station },
        { "$sort": { "recvTime": -1 } },
        {
            "$group": {
                "_id": "$id_sensor_station_absolute",
                "latest": { "$first": "$$ROOT" },
            }
        },
        {
            "$project": {
                "_id": 1,
                "id_station": "$latest.id_station",
                //"id_sensor_station_absolute": "$id_sensor_station_absolute",
                "attrName": "$latest.attrName",
                "attrValue": "$latest.attrValue",
                "recvTimeTs": "$latest.recvTimeTs",
                "recvTime": "$latest.recvTime",
                "id_sensor_station_absolute": "$latest.id_sensor_station_absolute",
                "sensor_type": "$latest.attributes",


"name": { $arrayElemAt: ["$latest.attributes", 0] },
                "type": { $arrayElemAt: ["$latest.attributes", 1] },
                "place": { $arrayElemAt: ["$latest.attributes", 2] },
                "img": { $arrayElemAt: ["$latest.attributes", 1] },
                "name_comun": { $arrayElemAt: ["$latest.attributes", 4] },
                "medida": { $arrayElemAt: ["$latest.attributes", 3] },
                "interfaz": { $arrayElemAt: ["$latest.attributes", 6] },

            }
        }
    ], (err, DatagreenhouseRecuperado) => {
        if (err) return res.status(500).send({ message: 'Error al realizar la peticion' + err })
        if (!DatagreenhouseRecuperado) return res.status(404).send({ message: 'Error el usuario no existe' })
        res.status(200).send({ DatagreenhouseRecuperado })
    })

Thank you for your help. Best regards.

Upvotes: 0

Views: 45

Answers (1)

Valijon
Valijon

Reputation: 13093

Since version 3.4.4, MongoDB introduced a magnific operator: $arrayToObject

This operator allows us transmute array key:value pair into object.

Syntax

RAW DATA                           $map                        $arrayToObject
sensor_type : [                    sensor_type : [             sensor_type : {
  {                             \    {                      \   
    "name" : "manufacturer", ---->     k: "manufacturer", --->   
    "type" : "String",          /      v: "Hortisis"        /    "manufacturer" : "Hortisis"
    "value" : "Hortisis"                    
  }                                  }
]                                  ]                            }

db.datagreenhouses.aggregate([
  {
    "$match": {} // setup your match criteria
  },
  {
    "$sort": {
      "recvTime": -1
    }
  },
  {
    $group: {
      _id: "$id_sensor_station_absolute",
      medidas: {
        $push: {
          _id: "$_id",
          "marca": "Hortisis", // don't know where you get this value
          "modelo": "Estacion", // don't know where you get this value
          "id_station": "$id_station",
          "attrName": "$attrName",
          "attrValue": "$attrValue",
          "recvTimeTs": "$recvTimeTs",
          "recvTime": "$recvTime",
          "id_sensor_station_absolute": "$id_sensor_station_absolute",
          "sensor_type": {
            $arrayToObject: {
              $map: {
                input: "$sensor_type",
                in: {
                  k: "$$this.name",
                  v: "$$this.value"
                }
              }
            }
          }
        }
      }
    }
  }
])

MongoPlayground

[
  {
    "_id": "15_62",
    "medidas": [
      {
        "_id": ObjectId("5e3419e468d01013eadb83dc"),
        "attrName": "15",
        "attrValue": 0.33,
        "id_sensor_station_absolute": "15_62",
        "id_station": "62",
        "marca": "Hortisis",
        "modelo": "Estacion",
        "recvTime": "2020-01-30T12:24:05.00Z",
        "recvTimeTs": 1.580387045e+09,
        "sensor_type": {
          "id": "15",
          "img": "assets/img/contrast.png",
          "manufacturer": "Hortisis",
          "medida": "mS/cm",
          "name_comun": "CE del medio",
          "place": "interior",
          "timestamp": "2020-01-30T12:24:05.00Z",
          "type": "fertigation"
        }
      }
    ]
  }
]

All you need to do is transform data to the desired result with an easy to handle object ($unwind medidas field, transform and then $group again)

Note: If your MongoDB is earlier 3.4.4 version, follow update procedure:

  1. Install MongoDB 3.4.4 or newer
  2. Make mongodump with new version MongoBD
  3. Stop old MongoBD
  4. Remove /data directory (make backup)
  5. Start new MongoDB and run mongorestore

Upvotes: 1

Related Questions