How do I convert a JSON object into a MySQL row?

61
January 13, 2019, at 02:50 AM

I'd like to take a JSON string representing an object from a 3rd party API and insert it into a MySQL table. The JSON object properties match the table fields 1-to-1. There are several hundred columns in this table/JSON object. And there will be a few dozen rows to insert at any time.

I'd rather not make a huge struct. But if I need to, then I'd rather not db.Prepare() an INSERT statement with several hundred "?"s. But if I have to then I'd rather not Have to write a stmt.Exec() with several hundred parameters.

Is there a good way to do this in golang? Or is it just going to be extremely inefficient?

Answer 1

Use the following given a slice of valid database field names fieldNames and JSON data data:

var j map[string]interface{}
if err := json.Unmarshal(data, &j); err != nil {
    // handle error
}
var names []string
var inserts []string
var values []interface{}
for _, n := range fieldNames {
    if v, ok := j[n]; ok {
        names = append(names, n)
        inserts = append(inserts, "?")
        values = append(values, v)
    }
}
statement := "insert into yourTable (" +
    strings.Join(names, ", ") +
    ") values (" + strings.Join(inserts, ", ") + ")"
err := db.Exec(statement, values...)

To avoid SQL injection attacks, it's important to work from a slice of known column names.

You can query the database to create the fieldNames slice. See Get table column names in MySQL? for the query required.

READ ALSO
How to substantially decrease mySQL query processing time?

How to substantially decrease mySQL query processing time?

I have a trouble that my query in mySQL is being processing about 8-10 minutes before report finally appearsI have created few indexes and time has fallen till 2-3 minutes

31
How to get multiple rows/columns matched from another table in mysql

How to get multiple rows/columns matched from another table in mysql

I have a records table and a history table that adds timestamps as records flow through our systemThey look something like this

24
Trouble with creating a table on jsp page having a column with blob images from MySQL

Trouble with creating a table on jsp page having a column with blob images from MySQL

I am trying to create a table with a column of text description of an image and a second column with blob images from MySQL

43
get hourly results for count in the last 12 hours - MySql

get hourly results for count in the last 12 hours - MySql

I have a big query that is supposed to return the count for rfid's for each product have an association with one order

31