One Star

JSON import nested object to relational tables

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?
1 REPLY
Community Manager

Re: JSON import nested object to relational tables

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
----------------------------------------------------------
Talend | Data Agility for Modern Business