Extract values from JSON file in Talend

Question

I have json file like this:

{"2020-04-28": { "37,N7L2H4,Carry,CHOPA,PLX": { "inter_results": { "inter_mark": "GITA" ,"down": null ,"up": null ,"wiki": {"included": "false", "options": ["RRR", "SSS","HHH"] }} ,"38, N5L2J4, HURT, SERRA, PZT": { "inter_results": { "inter_mark": "MARI" ,"down": "250" ,"up": "1250" ,"wiki": {"included": "true", "options": ["XXX", "YYY"] }} ,"39, N4L2H4, HIBA, FILA, PFG": { "inter_results": { "inter_mark": "HILO" ,"down": "100" ,"up": "250" ,"wiki": {"included": "true", "options": ["RTG", "VTH","HJI","JKL"] }} } }

And i want extract the Values N7L2H4,N5L2J4,N4L2H4 from this json file using tFileInputJson with jsonPath.

user_899863 · Answer

Using the native components of Talend, this is hard to achieve. You can do it with some java code but it's not elegant.
Here's a solution using json components suite from Talend Exchange which you can download here

The component tJSONDocTraverseFields allows you to list all fields, paths and values of your json. It gives this output:

$.2020-04-28.37,N7L2H4,Carry,CHOPA,PLX.inter_results.inter_mark|4|inter_mark|"GITA"|false|21
$.2020-04-28.37,N7L2H4,Carry,CHOPA,PLX.inter_results.down|4|down|null|false|21
$.2020-04-28.37,N7L2H4,Carry,CHOPA,PLX.inter_results.up|4|up|null|false|21
$.2020-04-28.37,N7L2H4,Carry,CHOPA,PLX.inter_results.wiki.included|5|included|"false"|false|21
$.2020-04-28.37,N7L2H4,Carry,CHOPA,PLX.inter_results.wiki.options[0]|6|options|"RRR"|true|21
$.2020-04-28.37,N7L2H4,Carry,CHOPA,PLX.inter_results.wiki.options[1]|6|options|"SSS"|true|21
$.2020-04-28.37,N7L2H4,Carry,CHOPA,PLX.inter_results.wiki.options[2]|6|options|"HHH"|true|21
$.2020-04-28.38, N5L2J4, HURT, SERRA, PZT.inter_results.inter_mark|4|inter_mark|"MARI"|false|21
$.2020-04-28.38, N5L2J4, HURT, SERRA, PZT.inter_results.down|4|down|"250"|false|21
$.2020-04-28.38, N5L2J4, HURT, SERRA, PZT.inter_results.up|4|up|"1250"|false|21

You can then parse the json path to get the values you want:

I split the path by "." to get the field "37,N7L2H4,Carry,CHOPA,PLX", then split the result again on "," and get the first value.
tJSONDocOpen allows you to initialize your json file, it acts as a connection. You then select it in tJSONDocTraverseFields.

Extract values from JSON file in Talend

1 Answers