Return of JSON column from the bank in Golang

Asked

Viewed 118 times

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.

1 answer

1


This looks like a []byte, not "several numbers any". It is shown as "several numbers" because the []byte is a []uint8. A []byte can use directly on json.Unmarshal.

Maybe, if you specify the type (since the database has defined type, it makes no sense to use interface{}), you might be able to use string or specify how []byte. You can simply create a []byte, maybe even inside a struct with all data, and specify direct on Scan, instead of using the interface{}.

How he’s like a interface{}, you may have to make a assertion (a mere .([]byte)), try changing the fmt.Println(allRows) for:

ultimoJSON := allRows[0][len(allRows[0] - 1)] // Deduzindo que este seja os "vários números"
fmt.Println(string(ultimoJSON.([]byte)))
  • Hmmm, I’m going to test it here. Just to confirm, then JSON is treated as a byte structure, right? .

  • 1

    Several things are treated as []byte, especially when using the []interface{}, unless mistake this also varies if you are using the db.Prepare() or db.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 itself database/sql will do the conversion when possible (or will issue an error). You can use the reflect to create "generic query", so the function gets a &struct as []interface and popula her, but this is another matter

  • 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.

  • 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?

  • 1

    Yeah, apparently I’d have to wear something like allRows[0][len(allRows[0].([]interface{}) - 1)].([]interface{}) , but only testing. : P

Browser other questions tagged

You are not signed in. Login or sign up in order to post.