Formatting numeric sql > JSON

Getting Help
1

I have a problem with formatting when using sqlx

rows, err = db.Queryx(query)
for rows.Next() {
    results := make(map[string]interface{})
    err = rows.MapScan(results)
    fmt.Fprintf(w,"%#v \n", results)
}

The output when using %#v

fmt.Fprintf(w,“%#v \n”, results)

map[string]interface {}{"USER_ID”:”JD”, "USER_NAME”:”John Doe”}

map[string]interface {}{"USER_ID”:”JAD”, "USER_NAME”:”Jane Doe”}

map[string]interface {}{"USER_ID”:”DD”, "USER_NAME”:”Donald Duck”}

Using only %v

fmt.Fprintf(w,“%v \n”, results)

map[USER_ID:JD USER_NAME:John Doe]

map[USER_ID:JAD USER_NAME:Jane Doe]

map[USER_ID:DD USER_NAME:Donald Duck]

The desired output is to get rid of map[string]interface {}

{"USER_ID”:”JD”, "USER_NAME”:”John Doe”}

{"USER_ID”:”JAD”, "USER_NAME”:”Jane Doe”}

{"USER_ID”:”DD”, "USER_NAME”:”Donald Duck”}

Is this possible using sqlx?

2

MapScan scans a single Row into the dest map[string]interface{} (https://godoc.org/github.com/jmoiron/sqlx#MapScan) and this is a standard way for printing maps in go.
You can prettify it with json.MarshalIndent, e.g.

package main

import (
	"encoding/json"
	"fmt"
)

func main() {
	m := map[string]interface{}{"USER_ID": "JD", "USER_NAME": "John Doe"}
	fmt.Println(m)
	b, err := json.MarshalIndent(m, "", "  ")
	if err != nil {
		fmt.Println("error:", err)
	}
	fmt.Print(string(b))
}

https://goplay.space/#JDDDiGOwu3a
and get something like this

{
  "USER_ID": "JD",
  "USER_NAME": "John Doe"
}
3

This is a good answer to my question.

But, the problem is bigger than my original question. When fetching data from Postgresql using make(map[string]interface{}), the numeric columns are treated as Base64 and are becoming unreadable to humans.

It seems that the cause is how Golang databas/sql package OR Golang postgresql drivers presents the numeric type (now []uint8) to JSON.

Just now I am following and investigating this tread

4

If you know the table name (and you must know it to build a query) you can use infoschema for the table columns https://www.postgresql.org/docs/current/static/infoschema-columns.html. There you can find useful information for the column types, it gives you a chance to cast your binary data to the right type.
This is a tedious exercise, I’d rather use strict type definitions or 3rd party libraries for type casting.

1 Like
5

Thank you for the link. Could be very useful.

“I’d rather use strict type definitions…”. How can I do this with an unknown table?

“…or 3rd party libraries for type casting…”- Any clue?

6

The tables are unknown only until you get them the first time)) Someone has created the tables in the db, you have to copy those definitions into your code, just kidding)) Sqlboiler can generate everything for you https://github.com/volatiletech/sqlboiler
Perhaps, some of these repos can help as well https://github.com/mailru/dbr https://github.com/jmoiron/modl
some similar stack answers
https://stackoverflow.com/questions/17845619/how-to-call-the-scan-variadic-function-in-golang-using-reflection/17885636#17885636
https://stackoverflow.com/questions/42774467/how-to-convert-sql-rows-to-typed-json-in-golang

1 Like
7

This should (theroetically) work resulting in integer and numeric as text. Of course it does not work, so I wonder if there is any clue what I am doing wrong?

  var rows *sql.Rows 
  rows, err = db.Query(query)
  object := map[string]interface{}{}
  columns, err := rows.Columns()
  values := make([]interface{}, len(columns))

  for rows.Next() {
    err = rows.Scan(values...)
    for i, column := range columns {
      object[column.Name()] = new(*string)
      values[i] = object[column.Name()]
    }
    data, _ := json.Marshal(object)
    fmt.Fprintf(w,"%s\n", data)
  }

