JSON import nested object to relational tables

gaston_guy — Wed, 31 Aug 2016 13:14:46 GMT

Hello, I am trying to import data from MongoDB to a relational DB (SQL Server).

I don't have access to the MongoDB components so I am querying my collection with the mongo java driver, in a tJava component.

I get a:

List< DBObject >

which I send to a tExtractJSONFields

An object of my collection looks like this:

,
               "customer":{
                  "name":"Customer1",
                  "custid":"test_réf"
               }
            }
         ]
      }
   }
]

For a sell, I can have several employees. I have an array of employee and I want to store the affected employee in another table. So I would have 2 tables:

Sell
oid                |  shop  |  domain  |  zipCode  | ...
1564t8re13e4ter86  |  shop1 |  Divers  |  58000    | ...

Affected employee
employee_id  |  employee_name   |  oid
245975       |  employee1       | 1564t8re13e4ter86
458624       |  employee2       | 1564t8re13e4ter86

So I want to loop on the employee array, with a Jsonpath query:

"$.object.sell.employee"

The problem is that doing like this, I can't have the object_id. It seems that I can't get an attribute on a parent node if I define my Jsonpath query like this.
I also saw that I can do like in the following link:
But I don't understand when does he get the object_id at the lower levels.
How can I do?

Re: JSON import nested object to relational tables

Anonymous — Fri, 02 Sep 2016 09:05:38 GMT

Hi
Extract object_id first and store it to a context variable for used later, extract employee data another tExtractJsonFields and link it to a tMap, add the new column object_id in the output table.
Regards
Shong

topic Re: JSON import nested object to relational tables in Talend Studio

JSON import nested object to relational tables

Re: JSON import nested object to relational tables