Forming an array with aggregation in MongoDB

Question

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.

Valijon · Accepted Answer · 2020-01-31 18:31:05Z

1

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:

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

edited Jan 31, 2020 at 18:31

answered Jan 31, 2020 at 18:26

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Manolait Over a year ago

First of all thanks for the help. a doubt: if this collection has more records of the same sensor but only changes the date and value how can I have the last value inserted? before to "latest": {"$ first": "$$ ROOT"}, but it takes a lot and in this case it would not work and limit is not valid since the input parameters are an array and only shows one element and would have to Be the last of each.

Valijon Over a year ago

@Manolait change $push to $first / $last

Manolait Over a year ago

if I change $push for $first it gives me "sensor_type": null

Valijon Over a year ago

@Manolait can you share more samples to try to reproduce your case, because $first should work perfecto. Make sure field name written correctly

Manolait Over a year ago

I add the tests with the error: mongoplayground.net/p/gIrdnsSpWpy

|

Collectives™ on Stack Overflow

Forming an array with aggregation in MongoDB

1 Answer 1

Syntax

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Syntax

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related