Nested Json Structure in Talend

[PRIYABOSCH]

Dear Experts,

I am very new to Talend. I have to prepare a json file with nested structure. I have used twritejsonfield component for that. I am just trying to explore the examples given in the talend portal, and trying to implement the job. since the examples are very simple it is difficult for me to create the complex structure.

I have a table with 3 composite key columns. the table will be looking like the screenshot. I have to create a nested json file based on this composite keys. for example in the above table, the marked columns will be having same set of value for first two columns and the third column data will be different.

so the target JSON structure should be LIKE given below.

1A{

1B{

11,

12,

13,

14}

}

}

i have grouped the above 3 columns with TWRITEJSONFILED , groupby option but it provides strange results. the mapping screenshot is given below.

Please help me in preparing the correct json file format.

Thanks in Advance.

Priya

[Anonymous]

Hi,

Please check the below job flow and the result set whether it suits the requirement.

Thanks and Regards,

Subhadip

JSON4.JPG
JSON5.JPG
JSON3.JPG
JSON2.JPG
JSON1.JPG

[PRIYABOSCH]

Dear Subhadip,

thank you verymuch for your reply. could you please share the screenshot of SCHEMA mapping in TWRITEJSONFILED COMPONENT. this is to know which one is loop element.

Thanks in Advance!

Priya

[PRIYABOSCH]

Dear Subhadip,

Thanks a ton. I found the correct screenshot. could you please let me know why the "set as group element " option is not available for the first key.

Thanks and Regards,

Priya

[PRIYABOSCH]

Also I am getting the below error while loading to byte datatype column in twritejsonfield. Starting job JOB_JSON_RESULT_TEST at 16:00 21/06/2019. [statistics] connecting to socket on port 3349 [statistics] connected [Fatal Error] :1:177: Character reference "&# [FATAL]: dap4_pipelines.job_json_result_test_0_1.JOB_JSON_RESULT_TEST - tWriteJSONField_1_In nu.xom.ParsingException: Character reference "&# at line 1, column 177 net.sf.json.JSONException: nu.xom.ParsingException: Character reference "&# at line 1, column 177 at net.sf.json.xml.XMLSerializer.read(XMLSerializer.java:386) at dap4_pipelines.job_json_result_test_0_1.JOB_JSON_RESULT_TEST.tWriteJSONField_1_InProcess(JOB_JSON_RESULT_TEST.java:4029) at dap4_pipelines.job_json_result_test_0_1.JOB_JSON_RESULT_TEST$1ThreadXMLField_tWriteJSONField_1_Out.run(JOB_JSON_RESULT_TEST.java:2166) Caused by: nu.xom.ParsingException: Character reference "&# at line 1, column 177 at nu.xom.Builder.build(Unknown Source) at nu.xom.Builder.build(Unknown Source) at net.sf.json.xml.XMLSerializer.read(XMLSerializer.java:364) ... 2 more Caused by: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 177; Character reference "&# at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1239) ... 5 more Exception in component tWriteJSONField_1_In (JOB_JSON_RESULT_TEST) [FATAL]: dap4_pipelines.job_json_result_test_0_1.JOB_JSON_RESULT_TEST - tWriteJSONField_1_In nu.xom.ParsingException: Character reference "&# at line 1, column 177 net.sf.json.JSONException: nu.xom.ParsingException: Character reference "&# at line 1, column 177 at net.sf.json.xml.XMLSerializer.read(XMLSerializer.java:386) at dap4_pipelines.job_json_result_test_0_1.JOB_JSON_RESULT_TEST.tWriteJSONField_1_InProcess(JOB_JSON_RESULT_TEST.java:4029) at dap4_pipelines.job_json_result_test_0_1.JOB_JSON_RESULT_TEST$1ThreadXMLField_tWriteJSONField_1_Out.run(JOB_JSON_RESULT_TEST.java:2166) Caused by: nu.xom.ParsingException: Character reference "&# at line 1, column 177 at nu.xom.Builder.build(Unknown Source) at nu.xom.Builder.build(Unknown Source) at net.sf.json.xml.XMLSerializer.read(XMLSerializer.java:364) ... 2 more Caused by: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 177; Character reference "&# at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1239) ... 5 more net.sf.json.JSONException: nu.xom.ParsingException: Character reference "&# at line 1, column 177 at net.sf.json.xml.XMLSerializer.read(XMLSerializer.java:386) at dap4_pipelines.job_json_result_test_0_1.JOB_JSON_RESULT_TEST.tWriteJSONField_1_InProcess(JOB_JSON_RESULT_TEST.java:4029) at dap4_pipelines.job_json_result_test_0_1.JOB_JSON_RESULT_TEST$1ThreadXMLField_tWriteJSONField_1_Out.run(JOB_JSON_RESULT_TEST.java:2166) Caused by: nu.xom.ParsingException: Character reference "&# at line 1, column 177 at nu.xom.Builder.build(Unknown Source) at nu.xom.Builder.build(Unknown Source) at net.sf.json.xml.XMLSerializer.read(XMLSerializer.java:364) ... 2 more Caused by: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 177; Character reference "&# at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1239) ... 5 more [statistics] disconnected Job JOB_JSON_RESULT_TEST ended at 16:00 21/06/2019. [exit code=1] Kindly help me in resolving. Thanks and Regards, Priya

[PRIYABOSCH]

Dear Subhadip/experts,

I need to load the data coming out of twritejsonfield component to a json file. if I include toutputjsonfile component, I get only root node in the mapping area.

also I need to write the json data prepared with some headers like given below,

{  

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

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

"description": "JSON Schema for data IDs

{

---

---

}

} how this can be achieved with twritejsonfield or toutputjsonfile component. 

Regards,

Priya

[Anonymous]

Hi @PRIYABOSCH 

Please mark this topic as solved if the solution suits your requirement.

Thanks and Regards,

Subhadip