Convert Mongodb schema including nested Object

Question

Suppose a mongodb collection schema like this:

{
   "_id"         : ObjectId("5a5b2657a19692e18a3792ad"),
   "Toponym"     : "Climate station Stavenhagen",
   "Lat"         : 53.33333,
   "Lon"         : "13.99999",
   "SensorMaker" : "Hitachi",
   "SensorClass" : "Thermometer",
   "Dimension"   : "SoilTemperature_0.05mSensor1",
   "Gauge"       : "degC"
}

And I would like to change the complete collection (~ 90k items) to this, to conform to minimal GeoJson:

{
  "_id"         : ObjectId("5a5b2657a19692e18a3792ad"),
  "Toponym"    : "Climate station Stavenhagen",
  "geometry"   : {
       "type"        : "Point", 
       "coordinates" : [53.33333, 13.99999]
       },
  "SensorMaker": "Hitachi",
  "SensorClass": "Thermometer",
  "Dimension"  : "SoilTemperature_0.05mSensor1",
  "Gauge"      : "degC"
}

I tried to convert it using this query, but whatever I do I will receive an error the like "Line 5: Unexpected string":

db.sensor_geo.aggregate([
{ '$group' : {
    '_id' : '$_id',
    'Toponym' : '$Toponym'
    'geometry': { 'type': 'Point', { $set : {"coordinates.$[]": [ {'$Lat', '$Lon'} ] }}},
    'SensorMaker' : '$SensorMaker',
    'SensorClass' : '$SensorClass',
    'Dimension'   : '$Dimension',
    'Gauge'       : '$Gauge'
   } 
}
]);

Should I've used $push instead of $set, even though this also lead nowhere? Do I also have to create an ObjectID for the nested Object, and that may have caused the problem?

s7vr · Accepted Answer · 2018-02-23 10:14:03Z

1

You can try below aggregation pipeline with bulk writes.

Below aggregation changes the Lat and Lon field to geometry with bulk update to write the new geometry field and remove the Lat and Lon fields.

var bulk = db.getCollection("sensor_geo").initializeUnorderedBulkOp();
var count = 0;
var batch = 1;

db.getCollection("sensor_geo").aggregate([
{"$project":{
    "geometry":{
      "type":"Point", "coordinates":["$Lat", "$Lon"]
    }
}}]).forEach(function(doc){ 
    var _id = doc._id; 
    var geometry = doc.geometry; 
    bulk.find({ "_id" : _id }).updateOne(
      {
        $set: {"geometry":geometry},
        $unset: {"Lat":"", "Lon":""}
      }
   ); 
    count++;  
    if (count == batch) { 
        bulk.execute(); 
        bulk = db.getCollection("sensor_geo").initializeUnorderedBulkOp(); 
        count = 0;
    } 
});

if (count > 0) { 
    bulk.execute(); 
}

answered Feb 23, 2018 at 10:14

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

5 Comments

Semo Over a year ago

Thank you. I get the error: "FieldPath 'Point' doesn't start with $". I tried with fixing the issue with: {"$project":{ "geometry": { $literal: { "type": "Point" }}, "coordinates":["$Lat", "$Lon"] } } but still failing, with new "disallowed field type Array in object expression (at 'coordinates')". Why disallowed field type Array? Confusing.

s7vr Over a year ago

Yw. Are you running the query in mongo shell ? What is your mongo server version ? You don't need to use literal and array should work.

Semo Over a year ago

MongoDB shell version: 2.6.10 on Ubuntu 16.04, but executing the script from Robo3T a.k.a Robomongo

s7vr Over a year ago

Oh okay that explains. That is old server which has reached end of life Oct 2016. Can you upgrade to 3.2 atleast if not the latest 3.6 ? Both array and constant support was added in 3.2

Semo Over a year ago

You helped me. That did it. after an odyssey your script finally solved the issue. I will investigate the unorderedbuld stuff whatever it does. Ur great.

Collectives™ on Stack Overflow

Convert Mongodb schema including nested Object

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related