# JSON to SQL database
**URL:** https://discourse.nodered.org/t/json-to-sql-database/25499
**Category:** General
**Created:** [25 April 2020 20:32 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499 "2020-04-25T20:32:07Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@asdf123](https://discourse.nodered.org/u/asdf123)
#### Post date: [25 April 2020 20:32 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/1 "2020-04-25T20:32:07Z")
Hi everyone,
Can you help me please with my project?
I'd like to download data from a website (here: [https://nvd.nist.gov/vuln/data-feeds](https://nvd.nist.gov/vuln/data-feeds)). It's a JSON file, and i'd like to insert the data to my sqlite database.
I created the database with the information i'd like to use, and I created a http request for the website.
I'd like to know, how can I insert the data from the JSON file to my databse correctly, because I'd like to create different selects and so on.
Any help would be wonderful.
Thank you.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [25 April 2020 20:45 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/2 "2020-04-25T20:45:16Z")
If you feed it through a JSON node that should convert it to a javascript object, then you should be able to extract whatever data you need to put in the db.
---
### Author:  [@asdf123](https://discourse.nodered.org/u/asdf123)
#### Post date: [25 April 2020 20:59 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/3 "2020-04-25T20:59:59Z")
I tried to use the JSON node, but I got an error: JSON parse error.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [25 April 2020 21:12 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/4 "2020-04-25T21:12:27Z")
Well it isn't json then. Can you copy/paste it here, or a bit of it at least if it is large. Are you sure you are not getting xml? It seemed to be an option when I looked briefly at the link you posted.
---
### Author:  [@asdf123](https://discourse.nodered.org/u/asdf123)
#### Post date: [25 April 2020 21:19 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/5 "2020-04-25T21:19:52Z")
Hi,
Yes I'm sure. I downloaded the zip file, and got a JSON. Here is the content:
```auto
"configurations" : {
"CVE_data_version" : "4.0",
"nodes" : [ {
"operator" : "OR",
"cpe_match" : [ {
"vulnerable" : true,
"cpe23Uri" : "cpe:2.3:a:google:chrome:*:*:*:*:*:*:*:*",
"versionEndExcluding" : "17.0.963.46"
} ]
} ]
},
"impact" : {
"baseMetricV2" : {
"cvssV2" : {
"version" : "2.0",
"vectorString" : "AV:N/AC:M/Au:N/C:P/I:P/A:P",
"accessVector" : "NETWORK",
"accessComplexity" : "MEDIUM",
"authentication" : "NONE",
"confidentialityImpact" : "PARTIAL",
"integrityImpact" : "PARTIAL",
"availabilityImpact" : "PARTIAL",
"baseScore" : 6.8
},
"severity" : "MEDIUM",
"exploitabilityScore" : 8.6,
"impactScore" : 6.4,
"acInsufInfo" : false,
"obtainAllPrivilege" : false,
"obtainUserPrivilege" : false,
"obtainOtherPrivilege" : false,
"userInteractionRequired" : true
}
},
"publishedDate" : "2012-02-09T04:10Z",
"lastModifiedDate" : "2020-04-17T13:31Z"
}, {
"cve" : {
"data_type" : "CVE",
"data_format" : "MITRE",
"data_version" : "4.0",
"CVE_data_meta" : {
"ID" : "CVE-2011-3958",
"ASSIGNER" : "cve@mitre.org"
},
"problemtype" : {
"problemtype_data" : [ {
"description" : [ {
"lang" : "en",
"value" : "CWE-416"
} ]
} ]
```
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [25 April 2020 21:39 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/6 "2020-04-25T21:39:20Z")
What is the exact error from the JSON node?
---
### Author:  [@asdf123](https://discourse.nodered.org/u/asdf123)
#### Post date: [25 April 2020 21:45 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/7 "2020-04-25T21:45:02Z")
That's all:
msg : string[16]
"JSON parse error"
---
### Author:  [@knolleary](https://discourse.nodered.org/u/knolleary)
#### Post date: [25 April 2020 22:09 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/8 "2020-04-25T22:09:54Z")
> [@asdf123](#):
>
> Yes I'm sure. I downloaded the zip file
What do you mean you downloaded a zip file?
I thought you were accessing JSON directly?
Can you share exactly what URL you are accessing? The page you have linked to contains dozens of links to JSON data and zip files.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [26 April 2020 05:39 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/9 "2020-04-26T05:39:17Z")
If you could export a short flow exhibiting the problem that would be even better.
---
### Author:  [@bakman2](https://discourse.nodered.org/u/bakman2)
#### Post date: [26 April 2020 05:43 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/10 "2020-04-26T05:43:28Z")
> [@asdf123](#):
>
> Here is the content:
That's not json as it is missing the opening `{`
Which file did you download ?
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [26 April 2020 07:19 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/11 "2020-04-26T07:19:04Z")
> [@bakman2](#):
>
> That's not json as it is missing the opening `{`
I am not sure that JSON requires that the content is always an object, rather than a sequence of items not contained in an object. See [RFC 7158 - The JavaScript Object Notation (JSON) Data Interchange Format](https://tools.ietf.org/html/rfc7158) , particularly the second para of section 2.
However, in the context of the JSON node which is expected to output an object perhaps the node only accepts {..}.
---
### Author:  [@dceejay](https://discourse.nodered.org/u/dceejay)
#### Post date: [26 April 2020 08:14 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/12 "2020-04-26T08:14:47Z")
No- see section 3. It is invalid Json.
---
### Author:  [@knolleary](https://discourse.nodered.org/u/knolleary)
#### Post date: [26 April 2020 08:20 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/13 "2020-04-26T08:20:16Z")
Personally, I'd give them the benefit of the doubt that they have made a mistake in the copy and paste in the forum, given the website they have pointed to lists lots of valid JSON feeds.
But my questions still stand as to exactly which URL they are trying to download. If they have picked one that wraps the JSON as a zip file, then they won't be able to download it and treat it as JSON straight away.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [26 April 2020 08:27 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/14 "2020-04-26T08:27:11Z")
> [@dceejay](#):
>
> No- see section 3.
Yes, on further consideration I think you are right. What it is saying is that a single json value is valid json (so "hello world" is valid json) but you can't have a sequence of them separated by commas unless they are in an object.
---
### Author:  [@asdf123](https://discourse.nodered.org/u/asdf123)
#### Post date: [26 April 2020 18:24 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/15 "2020-04-26T18:24:15Z")
I always want to use the last modified ZIP file, from the website. In that case : Feed: CVE-Modified, Updated: 4/26/2020 12:01:08 PM -04:00 and from the download section the ZIP file, in which there is a JSON file, and I want to use that JSON file in a form that I can insert the data into the sqlite database.
Sorry for the questions, I'm really new, and I didn't find so much information about that case.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [26 April 2020 20:57 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/16 "2020-04-26T20:57:47Z")
The example you posted isn't JSON because it does not start and end with { and }. Did you not see the posts saying that?
If they really are not there then you could add them and then try the JSON node.
---
### Author:  [@knolleary](https://discourse.nodered.org/u/knolleary)
#### Post date: [26 April 2020 21:05 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/17 "2020-04-26T21:05:41Z")
@Colin that is _not_ the issue here. The website only provides the data wrapped in a zip file. So the HTTP Request node cannot get the JSON directly - it gets the zip file which needs to be extracted before the JSON can be accessed.
@asdf123 here is a flow that gets the data, extracts the zip file and parses the JSON. You will need to install `node-red-contrib-zip` first.
```auto
[{"id":"483b0b05.a55344","type":"inject","z":"623e0198.c764b","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":240,"wires":[["f2b63ad.3ca42c8"]]},{"id":"f2b63ad.3ca42c8","type":"http request","z":"623e0198.c764b","name":"","method":"GET","ret":"bin","paytoqs":false,"url":"https://nvd.nist.gov/feeds/json/cve/1.1/nvdcve-1.1-modified.json.zip","tls":"","persist":false,"proxy":"","authType":"","x":290,"y":240,"wires":[["9ec4df54.c26fd"]]},{"id":"f45acf66.a0adb","type":"debug","z":"623e0198.c764b","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":850,"y":240,"wires":[]},{"id":"9ec4df54.c26fd","type":"zip","z":"623e0198.c764b","name":"","mode":"decompress","filename":"","outasstring":false,"x":450,"y":240,"wires":[["aad8c1ac.d4cd2"]]},{"id":"473d6965.d4fe88","type":"json","z":"623e0198.c764b","name":"","property":"payload","action":"","pretty":false,"x":710,"y":240,"wires":[["f45acf66.a0adb"]]},{"id":"aad8c1ac.d4cd2","type":"function","z":"623e0198.c764b","name":"","func":"msg.payload = msg.payload[0].payload.toString()\nreturn msg;","outputs":1,"noerr":0,"x":590,"y":240,"wires":[["473d6965.d4fe88"]]}]
```
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [26 April 2020 21:20 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/18 "2020-04-26T21:20:51Z")
> [@knolleary](#):
>
> @Colin that is _not_ the issue here.
Since @asdf123 posted the unzipped contents I assumed he had worked out out how to do that, as he gave the impression that the string he posted was what he fed into the JSON node. Perhaps that is not the case though.
---
### Author:  [@asdf123](https://discourse.nodered.org/u/asdf123)
#### Post date: [26 April 2020 21:36 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/19 "2020-04-26T21:36:44Z")
Thank you so much, I managed to solve the problem with your help.
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [10 May 2020 21:36 UTC](https://discourse.nodered.org/t/json-to-sql-database/25499/20 "2020-05-10T21:36:47Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.