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

Question

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?

Charlie Tumahai · Accepted Answer · 2019-01-16T15:47:28.823

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.

If the column names and JSON names are different, then replace the slice with a map where the keys are the column names and the values are the JSON names:

fieldNames := map[string]string{
  "column1": "json1",
  ... and so on
}

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 dbName, jsonName := range fieldNames {
    if v, ok := j[jsonName]; ok {
        names = append(names, dbName)
        inserts = append(inserts, "?")
        values = append(values, v)
    }
}
statement := "insert into yourTable (" +
    strings.Join(names, ", ") +
    ") values (" + strings.Join(inserts, ", ") + ")"
err := db.Exec(statement, values...)

So, I still need to create a `fieldNames` that has the field names? and it can't be snake_case (cuz that's what they are)? Regardless of if I go this way, thanks for showing me some interesting Go that I wasn't aware of. — JonTroncoso, Jan 12 '19 at 01:11
The above code works with snake_case names. The only requirement is that the JSON property names match the table field names. Query the database to get the field names. See [Get table column names in MySQL?](https://stackoverflow.com/questions/1526688/get-table-column-names-in-mysql) for an example of the queries to use. — Charlie Tumahai, Jan 12 '19 at 01:21
Thanks, I expanded on your example to fit my needs. Specifically, I used a switch to handle different fields differently. I might post my code later, but I'd need to remove IP. Am I correct in understanding that this is not type safe? And that I need to use a struct for type safety? I might post a separate question on that — JonTroncoso, Jan 16 '19 at 07:58

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

1 Answers1