Extra "\" in JSON file produced | urgent issue

[PRIYABOSCH]

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

[Anonymous]

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

[PRIYABOSCH]

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.

{"data":[{"ID":"{\"ID\":\"XKBD8d\",\"NAME\":\"6ZMv3P\"}","NAME":null},{"ID":"{\"ID\":\"yawWXR\",\"NAME\":\"e9uXQw\"}","NAME":null},{"ID":"{\"ID\":\"dhWZ5x\",\"NAME\":\"vN1v5N\"}","NAME":null},{"ID":"{\"ID\":\"jhS0di\",\"NAME\":\"8xfsPi\"}","NAME":null},{"ID":"{\"ID\":\"iKir8s\",\"NAME\":\"5Cj8jO\"}","NAME":null},{"ID":"{\"ID\":\"O523uK\",\"NAME\":\"1wjDOC\"}","NAME":null},{"ID":"{\"ID\":\"SV1hcR\",\"NAME\":\"tuVPOC\"}","NAME":null},{"ID":"{\"ID\":\"hxmBRN\",\"NAME\":\"6zrF2L\"}","NAME":null},{"ID":"{\"ID\":\"DAX9b6\",\"NAME\":\"Y8Sk71\"}","NAME":null},{"ID":"{\"ID\":\"6vwtux\",\"NAME\":\"371OAr\"}","NAME":null},{"ID":"{\"ID\":\"i4qjdU\",\"NAME\":\"V1QzjQ\"}","NAME":null},{"ID":"{\"ID\":\"nG5E7B\",\"NAME\":\"cS8Gxs\"}","NAME":null},{"ID":"{\"ID\":\"rbkgRC\",\"NAME\":\"Fi3AvH\"}","NAME":null},{"ID":"{\"ID\":\"0XshTo\",\"NAME\":\"O2t9ZZ\"}","NAME":null},{"ID":"{\"ID\":\"rWh83b\",\"NAME\":\"ZkoNHF\"}","NAME":null},{"ID":"{\"ID\":\"U2W0nW\",\"NAME\":\"W10goI\"}","NAME":null},{"ID":"{\"ID\":\"xSYPKS\",\"NAME\":\"uCgTWR\"}","NAME":null},{"ID":"{\"ID\":\"9ycNxB\",\"NAME\":\"QCwfl2\"}","NAME":null},{"ID":"{\"ID\":\"tPHqwq\",\"NAME\":\"O7ksVc\"}","NAME":null},{"ID":"{\"ID\":\"GVLS81\",\"NAME\":\"KpgY4J\"}","NAME":null},{"ID":"{\"ID\":\"5PaX5g\",\"NAME\":\"28TPkn\"}","NAME":null},{"ID":"{\"ID\":\"WIIFmD\",\"NAME\":\"Xbf6TQ\"}","NAME":null},{"ID":"{\"ID\":\"IAM0oh\",\"NAME\":\"DBTilC\"}","NAME":null},{"ID":"{\"ID\":\"iGTpHH\",\"NAME\":\"cezESj\"}","NAME":null},{"ID":"{\"ID\":\"OtyAj9\",\"NAME\":\"VE6yeq\"}","NAME":null},{"ID":"{\"ID\":\"MccpIV\",\"NAME\":\"1hRQRq\"}","NAME":null},{"ID":"{\"ID\":\"U3QKld\",\"NAME\":\"YU1Dhj\"}","NAME":null},{"ID":"{\"ID\":\"1RXnYj\",\"NAME\":\"Vj2FWp\"}","NAME":null},{"ID":"{\"ID\":\"w2Soil\",\"NAME\":\"qo09qj\"}","NAME":null},{"ID":"{\"ID\":\"yeuJ1i\",\"NAME\":\"L6gc4M\"}","NAME":null},{"ID":"{\"ID\":\"YzV7oN\",\"NAME\":\"2T8BWg\"}","NAME":null},{"ID":"{\"ID\":\"l2mCUl\",\"NAME\":\"ozVO96\"}","NAME":null},{"ID":"{\"ID\":\"yYVu5Y\",\"NAME\":\"U5jPWS\"}","NAME":null},{"ID":"{\"ID\":\"g3sH25\",\"NAME\":\"HrnABs\"}","NAME":null},{"ID":"{\"ID\":\"SIhyXm\",\"NAME\":\"W4UmKl\"}","NAME":null},{"ID":"{\"ID\":\"mLlr0e\",\"NAME\":\"yyXJ5F\"}","NAME":null},{"ID":"{\"ID\":\"9XT2aW\",\"NAME\":\"hg5YxW\"}","NAME":null},{"ID":"{\"ID\":\"kgFN6P\",\"NAME\":\"UfZMXW\"}","NAME":null},{"ID":"{\"ID\":\"cwynEa\",\"NAME\":\"u4Rw7Z\"}","NAME":null},{"ID":"{\"ID\":\"TJi4xa\",\"NAME\":\"tPT5mO\"}","NAME":null},{"ID":"{\"ID\":\"0PZz0F\",\"NAME\":\"qsPRnA\"}","NAME":null},{"ID":"{\"ID\":\"KD2lVY\",\"NAME\":\"6wnn2s\"}","NAME":null},{"ID":"{\"ID\":\"Xx1bqk\",\"NAME\":\"stIOYx\"}","NAME":null},{"ID":"{\"ID\":\"uRsKTL\",\"NAME\":\"3s7BFu\"}","NAME":null},{"ID":"{\"ID\":\"Uzmokt\",\"NAME\":\"039iC5\"}","NAME":null},{"ID":"{\"ID\":\"usTbzC\",\"NAME\":\"AuSR6v\"}","NAME":null},{"ID":"{\"ID\":\"gu2pWP\",\"NAME\":\"qMsIPQ\"}","NAME":null},{"ID":"{\"ID\":\"lvq7GI\",\"NAME\":\"n4S4Ot\"}","NAME":null},{"ID":"{\"ID\":\"sIqGoF\",\"NAME\":\"JpwGl2\"}","NAME":null},{"ID":"{\"ID\":\"UpTOjH\",\"NAME\":\"sOLlhv\"}","NAME":null},{"ID":"{\"ID\":\"JM7Vix\",\"NAME\":\"vVBqxC\"}","NAME":null},{"ID":"{\"ID\":\"AvvHW6\",\"NAME\":\"gdiY1r\"}","NAME":null},{"ID":"{\"ID\":\"QK9TvP\",\"NAME\":\"siG8vg\"}","NAME":null},{"ID":"{\"ID\":\"7WTmNc\",\"NAME\":\"RGgs0P\"}","NAME":null},{"ID":"{\"ID\":\"1oDwq6\",\"NAME\":\"a9k0BE\"}","NAME":null},{"ID":"{\"ID\":\"REYqfm\",\"NAME\":\"Xj276z\"}","NAME":null},{"ID":"{\"ID\":\"aGpOF4\",\"NAME\":\"kYCSSC\"}","NAME":null},{"ID":"{\"ID\":\"teZVQ6\",\"NAME\":\"mtJLG7\"}","NAME":null},{"ID":"{\"ID\":\"U7Etr9\",\"NAME\":\"n5DV8e\"}","NAME":null},{"ID":"{\"ID\":\"Vq3wV3\",\"NAME\":\"XwWbBv\"}","NAME":null},{"ID":"{\"ID\":\"6BROdm\",\"NAME\":\"wxNTVM\"}","NAME":null},{"ID":"{\"ID\":\"Q5UCZo\",\"NAME\":\"kSystn\"}","NAME":null},{"ID":"{\"ID\":\"Jcz6Tr\",\"NAME\":\"cDL7Ez\"}","NAME":null},{"ID":"{\"ID\":\"9C4MwF\",\"NAME\":\"8lAoPk\"}","NAME":null},{"ID":"{\"ID\":\"q7sKkB\",\"NAME\":\"8TOgmU\"}","NAME":null},{"ID":"{\"ID\":\"dnpCzi\",\"NAME\":\"aT01V3\"}","NAME":null},{"ID":"{\"ID\":\"gtoA84\",\"NAME\":\"THqgsl\"}","NAME":null},{"ID":"{\"ID\":\"zuO1BP\",\"NAME\":\"A4KQWp\"}","NAME":null},{"ID":"{\"ID\":\"bKEjPR\",\"NAME\":\"63VRrh\"}","NAME":null},{"ID":"{\"ID\":\"vKHdPC\",\"NAME\":\"C6Ejvz\"}","NAME":null},{"ID":"{\"ID\":\"Trb4xN\",\"NAME\":\"1iJMCG\"}","NAME":null},{"ID":"{\"ID\":\"Ajar0R\",\"NAME\":\"QKPQEw\"}","NAME":null},{"ID":"{\"ID\":\"kO0EdX\",\"NAME\":\"mup8e8\"}","NAME":null},{"ID":"{\"ID\":\"fEbhGb\",\"NAME\":\"b4bOwh\"}","NAME":null},{"ID":"{\"ID\":\"ycDCgV\",\"NAME\":\"yZINsl\"}","NAME":null},{"ID":"{\"ID\":\"6x7xyX\",\"NAME\":\"jNqS40\"}","NAME":null},{"ID":"{\"ID\":\"JZWDS7\",\"NAME\":\"z4a16X\"}","NAME":null},{"ID":"{\"ID\":\"LMrrlD\",\"NAME\":\"k5TO8w\"}","NAME":null},{"ID":"{\"ID\":\"ndi4Vj\",\"NAME\":\"VaLSTq\"}","NAME":null},{"ID":"{\"ID\":\"YxpfQS\",\"NAME\":\"Rx8joC\"}","NAME":null},{"ID":"{\"ID\":\"x7ijav\",\"NAME\":\"FG21r7\"}","NAME":null},{"ID":"{\"ID\":\"2B2SyB\",\"NAME\":\"UGKGyB\"}","NAME":null},{"ID":"{\"ID\":\"7PYHr3\",\"NAME\":\"UlZ03G\"}","NAME":null},{"ID":"{\"ID\":\"H4DBgB\",\"NAME\":\"oztQIG\"}","NAME":null},{"ID":"{\"ID\":\"f9TwSX\",\"NAME\":\"WnY8uT\"}","NAME":null},{"ID":"{\"ID\":\"9IOX5A\",\"NAME\":\"rCMEPe\"}","NAME":null},{"ID":"{\"ID\":\"hYtJmh\",\"NAME\":\"AAZqQM\"}","NAME":null},{"ID":"{\"ID\":\"8WKcfc\",\"NAME\":\"Wujij9\"}","NAME":null},{"ID":"{\"ID\":\"4eLWl1\",\"NAME\":\"o7sWdL\"}","NAME":null},{"ID":"{\"ID\":\"t0OKol\",\"NAME\":\"Z0QG6u\"}","NAME":null},{"ID":"{\"ID\":\"qqsv8h\",\"NAME\":\"WYbu2F\"}","NAME":null},{"ID":"{\"ID\":\"vTX9mY\",\"NAME\":\"0eHpce\"}","NAME":null},{"ID":"{\"ID\":\"PULIws\",\"NAME\":\"cF4GFa\"}","NAME":null},{"ID":"{\"ID\":\"6WLljD\",\"NAME\":\"TL2x4U\"}","NAME":null},{"ID":"{\"ID\":\"ORNDMi\",\"NAME\":\"4Rqde4\"}","NAME":null},{"ID":"{\"ID\":\"TndbUX\",\"NAME\":\"IQAUh5\"}","NAME":null},{"ID":"{\"ID\":\"EeNfmZ\",\"NAME\":\"bjpXll\"}","NAME":null},{"ID":"{\"ID\":\"qduJib\",\"NAME\":\"e8Rks8\"}","NAME":null},{"ID":"{\"ID\":\"gVzZq7\",\"NAME\":\"V319cR\"}","NAME":null},{"ID":"{\"ID\":\"tV8ADu\",\"NAME\":\"STDDfa\"}","NAME":null}]}

Please check and let me know your feedback.

Thank you!

Best Regards,

Priya

[Anonymous]

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

[PRIYABOSCH]

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.

[PRIYABOSCH]

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

[Anonymous]

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

[PRIYABOSCH]

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

[lennelei]

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

[Anonymous]

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