JSON import nested object to relational tables

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

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog