how can i parsing json array and create the table on sql server

Highlighted
Seven Stars

how can i parsing json array and create the table on sql server

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:

CVEScopedebainbugdescriptionstatusfixed_versionurgency
       

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 regardextractjson.PNGinputjson.PNGjobtest.PNGtdboutput.PNGtextractjson.PNG


Accepted Solutions
Community Manager

Re: how can i parsing json array and create the table on sql server

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

Screenshot 2019-05-20 at 20.47.16.png

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

Seven Stars

Re: how can i parsing json array and create the table on sql server

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

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


All Replies
Nine Stars

Re: how can i parsing json array and create the table on sql server

1. you first extract data from your json file as you show it in the image. 

You can test using tlogrow

 

2. you create database connection and test that the connection is working correctly

 

3. last step tDBOutput

 

 

Regards
DGM
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seven Stars

Re: how can i parsing json array and create the table on sql server

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:

CVEScopedebainbugdescriptionstatusfixed_versionurgency
      

jobtest7.PNGinputjsontest.PNGextjsontest8.PNGconnectiontest8.PNGservertest8.PNG

Seven Stars

Re: how can i parsing json array and create the table on sql server

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:

CVEScopedebainbugdescriptionstatusfixed_versionurgency
      

jobtest7.PNGinputjsontest.PNGextjsontest8.PNGconnectiontest8.PNGservertest8.PNG

Seven Stars

Re: how can i parsing json array and create the table on sql server

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



Community Manager

Re: how can i parsing json array and create the table on sql server

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

Screenshot 2019-05-20 at 20.47.16.png

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

Seven Stars

Re: how can i parsing json array and create the table on sql server

I thank you very much for your answer and your time; I will test what you say
have a nice trip

Seven Stars

Re: how I can parsing json array and create the table on sql server

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?

mycode.PNGlib.PNGroutinedefi.PNGjob11.PNGtmaptest11.PNGeror.PNG

Seven Stars

Re: how can i parsing json array and create the table on sql server

Thank you very much for your answer

can you explain to me how to use the code with screenshot job and component configuration
Thank you very much.

Seven Stars

Re: how can i parsing json array and create the table on sql server

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

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

Community Manager

Re: how can i parsing json array and create the table on sql server

I think this is just a typo :-) I confused the exact same thing when I worked on this. The field is "debianbug" not "debainbug".

 

Good work on extrapolating from my code by the way :-)

Seven Stars

Re: how can i parsing json array and create the table on sql server

Thanks a lot for your time and help :-)
Community Manager

Re: how can i parsing json array and create the table on sql server

No problem, glad I could help :-)

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

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download