topic Re: parsing and extract json in Talend Studio

parsing and extract json

Anonymous — Sat, 16 Nov 2024 05:36:39 GMT

Hello,

In fact, I just started working with Talend open studio for data integration; I want to export an array long json and I want to transform the data coming from JSON by using Talend to organize and store them in a SQL database and sqlserver

my json: https://security-tracker.debian.org/tracker/data/json

How I would like the output to look like:

Package	CVE	Scope	Debian bug	description	status	fixed_version	urgency

I attach all the configuration photos of my components and the code; can someone help me?

I have two problems :

1: first I couldn't extract the package name; I attach a picture of my json file; in fact CVEs are categorized in different packages, CVEs extract good but I want to add a column Package in my DB ; I don't know what to add in tjava for node extraction from each cves that is called package

2: I found a problem my codes do not extract all the CVEs well for example in my source I have CVE-2019_3462 but my job does not extract it, I could not find what is the problem

Does anyone have any advice or ideas on how I can achieve this?

I use "Talend open studio for data integration version 7.1.1.2018102681147

Thank you very much for your time and help

Best regard

Re: parsing and extract json

fdenis — Wed, 12 Jun 2019 13:46:50 GMT

first try to work without using java.
look at document data type and txmlmap. it may be helpfull.

Re: parsing and extract json

Anonymous — Wed, 12 Jun 2019 14:20:08 GMT

thank you, but without java code it extracts nothing

Re: parsing and extract json

Anonymous — Mon, 17 Jun 2019 16:48:55 GMT

I have adjusted your code above to extract the Package Name and the JSON for that Package. You will need to further process this to get the CVEs from it each row of JSON returned. My job looks like below....

The tFileInputJSON_1 component is configured very similarly to yours. But the JSONPath for the content is set to "." instead of "*". This component is linked to a tSetGlobalVar_1. This stores the whole JSON into a String.

The tJavaFlex is configured with the following code.....

Start Code

Object obj = new org.json.simple.parser.JSONParser().parse(((String)globalMap.get("content")));
org.json.simple.JSONObject jobj = (org.json.simple.JSONObject) obj;
Set<String> jkeys = jobj.keySet();
Iterator<String> jit = jkeys.iterator();
while(jit.hasNext()){

Main Code

String jit1 = jit.next().toString();
row2.package_name = jit1;
row2.package_json = ((org.json.simple.JSONObject)jobj.get(jit1)).toJSONString();

End Code

This will return a row for each Package section. You then need to do something very similar to retrieve the CVEs from the Packages.

Re: parsing and extract json

Anonymous — Tue, 18 Jun 2019 10:43:42 GMT

first of all, thank you for your time and answer,

could you put the tSetGolabalVar component configuration and the tJavaFlex and advanced setting scheme of tJavaFlex
regards

Re: parsing and extract json

Anonymous — Tue, 18 Jun 2019 10:54:54 GMT

I could, but you have all the information you need in what I have already given you. The tSetGlobalVar receives the data from the tFileInputJSON_1 and stores it with a key of "content". This can be worked out from the Start Code of the tJavaFlex. The schema of the tJavaFlex can be figured out by looking at the Main Code.

I'm not trying to be difficult, but I have thrown away my example code and extrapolating from the information that is there will help you learn Talend.

Re: parsing and extract json

Anonymous — Wed, 19 Jun 2019 11:15:00 GMT

Actually, I asked you because despite that I don't change much I have the following error

regards

Re: parsing and extract json

Anonymous — Wed, 19 Jun 2019 11:34:14 GMT

Add....

row1.content

....where you have "" in your tSetGlobalVar. No quotes. You are referencing the row.column that is returned by your tFileInputJSON_1 component

Re: parsing and extract json

Anonymous — Wed, 19 Jun 2019 15:10:58 GMT

Thank you, I fixed that but I have other problems java heap space despite that I have already added XMS and XMX

regards

Re: parsing and extract json

Anonymous — Wed, 19 Jun 2019 15:55:38 GMT

How much RAM does your machine have? Also, do you get this error if you switch off everything after the tLogRow_1?

Re: parsing and extract json

Anonymous — Thu, 20 Jun 2019 09:43:10 GMT

Ram: 8 g, I've already tested it but it shows the same problem

Re: parsing and extract json

Anonymous — Thu, 20 Jun 2019 11:18:27 GMT

This is very strange. I am running the same job (I recreated it) with the standard 1024MB assigned to a job. My version will run with no issues whatsoever. I get the same number of rows displayed by your job. Can you replace the tLogRow component with another one? Simply delete it and add a new one. Leave the standard settings for the tLogRow.

Re: parsing and extract json

Anonymous — Thu, 20 Jun 2019 13:37:39 GMT

no I rebuilt the job with the new components and tested it but it shows the same problem. in any case I thank you very much

Re: parsing and extract json

Anonymous — Thu, 20 Jun 2019 13:55:48 GMT

This could be related to the Studio I guess. Take a look here (https://community.talend.com/t5/Migration-Configuration-and/Allocating-more-memory-to-Talend-Studio/ta-p/21642?advanced=false&collapse_discussion=true&filter=includeTkbs,location&include_tkbs=true&location=tkb-board:trb_mca&q=.ini%20file&search_type=thread) and make some of the changes suggested.

How much RAM does you computer have?

Re: parsing and extract json

Anonymous — Thu, 20 Jun 2019 15:13:53 GMT

thanks for the site but I've already made this change.finally, I managed to solve the problem by reinstalling another talend version; I'm tired because it doesn't work well and the cve columns are null

regards

Re: parsing and extract json

Anonymous — Thu, 20 Jun 2019 15:23:14 GMT

What version are you using? If it is v7.1.1 the attached job should be OK to be imported into your Studio. It will retrieve every CVE attached to a Package and returns the JSON for every CVE so that you can return the data associated to every CVE. Take a look at it to see how I achieved this. The problem you had was with dynamic arrays of key values. This solution can be extrapolated from to solve other similar issues.

Re: parsing and extract json

Anonymous — Fri, 21 Jun 2019 08:27:06 GMT

I now use version 7.1.1 . Thank you for attaching but I think there is a mistake to send the project.
it's for other projects

regards

Re: parsing and extract json

Anonymous — Fri, 21 Jun 2019 10:05:19 GMT

You are correct. I was working on another demonstration at the time and sent the wrong one. The one attached here is the correct one. Sorry about that.

test1234.zip

Re: parsing and extract json

Anonymous — Fri, 21 Jun 2019 14:33:38 GMT

I thank you very much. You help me a lot.

regards