topic Re: Missing Fields to extract json in Talend Studio

Missing Fields to extract json

lizzy1 — Thu, 08 Aug 2019 20:27:56 GMT

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

Re: Missing Fields to extract json

Anonymous — Thu, 08 Aug 2019 23:22:57 GMT

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

Re: Missing Fields to extract json

lizzy1 — Fri, 09 Aug 2019 18:10:13 GMT

[

{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

Re: Missing Fields to extract json

Anonymous — Fri, 09 Aug 2019 18:49:42 GMT

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

Re: Missing Fields to extract json

lizzy1 — Mon, 12 Aug 2019 12:32:15 GMT

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

Re: Missing Fields to extract json

Anonymous — Tue, 13 Aug 2019 16:08:31 GMT

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

Re: Missing Fields to extract json

lizzy1 — Tue, 13 Aug 2019 16:14:29 GMT

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?

Re: Missing Fields to extract json

Anonymous — Tue, 13 Aug 2019 16:16:41 GMT

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

Re: Missing Fields to extract json

lizzy1 — Tue, 13 Aug 2019 16:20:01 GMT

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

Re: Missing Fields to extract json

Anonymous — Tue, 13 Aug 2019 16:27:14 GMT

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

Re: Missing Fields to extract json

Anonymous — Thu, 15 Aug 2019 00:34:22 GMT

@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

Re: Missing Fields to extract json

ksuresh1 — Thu, 10 Feb 2022 00:48:01 GMT

@Lisa Carpenter , did you find a solution to this? I'm facing the same issue. Have a field from an API that is sometimes present and sometimes not.