Missing Fields to extract json

[lizzy1]

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

[Anonymous]

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

[lizzy1]

[

{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

[Anonymous]

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

[lizzy1]

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"
}
]

[Anonymous]

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).

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.

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!

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

[lizzy1]

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?

[Anonymous]

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

[lizzy1]

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

[Anonymous · Accepted solution]

Here you go!

Its working even for tExtractJSONFields component.

The component screenshots are as below.

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