0
I am creating a function to return the data of a query that involves SELECT
, however there is a column that stores a JSON, and in return comes a number structure, someone could explain to me what exactly this structure is about and how I can convert it to a readable format?
That’s my job so far:
func query(sqlQuery string) {
psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
"password=%s dbname=%s sslmode=disable",
host, port, user, password, dbname)
// Validate database params
db, err := sql.Open(driver, psqlInfo)
// If returned any error
if err != nil {
panic(err)
}
// Validate connection with database
err = db.Ping()
if err != nil {
panic(err)
}
// Execute query
rows, err := db.Query(sqlQuery)
// Get all name of columns returned of query
columns, _ := rows.Columns()
// Save number of columns returned of query
countColumns := len(columns)
var allRows []interface{}
for rows.Next() {
values := make([]interface{}, countColumns)
valuesPointers := make([]interface{}, countColumns)
for i := range columns {
valuesPointers[i] = &values[i]
}
if err := rows.Scan(valuesPointers...); err != nil {
log.Fatal(err)
}
allRows = append(allRows, values)
}
fmt.Println(allRows)
}
This is the return of one of the lines for example:
[871 AA 333 AA 3333 MERCEDES BENZ AGUM TESTETESTE 873 270 true 1980-01-01 00:00:00 +0000 +0000 4X2 Paraná [115 49 48] [123 34 116 114 117 99 107 34 58 32 110 117 108 108 44 32 34 116 114 97 105 108 101 114 34 58 32 123 34 100 97 116 97 34 58 32 123 34 99 97 112 97 99 105 116 121 34 58 32 34 54 48 48 34 44 32 34 103 114 111 117 112 105 110 103 34 58 32 34 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 97 34 44 32 34 105 115 84 114 97 105 108 101 114 34 58 32 34 111 110 34 44 32 34 99 108 97 115 115 105 102 105 99 97 116 105 111 110 34 58 32 34 98 98 98 98 98 98 98 98 98 98 98 98 98 98 34 125 44 32 34 112 114 111 102 105 108 101 34 58 32 123 34 107 109 112 114 105 99 101 34 58 32 110 117 108 108 44 32 34 103 112 115 111 114 99 97 110 34 58 32 34 103 112 115 34 44 32 34 109 97 120 115 112 101 101 100 34 58 32 110 117 108 108 44 32 34 109 97 120 97 99 99 101 108 101 114 97 116 105 111 110 34 58 32 110 117 108 108 44 32 34 109 97 120 116 105 109 101 105 100 108 101 115 116 111 112 34 58 32 110 117 108 108 44 32 34 105 110 105 116 111 100 111 109 101 116 101 114 100 97 116 101 34 58 32 102 97 108 115 101 44 32 34 105 110 105 116 111 100 111 109 101 116 101 114 118 97 108 117 101 34 58 32 110 117 108 108 125 125 44 32 34 118 101 104 105 99 108 101 34 58 32 123 34 105 100 34 58 32 34 34 44 32 34 110 97 109 101 34 58 32 34 65 65 32 51 51 51 34 44 32 34 98 114 97 110 100 34 58 32 34 77 69 82 67 69 68 69 83 32 66 69 78 90 34 44 32 34 99 108 97 115 115 34 58 32 34 52 88 50 34 44 32 34 109 111 100 101 108 34 58 32 34 65 71 85 77 34 44 32 34 112 108 97 116 101 34 58 32 34 65 65 32 51 51 51 51 34 44 32 34 114 101 103 105 111 110 34 58 32 34 80 97 114 97 110 195 161 34 44 32 34 99 111 109 109 101 110 116 115 34 58 32 34 34 44 32 34 102 117 101 108 116 121 112 101 34 58 32 34 115 49 48 34 44 32 34 111 112 101 114 97 116 105 111 110 34 58 32 34 84 69 83 84 69 84 69 83 84 69 34 44 32 34 99 117 115 116 111 109 101 114 105 100 34 58 32 34 50 55 48 34 125 125]]
The last position with several numbers is what in my table is a column in JSON format.
Hmmm, I’m going to test it here. Just to confirm, then JSON is treated as a byte structure, right? .
– Vinicius Gabriel
Several things are treated as
[]byte
, especially when using the[]interface{}
, unless mistake this also varies if you are using thedb.Prepare()
ordb.Query()
, since Mysql itself (deducing that you are using Mysql) has different protocols, which may or may not inform the type of data. It is always best to "inform" the type in advance, and itselfdatabase/sql
will do the conversion when possible (or will issue an error). You can use thereflect
to create "generic query", so the function gets a&struct
as[]interface
and popula her, but this is another matter– Inkeliz
Got it. First thank you, already cleared me some doubts that were hitting me. I’ll take a look at the reflect you mentioned. Well considering that one of the problems is that I’m using [ ]interface{}, in this structure would I have another way to scan without using []interface{}? I used this model because since the function can perform "any query", I will never know exactly which columns are being worked.
– Vinicius Gabriel
As for your response suggestion, I could not apply because there is no way to use Len() in an interface{} at first, I would have to perform some conversion to be able to apply this idea?
– Vinicius Gabriel
Yeah, apparently I’d have to wear something like
allRows[0][len(allRows[0].([]interface{}) - 1)].([]interface{})
, but only testing. : P– Inkeliz