How to do complex document from three tables in json to mongodb using Pentaho

Question

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)?

Answer 1

try to add modifier options "$addToSet"

Answer 2

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 the data.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 the tags[0], tags[1], tags[2] in below screenshot). Note that the first array begins as 0.