unable to extract the fields of json using textractjsonfields component?

Five Stars

unable to extract the fields of json using textractjsonfields component?

Hi,

I am unable to extract fields from JSON data using textractjsonfields. JSON input data looks like this

[{"Id":42925362566,"UnitId":"254125220221","Direction":201.00,"DriverKeyCode":5257,"EventTypeId":149,"EventTypeDescription":"Start of Speeding","LocalTimestamp":"2018-04-17T09:01:54+03:00","Odometer":1349826,"Position":[49.34381,-18.19258],"Speed":46.00,"UnitOfDistanceCode":"km","StartOfExceptionData":{"Limit":45.0}},{"Id":42963589658,"UnitId":"261321220221","Direction":202.00,"DriverKeyCode":8507,"EventTypeId":150,"EventTypeDescription":"Speeding","LocalTimestamp":"2018-04-17T05:02:03+03:00","Odometer":1349827,"Position":[49.34342,-18.19352],"Speed":46.00,"UnitOfDistanceCode":"km","EndOfExceptionData":{"Limit":45.0,"Maximum":46.0,"Duration":14}},{"Id":42965248569,"UnitId":"261321525654","Direction":158.00,"DriverKeyCode":8565,"EventTypeId":149,"EventTypeDescription":"Start of Speeding","LocalTimestamp":"2018-04-17T05:02:47+03:00","Odometer":542971,"Position":[47.48203,-18.86108],"Speed":46.00,"UnitOfDistanceCode":"km","StartOfExceptionData":{"Limit":45.0}},{"Id":42965588747,"UnitId":"261321220440","Direction":158.00,"DriverKeyCode":4575,"EventTypeId":150,"EventTypeDescription":"Speeding","LocalTimestamp":"2018-04-17T05:03:05+03:00","Odometer":542971,"Position":[47.48280,-18.86287],"Speed":47.00,"UnitOfDistanceCode":"km","EndOfExceptionData":{"Limit":45.0,"Maximum":47.0,"Duration":24}},

 

Input fields come as

statusCode,

body,

string,

so I have given the output extract fields columns as

Id,

UnitId,
Direction,
DriverKeyCode,
EventTypeId,
EventTypeDescription,
LocalTimestamp,
Odometer,
Position,
Speed,
UnitOfDistanceCode.

 

Loop xpath query: "/*"
jsonfields.png


Accepted Solutions
Thirteen Stars

Re: unable to extract the fields of json using textractjsonfields component?

try with XPath - "/" (no *)

and it would be something like:

.-----------+------------+---------+-----------+-----------+-----.
|                           tLogRow_1                            |
|=----------+------------+---------+-----------+-----------+----=|
|Id         |UnitId      |Direction|PositionLat|PositionLon|Speed|
|=----------+------------+---------+-----------+-----------+----=|
|42925362566|254125220221|201      |49.34381   |-18.19258  |46   |
|42963589658|261321220221|202      |49.34342   |-18.19352  |46   |
|42965248569|261321525654|158      |47.48203   |-18.86108  |46   |
'-----------+------------+---------+-----------+-----------+-----'  

 

-----------

All Replies
Thirteen Stars

Re: unable to extract the fields of json using textractjsonfields component?

try with XPath - "/" (no *)

and it would be something like:

.-----------+------------+---------+-----------+-----------+-----.
|                           tLogRow_1                            |
|=----------+------------+---------+-----------+-----------+----=|
|Id         |UnitId      |Direction|PositionLat|PositionLon|Speed|
|=----------+------------+---------+-----------+-----------+----=|
|42925362566|254125220221|201      |49.34381   |-18.19258  |46   |
|42963589658|261321220221|202      |49.34342   |-18.19352  |46   |
|42965248569|261321525654|158      |47.48203   |-18.86108  |46   |
'-----------+------------+---------+-----------+-----------+-----'  

 

-----------

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.