CODEWITHSUNDEEP
mysqljsondatabasegoweb
user8800020
user8800020

Reputation: 133

Reading Json Data from MySql in Golang?

I am facing a case where I had to store dynamic values in the database with key and value pairs given by the user itself.

User gives the key and value, and I interpret it as

{"key": "user Given", "value": "user Given"}

and I add all such things to an array, and I want this array to be read into Go code where the array of objects is read from the Database table.

Upvotes: 2

Views: 5868

Answers (2)

JediMind
JediMind

Reputation: 121

You can use the JSON Unmarshaler interface, but depending on how you are retrieving the data from MySql, will vary your implementation. But the idea is the same. For this example, I use https://github.com/go-sql-driver/mysql and assuming you want to store the data in an actual JSON field (Mysql >= 5.7), you could do something like this:

type MyField struct {
    Key string `json:"key"`
    Value string `json:"value"`
}

type MyModel struct {
    ID uint64
    MyFieldName MyField `json:"my_field_name"`
}


func (m *MyField) Scan(src interface{}) error {
    // The data stored in a JSON field is actually returned as []uint8
    val := src.([]uint8)
    return json.Unmarshal(val, &m)
}

func main() {
    db, err := sql.Open("mysql", "root:password@tcp(127.0.0.1)/dbname")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    results, err := db.Query("SELECT id, my_field_name FROM my_table")

    if err != nil {
        panic(err.Error())
    }

    for results.Next() {
        var row MyModel
        err := results.Scan(&row.ID, &row.MyFieldName)
        if err != nil {
            panic(err.Error())
        }
        fmt.Println(row.MyFieldName.Key)
    }
}

Upvotes: 5

Zanicar
Zanicar

Reputation: 100

A quick hack, not necessarily the most elegant approach, is to use Golang's default JSON Unmarshaler behavior for golang maps:

jstr := `{"key": "userKeyValue", "value": "userValueValue"}`

// declare a map that has a key string and value interface{} so that any values or
// types will be accepted;
jmap := make(map[string]interface{})

err := json.Unmarshal(jstr, &jmap)
if err != nil {
    log.Fatal(err)
}

for k, v := range jmap {
    fmt.Printf("Key: %v, Value: %v\n", k, v)
    // If you want to directly work with v remember it is an interface{}
    // Thus to use it as a string you must v.(string)
}
// Will output the following:
// Key: key, Value: userKeyValue
// Key: value, Value: userValueValue

You can now use standard golang map to manipulate or manage the received data... The more elegant approach is to implement the JSON Marshaler and Unmarshaler interfaces for your declared type. These are described in the golang documentation: https://golang.org/pkg/encoding/json/

Upvotes: -3

Related Questions