Missing Fields to extract json

Six Stars

Missing Fields to extract json

I am trying to ingest data from an API. Two of the fields are "DateStarted" and "DateCompleted". In the event that the task has been started but not completed, the "DateCompleted" key does not appear. In talend, I am using tExtractJSONFields to read this data, but whenever the "DateCompleted" key is missing, the whole row is skipped. How can I allow for missing keys within tExtractJSONFields? In the schema, I have the fields set as nullable, but it happens regardless.

The API also does report a true/false for "isCompleted" so I can check if the key would be there, if necessary, but I wanted to check if there was a simple way to fix this within tExtractJSONFields


Accepted Solutions
Employee

Re: Missing Fields to extract json

Here you go!

 

Its working even for tExtractJSONFields component.

image.png

 

The component screenshots are as below.

image.png

 

image.png

 

I hope I have answered your query. If you are still facing issue, it might be due to problem in the JSON input message.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

View solution in original post


All Replies
Employee

Re: Missing Fields to extract json

Hi,

 

    Could you please share a sample input data file with 5 records and please also specify the expected output from this JSON data?

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

 

 

Six Stars

Re: Missing Fields to extract json

[

{DateStarted: 05/01/2016,

isCompleted: False},

{DateStarted: 01/23/2017,

DateCompleted: 06/20/2018

isCompleted: True},

{DateStarted: 07/21/2016,

isCompleted: False},

{DateStarted: 03/08/2018,

DateCompleted: 01/20/2019

isCompleted: True},

{DateStarted: 01/01/2017,

DateCompleted: 03/10/2017

isCompleted: True}

]

 

And I would expect the output to be like

Date Started | Date Completed

05/01/2016 | null

01/23/2017 | 06/20/2018

07/21/2016 | null

03/08/2018 | 01/20/2019

01/01/2017 | 03/10/2017

 

 

Employee

Re: Missing Fields to extract json

Hi,

 

    When I am trying to validate the JSON, its giving me error. Could you please do a quick validation in a JSON validator like JSONLint and share the correct JSON input data?

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Six Stars

Re: Missing Fields to extract json

Here's the valid JSON

 

[{
"DateStarted": "05 / 01 / 2016",
"isCompleted": "False"
},
{
"DateStarted": "01 / 23 / 2017",
"DateCompleted": "06 / 20 / 2018",
"isCompleted": "True"
},
{
"DateStarted": "07 / 21 / 2016",
"isCompleted": "False"
},
{
"DateStarted": "03 / 08 / 2018",
"DateCompleted": "01 / 20 / 2019",
"isCompleted": "True"
},
{
"DateStarted": "01 / 01 / 2017",
"DateCompleted": "03 / 10 / 2017",
"isCompleted": "True"
}
]

Employee

Re: Missing Fields to extract json

Hi,

 

    Apologies for the delay as I was held up with some important tasks.

 

     Please refer the solution below. (Note:- Your input JSON is having space in date data. So the output is also reflecting the same way).

image.png

The steps to do create the JSON input file in repository is as below. Right click to create new JSON file and fill the details as shown below.

image.png

 

image.png

image.png

 

image.png

 

image.png

 

Once the repository is created, drag the component to workspace and it will show two options as below. Select the first one and you are ready to read the data!

image.png

 

I would appreciate if you could spare a second to mark the topic as resolved :-) Kudos will be a bonus!

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

 

 

 

 

Six Stars

Re: Missing Fields to extract json

As I explained in my initial post, my JSON is coming from an API, not a file. extractJSONFields does not seem to act in the same way as the component you are using. Are you suggesting that I should create a file of my raw JSON, save it, then read it?

Employee

Re: Missing Fields to extract json

Hi,

 

    Please try second option in that case and feed the input JSON data as input to tExtractJSONFields component.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Six Stars

Re: Missing Fields to extract json

It fails whenever there is a field missing (dateCompleted), returning a blank row.

Employee

Re: Missing Fields to extract json

Here you go!

 

Its working even for tExtractJSONFields component.

image.png

 

The component screenshots are as below.

image.png

 

image.png

 

I hope I have answered your query. If you are still facing issue, it might be due to problem in the JSON input message.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

View solution in original post

Employee

Re: Missing Fields to extract json

@lizzy 

 

Hi,

 

   Could you please mark the topic as resolved if you were able to resolve the issue?

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

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

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog