tWriteJSONField please help building thos JSON , with array and subcategories

Anonymous — Fri, 15 Jun 2018 18:36:20 GMT

Hello,

We have this structure that we need to create in tWriteJsonField and we cannot get exact structure. I below Json we have an array . So,
Glass - is category of product there could be multiple categories . For each of this category we need to display Ship_To,ShipTo_Name.......Dec(UOM). All Values under glass category group by state.
So, for state PA in this example we have 3 customers we need to list values for each .
Important that at the end of each state report we need to sum all 3 customer (in this example) and show totals .

So my issue is building array and all brackets exactly as show in the example . Also how do we group like this? can anyone show how tWriteJsonField would look like to have this structure.

Another issue, i have actually json file with data in it (that was generated by other system sql server) i thought i could just import it to use that structure but talend does not let me import it.

Thank you in advance
JSON:
-----

{
"title": "Summary Report ",
"reportDate": "15/06/2018",
"Customer":"ALL",
"reportName": "Summary",
"duration": "3/15/2018",
"otherDetails": {
"Product_Category": "ALL",
"State(s)": "ALL",
"Ship_To_Account(s)": "ALL"
},
"datas": [

{
"Glas": [

{
"Ship_To": "9999998",
"ShipTo_Name": "HOME HOME",
"City": "Philly",
"State": "PA",
"Total MSF Shipped": 0.660,
"Jan(MSF)": 0.000,
"Feb(MSF)": 0.660,
"Mar(MSF)": 0.000,
"Apr(MSF)": 0.000,
"May(MSF)": 0.000 ,
"Jun(MSF)": 0.000 ,
"Jul(MSF)": 0.000 ,
"Aug(MSF)": 0.000,
"Sep(MSF)": 0.000,
"Oct(MSF)": 0.000 ,
"Nov(MSF)": 0.000,
"Dec(MSF)": 0.000
},
{
"Ship_To": "7777",
"ShipTo_Name": "BLDG ",
"City": "Philly",
"State": "PA",
"Total MSF Shipped": 7.040,
"Jan(MSF)": 0.000,
"Feb(MSF)": 0.000,
"Mar(MSF)": 1.408,
"Apr(MSF)": 4.224,
"May(MSF)": 0.000 ,
"Jun(MSF)": 1.408 ,
"Jul(MSF)": 0.000 ,
"Aug(MSF)": 0.000,
"Sep(MSF)": 0.000,
"Oct(MSF)": 0.000 ,
"Nov(MSF)": 0.000,
"Dec(MSF)": 0.000
},
{
"Ship_To": "2312312312",
"ShipTo_Name": "Nameof Ship to",
"City": "Philly",
"State": "PA",
"Total MSF Shipped": 5.632,
"Jan(MSF)": 0.000,
"Feb(MSF)": 2.816,
"Mar(MSF)": 0.000,
"Apr(MSF)": 1.408,
"May(MSF)": 1.408 ,
"Jun(MSF)": 0.000 ,
"Jul(MSF)": 0.000 ,
"Aug(MSF)": 0.000,
"Sep(MSF)": 0.000,
"Oct(MSF)": 0.000 ,
"Nov(MSF)": 0.000,
"Dec(MSF)": 0.000
}
,{
"Total for State BC":[13.332, 0.000,3.476, 1.408,5.632,
1.408,1.408, 0.000,0.000, 0.000,0.000, 0.000,
0.000]
}
]

topic tWriteJSONField please help building thos JSON , with array and subcategories in Talend Studio

tWriteJSONField please help building thos JSON , with array and subcategories