getting this error:

column.Name undefined (type string has no field or method Name)

What am I doing wrong?

8

rows.Columns() returns a slice of strings []string, you shuld use just column, not column.Name()

1 Like
9

Yes, that error went away :slight_smile: but no values are shown

  var rows *sql.Rows 
  rows, err = db.Query(query)
  object := map[string]interface{}{}
  columns, _ := rows.Columns()
  values := make([]interface{}, len(columns))

  for rows.Next() {
    err = rows.Scan(values...)
    for i, column := range columns {
      object[column] = new(*string)
      values[i] = object[column]
    }
    data, _ := json.Marshal(object)
    fmt.Fprintf(w,"%s\n", data)
  }

The result of localhost:8080/getuser

{“DATE”:null,“QTY”:null,“ID”:null,“USER_NAME”:null}
{“DATE”:null,“QTY”:null,“ID”:null,“USER_NAME”:null}

What ist wrong now?

10

please, learn how to use rows.Scan properly https://golang.org/pkg/database/sql/#Rows , I have this example

	cols := make([]interface{}, len(colNames))
	colPtrs := make([]interface{}, len(colNames))
	for i := 0; i < len(colNames); i++ {
		colPtrs[i] = &cols[i]
	}

	err = rows.Scan(colPtrs...)
11

RTFM is of high value when you know exact what you are looking for. As there is no working sql examples (what I have found) in Go playground, so it is hard to test and learning by doing. So it is sort of catch 22.

12

what are you talking about? I gave you 2 examples, rows.Scan accepts an array of pointers, not values

13

Yes, thank you. But I cannot figure out the context to use them.

14

You where correct. But by chance I put your code in a context. And it is almost working!

  var rows *sql.Rows
  rows, err = db.Query(query)
  cols, _ := rows.Columns()
  vals := make([]interface{}, len(cols))
  for i, _ := range cols {
     vals[i] = &cols[i]
  }
  for rows.Next() {
     err = rows.Scan(vals...)
     json, _ := json.Marshal(vals)
     fmt.Fprintf(w,"%s\n", json)
  }

[“1”,“John Doe”,“6.00”]

But how do I get the column names back?

[“ID”:“1”,“NAME”:“John Doe”,“QTY”:“6.00”]

15

you need a map before marshalling to json

var rows *sql.Rows
rows, err = db.Query(query)
cols, _ := rows.Columns()
colNames, _ := rows.Columns()
vals := make([]interface{}, len(cols))
for i, _ := range cols {
	vals[i] = &cols[i]
}
for rows.Next() {
	var myMap = make(map[string]interface{})
	err = rows.Scan(vals...)
	for i, v := range vals {
		myMap[colNames[i]] = v
	}
	json, _ := json.Marshal(myMap)
	fmt.Fprintf(w,"%s\n", json)
}

added colNames, _ := rows.Columns(), myMap and a for loop

16

I must have done something wrong, I get double values:

[“1”:“1”,“John Doe”:“John Doe”,“6.00”:“6.00”]

17

Just an suggestion. If you don’t have a very strong reason to put the results in map try to avoid this. Maps are dynamic structures and you must protect them with mutexes otherwise in concurent environment you will get panics. In terms of sql queries this will unnecessarily slow down the operation for a while. Instead use scan and structs.

18

I have been warned to do this. But typing thousand of structs manually may cause even more panic :slight_smile:

19

Moving the column name loop outside the row loop did the trick. Thank You!

  var rows *sql.Rows
  rows, err = db.Query(query)
  cols, _ := rows.Columns()
  colnames, _ := rows.Columns()
  vals := make([]interface{}, len(cols))

  for i, _ := range cols {
     vals[i] = &cols[i]
  }

  mymap := make(map[string]interface{})
 
  for i, val := range vals {
    mymap[colnames[i]] = val
  }
   
  for rows.Next() {
     err = rows.Scan(vals...)
     json, _ := json.Marshal(mymap)
     fmt.Fprintf(w,"%s\n",json)
  }
20

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.