Write JSON to mysql database with node.js

Question

I'm trying to write a JSON object (or string, unsure) to my mysql database using node.js. I first retrieved the JSON via an xml url using xml2js. I am able to log the json string result in my console via JSON.stringify, but I am unsure how to proceed from here.

Here is the url I took the xml from: https://water.weather.gov/ahps2/hydrograph_to_xml.php?gage=deld1&output=xml

I would like to write each instance from the JSON string to a row, with the columns as the name of the data. It would look something like this:

Here is my code in index.js, which I enact with node index.js on the console:

var parseString = require('xml2js').parseString;
var http = require('http');
var https = require('https');
var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  database: "mydb"
});




function xmlToJson(url, callback) {
  var req = https.get(url, function(res) {
    var xml = '';

    res.on('data', function(chunk) {
      xml += chunk;
    });

    res.on('error', function(e) {
      callback(e, null);
    }); 

    res.on('timeout', function(e) {
      callback(e, null);
    }); 

    res.on('end', function() {
      parseString(xml, function(err, result) {
        callback(null, result);
      });
    });
  });
}

var url = "https://water.weather.gov/ahps2/hydrograph_to_xml.php?gage=deld1&output=xml"

xmlToJson(url, function(err, data) {
  if (err) {
    return console.err(err);
  }

  strungout = JSON.stringify(data, null, 1);
  console.log(strungout);
  //strungout contains my json string

})

  con.connect(function(err) {
  if (err) throw err;
  //below is where I might make an insert statement to insert my values into a mysql table
  var sql = someinsertstatement
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("records inserted");
     res.end();
  });
});

As mentioned, when I run the above code in my console, the console returns the JSON, though I am unsure how to assign this to a variable that I can then write into my mysql database.

Alternatively, if there is an easier way to write xml from a website directly to my mysql database, I would certainly appreciate any pointers. I feel like it should be easier than this, but I am new to pretty much all of it.

EDIT: Adding the JSON. I removed the line breaks to consolidate it. Trying to assign the result '4.68' to a variable.

data = {"site": {"observed": [{"datum": [{"valid": [{"_": "2019-02-21T19:42:00-00:00","$": {"timezone": "UTC"}}],"primary": [{"_": "4.68","$": {"name": "Stage","units": "ft"}}]}]}]}};

Thank you.

score 2 · Accepted Answer · answered Feb 22 '19 at 00:31

This worked on my end. Found that the main data you seek is site.observed.datum

const parser = require('xml2json');
const request = require("request");
var mysql = require('mysql');

var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "password",
    database: "mydb"
  });

var api_url = 'https://water.weather.gov/ahps2/hydrograph_to_xml.php?gage=deld1&output=xml';

function xmlToJson(url, callback){
    return request({
        method: 'GET',
        url: api_url,
    }, function (error, response, body) {
        if (error) {
           return callback({
               errorResponse: error,
               rowsToInsert: false
            });
        }else{
            let jsonRes = JSON.parse(parser.toJson(body));
            let datumResult = jsonRes.site.observed.datum;//I had to log Object.keys multple time to get the 
            const readyForDB = datumResult.map(x => {
                let timeOfReading = x.valid.$t;
                let stage = x.primary.$t;
                let flow = x.secondary.$t;
                return [
                    timeOfReading, stage, flow
                ]
            });
            return callback({
                errorResponse: false,
                rowsToInsert: readyForDB
            });
        }
    })
}
return xmlToJson(api_url, ({errorResponse, rowsToInsert}) => {
    if(errorResponse){
       throw callback.errorResponse;
    }
    return con.connect(function(err) {
        if (err) throw err;
        //below is where I might make an insert statement to insert my values into a mysql table
        var sql = "INSERT INTO forecast (timeOfReading, stage, flow) VALUES ?"
        con.query(sql, [rowsToInsert], function (err, result) {
        if (err) throw err;
            console.log(result.affectedRows + " rows inserted");
        });
    });
});

This worked flawlessly, thank you so much! Now I just need to know how to loop this for a whole bunch of xml links and add the latest reading for each to the database...but I will work on it for a while first. — cdwhiteiv, Feb 23 '19 at 02:16

score 0 · Answer 2 · answered Feb 21 '19 at 03:52

0

Sounds like you have the JSON you want but are unsure how to access data within it. Correct me if I'm wrong.

Lets say you have this JSON object called "test":

You can access the value of 1 by calling test.a, and similarly access the value of 2 by calling test.b.x

answered Feb 21 '19 at 03:52

Adam

219
1
2
11

Yes I believe that would be the first step in solving this. Then I would need to add that to the mysql database after assigning to a variable. Any idea how I would write that mysql insert statement? – cdwhiteiv Feb 21 '19 at 20:01
Ok after going down a rabbit hole involving lodash I still can't figure out how to retrieve any of the values from the JSON. It seems the JSON data is nested within 7ish arrays and I keep getting an error. I've added the JSON info above. I'm trying to pull the '4.68' and 'Stage' results into variables but can't figure out how. – cdwhiteiv Feb 21 '19 at 21:10

Write JSON to mysql database with node.js

2 Answers2