Hello,
In fact, I just started working with talend open studio for data integration; I want to export a 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
It is necessary that the data in the JSON file be structured in a mysql database according to the same division so I need to load JSON files periodically in a table sql(insert in the same table) in the SQL Server database. Can you explain to me which components with which configuration and schema I can use?
How I would like the output to look like:
| CVE | Scope | debainbug | description | status | fixed_version | urgency |
I attach one part short my jsonfille : https://security-tracker.debian.org/tracker/data/json
I thank you in advance.
I use "Talend open studio for data integration version 7.1.1.2018102681147
Thank you very much for your time and help
Best regard
Accepted Solutions
I've looked into this and have found that your JSON is of an irregular structure. You seem to have CVEs that are grouped together with other CVEs. If you consume your data using a tFileInputJSON and have the following configuration....
....then connect the output to a tLogRow, you will see a couple of thousand JSON sub sections. They all appear to be individual CVE sections (and some are), but if you look deeper you will see that some are grouped together. The rule is needed for this.
I've written some Java to pull the sub CVE sections out of the groups. This showed me that the debianbug field does not always appear. The code I used can be seen below....
public static ArrayList<String> getSubJSON(String json) {
ArrayList<String> returnArray = new ArrayList<String>();
try {
JSONObject obj = new JSONObject(json);
java.util.Iterator<String> it = obj.keys();
while(it.hasNext()) {
String key = (it.next()).toString();
returnArray.add("{\""+key+"\":"+obj.get(key).toString()+"}");
}
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return returnArray;
}
You will need the following imports...
import org.json.*; import java.util.ArrayList;
....and will need to import the java-json.jar into your routine to compile the above code.
I used the above code to pull out the grouped CVE sections and got the result which I have added in the attached file.
Before you can get any further with this, you will need to work out why the structure is as it is. Once you know this, you will need to use a bit of Java to pull out the CVE names, since dynamic JSON is not handled very well by the standard components. The code I have given you shows how to achieve this though.
I shall be away on holiday for the next week, but hopefully you can extrapolate from this (or someone else can follow this and fill in any blanks I may have left).
Good luck.
JSON_Objects.txt
hello;
I managed to connect my database on sqlserver but my problem is on the data extraction with tJsonExtart components, I could not extract correct and it displays the colouns null
How I would like the output to look like:
| CVE | Scope | debainbug | description | status | fixed_version | urgency |
inputfille.docx
hello;
I managed to connect my database on sqlserver but my problem is on the data extraction with tJsonExtart components, I could not extract correct and it displays the colouns null
How I would like the output to look like:
| CVE | Scope | debainbug | description | status | fixed_version | urgency |
inputfille.docx
inputfille.docx
I've looked into this and have found that your JSON is of an irregular structure. You seem to have CVEs that are grouped together with other CVEs. If you consume your data using a tFileInputJSON and have the following configuration....
....then connect the output to a tLogRow, you will see a couple of thousand JSON sub sections. They all appear to be individual CVE sections (and some are), but if you look deeper you will see that some are grouped together. The rule is needed for this.
I've written some Java to pull the sub CVE sections out of the groups. This showed me that the debianbug field does not always appear. The code I used can be seen below....
public static ArrayList<String> getSubJSON(String json) {
ArrayList<String> returnArray = new ArrayList<String>();
try {
JSONObject obj = new JSONObject(json);
java.util.Iterator<String> it = obj.keys();
while(it.hasNext()) {
String key = (it.next()).toString();
returnArray.add("{\""+key+"\":"+obj.get(key).toString()+"}");
}
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return returnArray;
}
You will need the following imports...
import org.json.*; import java.util.ArrayList;
....and will need to import the java-json.jar into your routine to compile the above code.
I used the above code to pull out the grouped CVE sections and got the result which I have added in the attached file.
Before you can get any further with this, you will need to work out why the structure is as it is. Once you know this, you will need to use a bit of Java to pull out the CVE names, since dynamic JSON is not handled very well by the standard components. The code I have given you shows how to achieve this though.
I shall be away on holiday for the next week, but hopefully you can extrapolate from this (or someone else can follow this and fill in any blanks I may have left).
Good luck.
JSON_Objects.txt
first of all thank you for your answer, I saw your code and what extract .
I tried to do the steps you said, first I created a new routine and added the code and then added it in tmap but it displays this error Mismatch type: cannot convert from ArrayList<String> to String talend
I add the pictures of d; in any case I can't extract the data and it still displays null in tlogrow .
in addition, on the one hand I don't know how I configure tExtractjsonfields
On the other hand I tried to use in Talend the cves file you attached but it just gives me a CVE
do you have any advice for my problem?
I change my job and I have successfully extracted the tables and saved in my database servers with other code but I have a problem I don't know why the column debainbug is null in case in my tfileinputjson it has values but in textractjsonField it is null
I attach all the configuration photos of my components and the code; can someone help me?
thanks
