topic Re: Extra "\" in JSON file produced | urgent issue in Talend Studio

Extra "\" in JSON file produced | urgent issue

PRIYABOSCH — Sat, 16 Nov 2024 05:29:23 GMT

Dear Experts,

I am very new to talend. I need to generate a json file. the job flow is created like below. I have two issues

1. I need to generate the json with headers.

{

"$schema": " http://json-schema.org/draft-07/schema# ",

"title": "data Identifier % Quality Data",

"description": "JSON Schema for data IDs

{

---

}

I am not sure how this can be achieved with twritejsonfield and tfileoutputjson components.

2. I am getting extra "\" in the output json file created from tfileoutputjson component. Please let me know how this can be eliminated.

Thank you!

Best Regards,

Priya

Re: Extra "\" in JSON file produced | urgent issue

Anonymous — Tue, 25 Jun 2019 15:51:58 GMT

Hi @PRIYABOSCH

Can you please let us know what columns/values you require in your JSON and the sample structure of your output JSON.

Thanks and Regards,

Subhadip

Re: Extra "\" in JSON file produced | urgent issue

PRIYABOSCH — Tue, 25 Jun 2019 15:58:51 GMT

Dear Subhadip,

Thanks much for your reply.

To be precise, I have developed another job with only two columns and the schema mapping is given below.

the output data I got from datagenerator component ->twritejsonfield->tfilejsonoutput is given below.

Please check and let me know your feedback.

Thank you!

Best Regards,

Priya

Re: Extra "\" in JSON file produced | urgent issue

Anonymous — Tue, 25 Jun 2019 16:10:16 GMT

Hi @PRIYABOSCH

I believe in the data itself you have " (quotes) while mapping to JSON.

If you have quotes in data, an escape character("\") will automatically be added before the quotes in data.

Thanks and Regards,

Subhadip

Re: Extra "\" in JSON file produced | urgent issue

PRIYABOSCH — Tue, 25 Jun 2019 17:36:17 GMT

Dear Subhadip,

No, for another same kind of job I am getting data from oracle table. in that job also I get "\"

Please let me know your comments.

Re: Extra "\" in JSON file produced | urgent issue

PRIYABOSCH — Tue, 25 Jun 2019 17:43:49 GMT

Dear Subhadip,

No, for another same kind of job I am getting data from oracle table. in that job also I get "\"

if I write data using tfilejsonoutput this "\" is not coming.

tdbinput->tmap->tfilejsonoutput

for the below flow only, this extra "\" is getting generated.

tdbinput->tmap->twritejsonfield->tfilejsonoutput

Please let me know your comments.

Thank you!

Regards,

Priya

Re: Extra "\" in JSON file produced | urgent issue

Anonymous — Tue, 25 Jun 2019 18:16:49 GMT

Hi @PRIYABOSCH

In your last part of job, instead of using tFileOutputJSON, use tFileOutputDelimited. The problem is after generating JSON using tWriteJSONFields when you again use tFileOutputJSON the escape character appears.

Write the each row line by line to tFileOutputDelimited, you will get similar JSON but without escape character.

Thanks and regards,

Subhadip

Re: Extra "\" in JSON file produced | urgent issue

PRIYABOSCH — Wed, 26 Jun 2019 06:29:01 GMT

Dear Subhadip,

Yes this one I have already tried. the tFileOutputDelimited is producing the data perfectly. but when I validate this data in json validator I get error. let me know what is missing here.

Regards,

Priya

Re: Extra "\" in JSON file produced | urgent issue

lennelei — Wed, 26 Jun 2019 08:17:17 GMT

Hello,

You have extra \" because you're trying to write json data in a json file therefore, the json file component escapes the quotes.

You should add some more details to the structure you want and the data coming from the database.

But I'll presume you retrieve some sort of data you want to output in an array on the json, and that you need the header :

{  
"$schema": "http://json-schema.org/draft-07/schema#",
"title": "data Identifier % Quality Data",  
"description": "JSON Schema for data IDs",
"data": [{"id":1,...},{"id":2,...}...]
}

The "data" is what's coming from the DB (with columns id, ...).

To produce this result, you can add one technical column to your tMap in ordre to group your data. For example, add a group_json column and set it to a fixed String such as "x" (the value is not important, you might also use the three columns schema, title and description you'll need in the output).

In your tWriteJSONField, create your whole JSON structure.

Don't forget to setup the loop on the "data" child and the group on the "data".

Don't forget to setup the group by parameter of the component (using the fixed String column added to the tMap).

And then, output the single row/column result in a tFileOutputDelimited (you might remove the column separator to show that the component is not used to output delimited data).

Regards

Re: Extra "\" in JSON file produced | urgent issue

Anonymous — Wed, 26 Jun 2019 15:47:14 GMT

Hi @PRIYABOSCH ,

Your JSON generated is absolutely correct.

The problem is in the file you are validating as JSON, every line i.e. each start of the item JSONTESTFLAT is a JSON structure and your validating tool is expecting the whole file to be a JSON, that means it is expecting after line 1 there should be a , and then line 2 and then 3, 4 etc.

TWriteJSONFileds will generate as many JSONs as many groups are there in the input data, which is perfectly fine in this case.

To validate the structure you can just put one line and check if the JSON generated is valid or not using your Validating tool.

Thanks and Regards,

Subhadip

Re: Extra "\" in JSON file produced | urgent issue

Anonymous — Thu, 27 Jun 2019 09:51:34 GMT

@subhadip13 wrote:

Hi @Psiphon Download

Your JSON generated is absolutely correct.

The problem is in the file you are validating as JSON, every line i.e. each start of the item JSONTESTFLAT is a JSON structure and your validating tool is expecting the whole file to be a JSON, that means it is expecting after line 1 there should be a , and then line 2 and then 3, 4 etc.

TWriteJSONFileds will generate as many JSONs as many groups are there in the input data, which is perfectly fine in this case.

To validate the structure you can just put one line and check if the JSON generated is valid or not using your Validating tool.

Thanks and Regards,

Subhadip

The problem is after generating JSON using tWriteJSONFields when you again use tFileOutputJSON the escape character appears.

Write the each row line by line to tFileOutputDelimited, you will get similar JSON but without escape character.

Re: Extra "\" in JSON file produced | urgent issue

PRIYABOSCH — Wed, 03 Jul 2019 15:15:32 GMT

Hi Subhadip,

You are exactly correct. The JSON file is validating correctly with ROW by ROW. But I want to send this whole data to KAKFA and REST.

I have two questions:

1. Is there any other way to avoid extra "\" from the output of Twritejsonfield? or the data will be passed to Rest and KAFKA without "\" if I connect from TWRITEJSONFIELD component to the rest or kafka.

2. I believe if we parse the json data with the below code in tjavarow after twritejsonfield this "\" will be eliminated.

import org.json.simple.JSONObject;

import org.json.simple.parser.JSONParser;

JSONParser jsonParser = new JSONParser();

JSONObject obj = (JSONObject) jsonParser.parse(row3.JSONTEST);

row4.JSONTEST = obj.toString();

is my understanding is correct. but here I am getting null pointer exception.

Please let me know your feedback.

Thank you!

Regards,

Priya

Re: Extra "\" in JSON file produced | urgent issue

Anonymous — Wed, 03 Jul 2019 16:23:16 GMT

Hi @PRIYABOSCH ,

If you want to send the whole data as a single JSON object, you have to chose the group element which is unique in the input file.

Thanks and Regards,

Subhadip