I have three tables in one database. These tables have a foreign key between they. The Table1 is master from Table2 and Table 2 is master from Table3.
I want get data values and transform in MongoDB document like this:
{
"_id" : ObjectId("cf3977abf592d19962ff7982"),
"T1Column1" : "Lorem Ipsum",
"T1Column2" : ISODate("2015-11-27T16:04:24.000Z"),
"Table2" : [
{
"T2Column1" : NumberLong(1),
"T2Column2" : "Lorem Ipsum",
"Table3" : [
{
"T3Column1" : "Lorem Ipsum",
"T3Column2" : "Lorem Ipsum"
},
{
"T3Column1" : "Lorem Ipsum",
"T3Column2" : "Lorem Ipsum"
}
]
},
{
"T2Column1" : NumberLong(2),
"T2Column2" : "Lorem Ipsum",
"Table3" : [
{
"T3Column1" : "Lorem Ipsum1",
"T3Column2" : "Lorem Ipsum"
},
{
"T3Column1" : "Lorem Ipsum2",
"T3Column2" : "Lorem Ipsum"
}
]
}
]
}
I already try to use "Mongo document path" in MongoDB Output Step, but don't is possible use "upsert" for subdocuments, as we can see in MongoDB Ouput Document:
How I can to do this using Pentaho Data Integration (PDI)?
try to add modifier options "$addToSet"
To insert into a Mongodb using PDI step MONGODB OUTPUT, the trick is in the 'Mongo Document Path' column. Put a bracket
[]
in the end of the field path where you want the array (See thedata.labels[].id
in below screenshot), and use$set
as modifier operation.You can also use a pointer if you want the data into a specific array. Use
[n]
at the end of field path (see thetags[0]
,tags[1]
,tags[2]
in below screenshot). Note that the first array begins as 0.