Splitting JSON array into objets (each in a row)

[VictorFaure]

Hello,

I'm currently working on requesting a paginated API which returns data into an array containing several objects like so:
"array_elem": [{"prop1": "val1", "prop2": "val2"}, {"prop1": "val3", "prop2": "val4"}]
What I want to do finally is to merge the arrays I receive from the different pages into one big array.
What I'm struggling with is splitting the above array into 2 rows containing json objects like this:
row1: {"prop1": "val1", "prop2": "val2"}

row2: {"prop1": "val3", "prop2": "val4"}

instead I get
["val1", "val2"]

["val3, "val4"]

And when merging into the big array it becomes 2D array and we lose the properties' names.

Here is my tExtractJSONFields config:

[Shicong_Hong · Accepted solution]

@VictorFaure 

Hello!

You can read the data as a string, then use Java code to intercept the substring, and finally, use the tNormalize component to split the string into multiple rows.

Java code on tJavaRow_1:

//Code generated according to input schema and output schema
output_row.body = (input_row.body.substring(input_row.body.indexOf(":")+1)).replaceAll("\\[", "").replaceAll("\\]", "");
output_row.body=output_row.body.replaceAll("\\}, \\{","\\}; \\{");

Hope it helps you!

Regards

Shicong

[VictorFaure]

Hello and thanks for the reply !

I usually dislike using regex replace on json strings but it seemed to be the only solution in this case, I have done a variation of the setup you show here and it works for me (I remove the square brackets and replace with broken pipe as separator in case of semicolon in my data).

Thanks again