Trying to send query output to browser as JSON, but numbers are treated as Base64. Integers prints out correct.
var rows *sqlx.Rows
enc := json.NewEncoder(w)
rows, err = db.Queryx(query)
for rows.Next() {
results := make(map[string]interface{})
err = rows.MapScan(results)
if err := enc.Encode(results); err != nil{
fmt.Fprintf(w,"%s
", results)
}
}
The result from JSON is (id integer, qty numeric / float):
{"ID":1,"QTY":"OC4wMA=="}
{"ID":2,"QTY":"OC4wMA=="}
Without JSON encoding, the numeric column is treated correct as numbers.
EDIT
@mkopriva hope this answer helps:
"if you provide the table definition so we can see how the column is defined, it would also help to see the SQL query so we can see how you're pulling the column from the db"
"ID" int4
"QTY" numeric
SELECT * FROM table
and it would also help if you provide the code and its output which you mentioned as "treating the columns correctly as numbers"
var rows *sqlx.Rows
rows, err = db.Queryx(query)
for rows.Next() {
results := make(map[string]interface{})
err = rows.MapScan(results)
fmt.Fprintf(w,"%s
", results)
}
Gives this result:
map[ID:1 QTY:9.75]
map[ID:2 QTY:7.00]
"can you do fmt.Printf("%T", results["QTY"]) for us? It is highly doubtful that if "QTY" is truly an int or float that the json encoder would marshal it as a base64 string.
without JSON this gives:
[]uint8 []uint8
After countless attempts, I found a solution that solves the problem by converting numbers to strings. Not ideal, but rather simple.
var rows *sql.Rows
rows, err = db.Query(query)
cols, _ := rows.Columns()
colnames, _ := rows.Columns()
vals := make([]interface{}, len(cols))
for i, _ := range cols { //bytes to string
vals[i] = &cols[i]
}
mymap := make(map[string]interface{})
for i, v := range vals { //adding column names
mymap[colnames[i]] = v
}
for rows.Next() {
err = rows.Scan(vals...)
json, _ := json.Marshal(mymap)
fmt.Fprintf(w,"%s
",json)
}
Feel free to downvote, but please do me the honour to explain why.
Assuming you're using lib/pq, the QTY value is being converted into a byte slice which is actually the correct type for the corresponding postgres numeric type. This is because a value of type numeric cannot be reliably fit into a float64 without loosing precision and so it makes sense for the driver to choose to convert a postgres numeric value into a Go []byte value.
See here: https://github.com/lib/pq/issues/648#issuecomment-322674708
You can, however, explicitly tell the driver that you need a float by passing a Go value of type *float64 to the Scan family of methods and the driver will convert the numeric into a float64, losing precission in the process. But if you pass an interface{}, the driver will make the most appropriate choice and convert the value to a byte slice.
If you don't care about precision but you must to use interface{} as the arguemnt to Scan, you can change the table column's type from numeric to float, if you do that then the driver can safely make the choice to convert your column into a Go float64.
If you care about precision, but for some reason you have to use a map of interfaces, you can write a simple function that converts a known key's value in the map from a []byte to a string, after that, when you pass the updated map to the json encoder you'll see that key's value being encoded as a normal string instead of base64.
func bytesToString(k string, m map[string]interface{}) {
if b, ok := m[k].([]byte); ok {
m[k] = string(b)
}
}