Rows to map to JSON using sqlx package

Question

Trying to get the result into a JSON string, I have to use MapScan because i have no structs that represent the data so here is what i did

import (
    "fmt"
    "log"
    "encoding/json"
    _ "github.com/jmoiron/sqlx"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err := sqlx.Connect("mysql", "uname:pwd@/db")
    if err != nil {
        log.Fatal(err)
    }

    m := map[string]interface{}{}

    //Go through rows
    rows, err := db.Queryx("SELECT id,cname FROM items")
    for rows.Next() {
        err := rows.MapScan(m)
        if err != nil {
            log.Fatal(err)
        }
    }

    //Marshal the map
    b, _ := json.Marshal(m)

    //Prints the resulted json
    fmt.Printf("Marshalled data: %s\n", b)
}

The output is Marshalled data: {"cname":"c29tZWl0ZW0","id":"MA=="}

and it should be Marshalled data: {"cname":"someitem","id":0}

and I am not sure how to go around this since the values returned in base64 encodig, any ideas?

Why don't you create a struct with the id and cname properties ? This would be much cleaner (and more efficient) than using a map. — Denys Séguret, Jul 15 '14 at 08:06
@dystroy because i dont know how the data looks like, i need to issue simple queries from the client, and there is no logic involved at all — FPGA, Jul 15 '14 at 08:09
@FPGA If it's an SQL database without a schema, that's an odd database. Note that you don't have to fill every field in the struct - you can create a struct that represents all of your columns and just populate the ones returned from a query (use struct tags). — elithrar, Jul 15 '14 at 08:28
@elithrar there are cases where you cant model the data because you have no idea how it looks like, an example is an sql table viewer on the client — FPGA, Jul 15 '14 at 08:32
This was confusing me at first. The base64 encoding happens when the JSON is marshaled. This has nothing to do with sqlx. Also the example above only works for one row results. This will otherwise only ever print the last result. — Michael Thessel, Jun 30 '17 at 18:49

score 4 · Accepted Answer · answered Jul 15 '14 at 08:01

4

Just iterate over your map and decode the base64 strings prior to marshal the map:

for k, encoded := range m {
    decoded, err := base64.StdEncoding.DecodeString(encoded)
    if err != nil {
            log.Fatal("error:", err)
    }
    m[k] = decoded
}

b, _ := json.Marshal(m)

You must add this to your imports : "encoding/base64".

answered Jul 15 '14 at 08:01

Denys Séguret

372,613
87
782
758

2

Yes you can, just don't use sqlx but standard db functions. But as it's really not clear why you use a map here, it's hard to give you the code. – Denys Séguret Jul 15 '14 at 08:05
i use a map because i simply want to issue queries from the browser to view my sql tables, there is not logic involved so i dont want to use structs – FPGA Jul 15 '14 at 08:07
this works but it's very confusing to me why the default is to base64 encode values from the db – william Aug 14 '14 at 21:49

Rows to map to JSON using sqlx package

1 Answers1