JSON per row

[MattE]

Hi,

I've got a database in the following format

id int

modified_on date

json text

the json field is a json string of objects in the format

{"json_id":1234, "Name": "My name 1", "Value": "my value 1"}, {"json_id":56789, "Name": "My name 2", "Value": "my value 2"}

i want o output a json file where the object structure is

{"Id": 1234, "modified_on": "2023-01-01", "json_id":1234, "Name": "My name 1", "Value": "my value 1"}, {"Id": 56789, "modified_on": "2023-01-01", "json_id":56789, "Name": "My name 2", "Value": "my value 2"}

But I'm struggling with standard talend components. I've got some java code which uses JSON.org to parse the json text field but it seems like tJavaFlex and tJavaRow can only output 1 row at the end rather than the one row per object i need.

Any one any idea how to achieve this?

Thanks

[rhall1 · Accepted solution]

If you've already got the code which generates your modified JSON String for each row, you are almost there. There is a simple way of doing this from this point. There may be better overall solutions, but this will get it working for you.

In the Start Code section, create a String variable called something like "finalJSON". It doesn't matter what you call it, but you need a variable. Then in the Main Code section where you are modifying your JSON, simply append the output JSON to this. Something like this....

finalJSON = finalJSON + "," + modifedJSON;

I am assuming that your modified JSON has a name like "modifiedJSON".

Then the next bit of code you need in the Main Code section is your output column data. Let's say you called it "JSON" and the output row is called "row2". Assign your "finalJSON" value to row2.JSON, like this.....

row2.JSON = finalJSON;

This will increment your JSON part by part over the entire job. So to ensure that you only get the fully built JSON, add a tAggregateRow straight after your tJavaFlex. Then set the JSON column in the Operations box and apply the function of "Last". This will only output your last row, which will be your complete JSON.

One last bit. Your JSON will not be complete as it will not be fully formed without the beginning and end being set up appropriately. I am assuming you require an array structure for this. A simple way of doing this is to add one last component. It could be another tJavaFlex. In the Main Code section, do something like this (assuming the incoming row is row4 and the outgoing row is row5).....

row5.JSON = "{\"myArray\":["+row4.JSON+"]}";

This will wrap your JSON in an array and will look like this....

{"myArray":[{"Id": 1234, "modified_on": "2023-01-01", "json_id":1234, "Name": "My name 1", "Value": "my value 1"}, {"Id": 56789, "modified_on": "2023-01-01", "json_id":56789, "Name": "My name 2", "Value": "my value 2"}]}

If you copy and paste the above into https://jsonpathfinder.com/ you will see how it turns out.

You may need to tweak this to your requirements, but it should work for you.