topic Re: How to extract values from Dynamic JSON fields? in Talend Studio

How to extract values from Dynamic JSON fields?

jerownimow — Sat, 16 Nov 2024 03:52:05 GMT

Hi Folks,

Good day!

I am new to Talend and badly needed your help. I have this job that consist of the following:

1st subjob:
processing logic -> tHashOutput (contains invoice and other fields)

2nd subjob:
Hash (contains the source of AggGrp - see attachment Source and Expected Output.json) -> tMap (to create JSON RequestBody) -> tAggregateRow (to get only the last generated string) -> tReplicate -> RestClient1 and RestClient2

I can get the response from both of these successfully. The problem is that they have dynamic JSON fields (doesn't look like a JSON field but a value).

Please see attached Response_1.json and Response_2.json for RestClient1 and RestClient2, respectively.

How can I extract these fields and process them to these:

Source and Expected Output.json
(This includes both Source and expected output)

P.S. There are also descriptions on the files on how it will be mapped. Let me know if you need more info.

Please help me. Thank you so much.

Re: How to extract values from Dynamic JSON fields?

jerownimow — Thu, 12 Dec 2019 04:13:03 GMT

Hi @rhall ,
Good day! sorry to bother you again. Seems that your solution in here Parse hashmap inside a json will work on my problem but I don't know how to execute it. I'm quite confused with point 1 and the output myColumn. On the issue posted, he wants 2 outputs both the key and the value if I'm not mistaken.

Can you please explain it to me like you explained the tJavaFlex on my other post?

Thank you so much.

Re: How to extract values from Dynamic JSON fields?

Anonymous — Thu, 12 Dec 2019 11:45:06 GMT

This is incredibly hard for someone who is new. If you do not have any Java knowledge, this will make it almost impossible to describe here. So I have built a job using TOS 7.2. This will extract the data from Response1.txt. I have had to download the org.json Jar and import it into the job. You will probably need to do this on your side (I don't believe my export will include that). There is a tLibraryLoad component which needs to have this Jar configured. You can find the Jar here: https://jar-download.com/artifacts/org.json

I hope this helps. Unfortunately I cannot spend a great deal more time on this problem. Hopefully you can extrapolate from this if you need to do more. If not, you will need to find a friendly Java developer to help you with this. By the way, Java is REALLY useful with Talend 🙂

DynamicJSON.zip

Re: How to extract values from Dynamic JSON fields?

jerownimow — Fri, 13 Dec 2019 00:26:53 GMT

No worries @rhall . Thank you so much for your time. Any online trainings if there's free or readable materials you can recommend for me to learn java?

Also, I got an error when tried to export the job. It says that 'Job Designs GetDynamicJSON 0.1' The item 'Job Designs GetDynamicJSON 0.1' from project 'Local_Project' was invalid.

Re: How to extract values from Dynamic JSON fields?

jerownimow — Fri, 13 Dec 2019 09:39:30 GMT

Hi @rhall seems that it is not compatible with my studio TOS for Big Data 7.1.

Re: How to extract values from Dynamic JSON fields?

Anonymous — Fri, 13 Dec 2019 11:44:25 GMT

You will need to use v7.2 to open it. I used TOS ESB v7.2.

Re: How to extract values from Dynamic JSON fields?

Anonymous — Fri, 13 Dec 2019 11:50:43 GMT

Sorry, I've had a lot of alerts to go through and I am responding in order. I've just noticed you sent me another message. As I said, try installing v7.2 and the job should import. With regard to learning Java, there are loads of online methods. Depending on your level of existing experience, there are different options. For an absolute beginner, I would recommend this approach https://www.bluej.org/

Re: How to extract values from Dynamic JSON fields?

jerownimow — Fri, 13 Dec 2019 12:32:10 GMT

Hi @rhall

No problem. Sorry for the spam, I didn't noticed that you already had a reply.
Unfortunately, I am not able to have 7.2 on my PC. Is it possible to just send me the screenshots like you did on the other thread we have?

Sorry for requesting this much and thank you for your tips! Will definitely grind on that.

Re: How to extract values from Dynamic JSON fields?

Anonymous — Fri, 13 Dec 2019 13:38:57 GMT

OK, here are some screenshots and the code I used.....

The tLibraryLoad component needs the Jar file I mentioned in my earlier post. After configuring that, you will need to set the other components up then save, close the job, then re-open the job. This is a *feature* that some people experience with Java libraries not being "seen" by the job. Hopefully you will not have any issues there.

The tFileInputJSON component is configured like below....

Essentially you are passing the whole JSON from the file to the next component in one go.

The tJavaFlex config can be seen below. I am just including the code. You can figure out the schema from the code. This must be copied and pasted exactly as it is shown.

Start Code

// start part of your Java code
String json = ((String)globalMap.get("json"));
org.json.JSONObject jsonObj = new org.json.JSONObject(json);
org.json.JSONObject appMapObj = jsonObj.getJSONObject("applicationMap");
org.json.JSONObject taxesObj = appMapObj.getJSONObject("taxes");
java.util.Iterator<String> taxKeysIt = taxesObj.keys();

while(taxKeysIt.hasNext() ) {
	String taxKey = (String)taxKeysIt.next();
	
	org.json.JSONObject taxKeyObj = taxesObj.getJSONObject(taxKey);

	java.util.Iterator<String> innerKeysIt = taxKeyObj.keys();
	
	while(innerKeysIt.hasNext() ) {
		String innerKey = innerKeysIt.next();
		
		org.json.JSONArray innerArr = taxKeyObj.getJSONArray(innerKey);
		
		java.util.Iterator<Object> innerArrIt = innerArr.iterator();
		
		while(innerArrIt.hasNext()){

Main Code

			org.json.JSONObject innerObj = (org.json.JSONObject)innerArrIt.next();
			row2.taxes = taxKey;
			row2.innerTaxCode = innerKey;
			row2.amount = !innerObj.isNull("amount") ? innerObj.getDouble("amount") : 0;
			row2.initialAmount = !innerObj.isNull("initialAmount") ?  innerObj.getDouble("initialAmount") : 0;
			row2.description = !innerObj.isNull("description") ? innerObj.getString("description") : "";
			row2.taxItemId = !innerObj.isNull("taxItemId") ? innerObj.getInt("taxItemId") : 0;

End Code

// end of the component, outside/closing the loop
		}
	}
}

The tLogRow just prints out the results.

Re: How to extract values from Dynamic JSON fields?

jerownimow — Mon, 16 Dec 2019 13:23:16 GMT

Thank you so much @rhall . It really worked, and I was able to proceed with the rest of the transformations.
Thank you so much for your time! God bless you, man!

Re: How to extract values from Dynamic JSON fields?

Anonymous — Mon, 16 Dec 2019 21:04:42 GMT

Glad I could help. You can return the favour by helping others as your experience grows 🙂

Re: How to extract values from Dynamic JSON fields?

Andreas_B1 — Wed, 14 Feb 2024 09:22:18 GMT

Hello,
this community thread has moved from https://community.talend.com/s/question/0D53p00007vClF4CAK/how-to-extract-values-from-dynamic-json-fields?language=en_US but it seems that the attachment of the json file was not migrated properly, so that the whole solution cannot be comprehended any more.
@jerownimow could you please reattach the file to this thread. Thank you