parsing and extract json

Highlighted
Seven Stars

parsing and extract json

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

package.PNG

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

1.PNG2.PNG5.PNG4.PNG6.PNG7.PNG


Accepted Solutions
Community Manager

Re: parsing and extract json

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.


All Replies
Twelve Stars

Re: parsing and extract json

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Seven Stars

Re: parsing and extract json

thank you, but without java code it extracts nothing

Community Manager

Re: parsing and extract json

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....

 

Screenshot 2019-06-17 at 16.41.11.png

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. 

Seven Stars

Re: parsing and extract json

 
Seven Stars

Re: parsing and extract json

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

Community Manager

Re: parsing and extract json

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. 

Seven Stars

Re: parsing and extract json

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

regardsCve1.PNG2.PNG22.PNG3.PNG4.PNG

Community Manager

Re: parsing and extract json

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

Seven Stars

Re: parsing and extract json

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

regards  6.PNG7.PNG8.PNG

Community Manager

Re: parsing and extract json

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

Seven Stars

Re: parsing and extract json

Ram: 8 g, I've already tested it but it shows the same problem9.PNG

Community Manager

Re: parsing and extract json

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.

Seven Stars

Re: parsing and extract json

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

Community Manager

Re: parsing and extract json

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/...) and make some of the changes suggested.

 

How much RAM does you computer have?

Seven Stars

Re: parsing and extract json

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

10.PNG

 

 

Community Manager

Re: parsing and extract json

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.

Seven Stars

Re: parsing and extract json

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

er.PNG

Community Manager

Re: parsing and extract json

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.

Seven Stars

Re: parsing and extract json

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

regards

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now