Santiago Bernal Santiago Bernal - 1 year ago 142
JSON Question

Insert json to mysql row in nodejs

I'm kinda new in Node.js and Im currently developing a program that connects to various external APIs for transactions.

The APIs returns a JSON that I want to store completely in a MySQL database column. I dont want to create a column for every data returned as that means I'll have to create different rows for every different JSON response, and thats not scalable.

I also want to keep them stored as JSON as they would be easy to process in a query and in code. So in my database I have a table where I store the external transaction info and an

column where I want to store the JSON Object.ยด

My code

connection.acquire(function(err, con) {
console.log('inserting: ' + [transactionId, externalReference,
externalTransactionStatus, externalExtra]);
con.query('insert into external_transaction (transaction, external_reference, status,' +
'external_extra) ' +
'values (?,?,?,?)', [transactionId, externalReference,
externalTransactionStatus, externalExtra],
function(err, result) {
if(err) console.log(err);

is the JSON received from the API. Running that code gives me an error as it uses the JSON to add more fields to the insert instead of inserting the json. Example: if the json has a structure like:

type: 'internal_transaction',
address: 'rDLwhx2M9YfbNfZWDq5opJMttQjh7xtvh5',
sequence: 42,
id: 98,
specification: 'created with rrDL55JshyMtlIU sequence'

I get the error: Error:

ER_BAD_FIELD_ERROR: Unknown column 'type' in 'field list' as it takes 'type' as a column for the table.

Any help on this would be appreciated.

Answer Source

I don't know much about mySql, but had a similar error using a postgresSql database, this seems to be some kind of parsing error, why not try to parse the json object to string doing JSON.stringify(externalExtra)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download