Converting data from Postgres DB tables into Relational Entities

[SPatel1683222230]

First time using Talend, so please excuse me if i am not using the correct terminology

I have data in a Postgres table which is of JSON and JSONB data type (arrays) as shown below

I need to take data from each of the above columns , flatten them out and push them to the Relational tables in Postgres. The endpoint_info and the exception_info (columns shown above have pretty deep nested JSON objects and are ARRAYS)

I have tried using the tExtractJSONFields component but i am not able to convert all the columns given it only works on one column at a time (JSON field)

Can someone please suggest the components or the flow of components i should look at to achieve this solution of flattening the data from Postgres JSON and JSONB data types to relational table structure

[Anonymous]

Hello @Sam Patel​ ,

To achieve your goal, I think you can design the job like:

tPostgresqlInput + tExtractJSONFields + tDBOutput

you can setup multiple group of above components regarding to the postgresql db columns , each tPostgresqlInput will read only one column data, and tExtractJSONFields to extract the fields from the column data, and tDBOutput to write the fields data to the target relational db table.

NOTE: it's suggested to setup the ReadBy to "JsonPath" and setup related Json query for the related columns for tExtractJSONFields component as the below

here is a json path finder tool : https://jsonpathfinder.com/