How to convert *sql.Rows to typed JSON in Golang

huangapple
go评论187阅读模式
英文:

How to convert *sql.Rows to typed JSON in Golang

问题

基本上，我正在尝试在MySQL数据库上运行查询，将数据转换为JSON并发送回客户端。我尝试了几种方法，所有的“简单”方法都会将整个JSON作为字符串发送回来。我希望将其作为一个键（string）和[]float64值发送回来。这样我就有了与键关联的数据数组。此外，这需要有一个类型。到目前为止，我找到的最好的方法是将所有数据放入一个结构体中，对其进行编码，然后将其发送回ResponseWriter。

我看到有几个关于从数据库生成JSON的问题，但我没有找到任何使用结构体方法的内容。我将下面的代码写成一个单独的函数来说明我的问题。这个函数非常有限，它只能处理两个字段，并且必须是float64类型。

因此，我的问题是：如何在将此JSON从查询响应中创建之前，为其设置正确的类型，并且是否有一种动态的方法（即，可以接受可变数量的列和未知类型）？

  1. type DbDao struct{
  2.   db *sql.DB
  3. }
  5. type JSONData struct {
  6.   Values []float64
  7.   Dates []string
  8. }
  10. func (d *DbDao) SendJSON(sqlString string, w http.ResponseWriter) (error) {
  12.   stmt, err := d.db.Prepare(sqlString)
  13.   if err != nil {
  14.     return err
  15.   }
  16.   defer stmt.Close()
  18.   rows, err := stmt.Query()
  19.   if err != nil {
  20.     return err
  21.   }
  22.   defer rows.Close()
  24.   values := make([]interface{}, 2)
  25.   scanArgs := make([]interface{}, 2)
  26.   for i := range values {
  27.     scanArgs[i] = &values[i]
  28.   }
  30.   for rows.Next() {
  31.     err := rows.Scan(scanArgs...)
  32.     if err != nil {
  33.       return err
  34.     }
  36.     var tempDate string 
  37.     var tempValue float64
  38.     var myjson JSONData
  40.     d, dok := values[0].([]byte)
  41.     v, vok := values[1].(float64)
  43.     if dok {
  44.       tempDate = string(d)
  45.       if err != nil {
  46.         return err
  47.       }
  48.       myjson.Dates = append(myjson.Dates, tempDate)
  49.     }
  50.     
  51.     if vok {      
  52.       tempValue = v 
  53.       myjson.Values = append(myjson.Values, tempValue)
  54.       fmt.Println(v)
  55.       fmt.Println(tempValue)
  57.     }    
  58.     
  59.     err = json.NewEncoder(w).Encode(&myjson)
  60.     if err != nil {
  61.       return err
  62.     }
  63.   }
  65.   return nil 
  66. }

希望这能帮到你！

英文:

Essentially, I am trying to run a query on a MySQL database, get the data made converted into JSON and sent back to the client. I have tried several methods and all of the "easy" ones result in sending back all of the JSON as a string. I need this to be send back as a key (string) with []float64 value. This way I have an array of data associated with a key. Also, this needs to have a type. The best method I've found so far to accomplish this was to build put all of the data into a struct, encode it and send that back to the ResponseWriter.

I have seen several questions on making JSON from a database but I haven't found anything utilizing the struct method. I wrote the below code into a single function to illustrate my question. This is VERY limited in that it will only handle two fields and it MUST be a float64.

Therefore, my question is: How do I create this JSON from a query response that has the correct type before sending this back to the client and is there a way to do this dynamically (ie, can accept a variable number of columns and unknown types)?:

  1. { "Values":[12.54, 76.98, 34.90], "Dates": ["2017-02-03", "2017-02-04:, "2017-02-05"]}

<pre><code>

  1. type DbDao struct{
  2. db *sql.DB
  3. }
  4. type JSONData struct {
  5. Values []float64
  6. Dates []string
  7. }
  8. func (d *DbDao) SendJSON(sqlString string, w http.ResponseWriter) (error) {
  9. stmt, err := d.db.Prepare(sqlString)
  10. if err != nil {
  11. return err
  12. }
  13. defer stmt.Close()
  14. rows, err := stmt.Query()
  15. if err != nil {
  16. return err
  17. }
  18. defer rows.Close()
  19. values := make([]interface{}, 2)
  20. scanArgs := make([]interface{}, 2)
  21. for i := range values {
  22. scanArgs[i] = &amp;values[i]
  23. }
  24. for rows.Next() {
  25. err := rows.Scan(scanArgs...)
  26. if err != nil {
  27. return err
  28. }
  29. var tempDate string 
  30. var tempValue float64
  31. var myjson JSONData
  32. d, dok := values[0].([]byte)
  33. v, vok := values[1].(float64)
  34. if dok {
  35. tempDate = string(d)
  36. if err != nil {
  37. return err
  38. }
  39. myjson.Dates = append(myjson.Dates, tempDate)
  40. }
  41. if vok {      
  42. tempValue = v 
  43. myjson.Values = append(myjson.Values, tempValue)
  44. fmt.Println(v)
  45. fmt.Println(tempValue)
  46. }    
  47. err = json.NewEncoder(w).Encode(&amp;myjson)
  48. if err != nil {
  49. return err
  50. }
  51. }
  52. return nil 
  53. }

</code></pre>

答案1

得分: 15

这是我能想到的最好的实现方法，可以使其具有动态性。而且与我的原始代码相比，这个实现方法要短得多。由于我经常看到这种类型的问题，希望这对其他人有所帮助。我也愿意接受其他更好的实现这个功能的答案：

  1. func (d *DbDao) makeStructJSON(queryText string, w http.ResponseWriter) error {
  3.     // 返回 rows *sql.Rows
  4.     rows, err := d.db.Query(queryText)
  5.     if err != nil {
  6.         return err
  7.     }
  8.     columns, err := rows.Columns()
  9.     if err != nil {
  10.         return err
  11.     }
  13.     count := len(columns)
  14.     values := make([]interface{}, count)
  15.     scanArgs := make([]interface{}, count)
  16.     for i := range values {
  17.         scanArgs[i] = &values[i]
  18.     }
  20.     masterData := make(map[string][]interface{})
  22.     for rows.Next() {
  23.         err := rows.Scan(scanArgs...)
  24.         if err != nil {
  25.             return err
  26.         }
  27.         for i, v := range values {
  29.             x := v.([]byte)
  31.             // 注意：来自 Go 博客：JSON and GO - 2011年1月25日：
  32.             // json 包使用 map[string]interface{} 和 []interface{} 类型来存储任意的 JSON 对象和数组；它可以将任何有效的 JSON 数据解组为普通的 interface{} 值。默认的具体 Go 类型为：
  33.             //
  34.             // bool 用于 JSON 布尔值，
  35.             // float64 用于 JSON 数字，
  36.             // string 用于 JSON 字符串，
  37.             // nil 用于 JSON null。
  39.             if nx, ok := strconv.ParseFloat(string(x), 64); ok == nil {
  40.                 masterData[columns[i]] = append(masterData[columns[i]], nx)
  41.             } else if b, ok := strconv.ParseBool(string(x)); ok == nil {
  42.                 masterData[columns[i]] = append(masterData[columns[i]], b)
  43.             } else if "string" == fmt.Sprintf("%T", string(x)) {
  44.                 masterData[columns[i]] = append(masterData[columns[i]], string(x))
  45.             } else {
  46.                 fmt.Printf("Failed on if for type %T of %v\n", x, x)
  47.             }
  49.         }
  50.     }
  52.     w.Header().Set("Content-Type", "application/json")
  54.     err = json.NewEncoder(w).Encode(masterData)
  56.     if err != nil {
  57.         return err
  58.     }
  59.     return err
  60. }
英文:

This is the best implementation that I was able to come up with that would make it dynamic. It is also significantly shorter than my original. As I've seen this type of question quite a bit, I hope this helps others. I am open to other answers that have a better implementation of this:

  1. func (d *DbDao) makeStructJSON(queryText string, w http.ResponseWriter) error {
  2. // returns rows *sql.Rows
  3. rows, err := d.db.Query(queryText)
  4. if err != nil {
  5. return err
  6. }
  7. columns, err := rows.Columns()
  8. if err != nil {
  9. return err
  10. }
  11. count := len(columns)
  12. values := make([]interface{}, count)
  13. scanArgs := make([]interface{}, count)
  14. for i := range values {
  15. scanArgs[i] = &amp;values[i]
  16. }
  17. masterData := make(map[string][]interface{})
  18. for rows.Next() {
  19. err := rows.Scan(scanArgs...)
  20. if err != nil {
  21. return err
  22. }
  23. for i, v := range values {
  24. x := v.([]byte)
  25. //NOTE: FROM THE GO BLOG: JSON and GO - 25 Jan 2011:
  26. // The json package uses map[string]interface{} and []interface{} values to store arbitrary JSON objects and arrays; it will happily unmarshal any valid JSON blob into a plain interface{} value. The default concrete Go types are:
  27. //
  28. // bool for JSON booleans,
  29. // float64 for JSON numbers,
  30. // string for JSON strings, and
  31. // nil for JSON null.
  32. if nx, ok := strconv.ParseFloat(string(x), 64); ok == nil {
  33. masterData[columns[i]] = append(masterData[columns[i]], nx)
  34. } else if b, ok := strconv.ParseBool(string(x)); ok == nil {
  35. masterData[columns[i]] = append(masterData[columns[i]], b)
  36. } else if &quot;string&quot; == fmt.Sprintf(&quot;%T&quot;, string(x)) {
  37. masterData[columns[i]] = append(masterData[columns[i]], string(x))
  38. } else {
  39. fmt.Printf(&quot;Failed on if for type %T of %v\n&quot;, x, x)
  40. }
  41. }
  42. }
  43. w.Header().Set(&quot;Content-Type&quot;, &quot;application/json&quot;)
  44. err = json.NewEncoder(w).Encode(masterData)
  45. if err != nil {
  46. return err
  47. }
  48. return err
  49. }

答案2

得分: 12

这是一种更好的方法（在Postgres中进行了测试）。不需要使用反射/反射：

  1. columnTypes, err := rows.ColumnTypes()
  3. if err != nil {
  4.     return err
  5. }
  7. count := len(columnTypes)
  8. finalRows := []interface{}{}
  10. for rows.Next() {
  12.     scanArgs := make([]interface{}, count)
  14.     for i, v := range columnTypes {
  16.         switch v.DatabaseTypeName() {
  17.         case "VARCHAR", "TEXT", "UUID", "TIMESTAMP":
  18.             scanArgs[i] = new(sql.NullString)
  19.             break;
  20.         case "BOOL":
  21.             scanArgs[i] = new(sql.NullBool)
  22.             break;
  23.         case "INT4":
  24.             scanArgs[i] = new(sql.NullInt64)
  25.             break;
  26.         default:
  27.             scanArgs[i] = new(sql.NullString)
  28.         }
  29.     }
  31.     err := rows.Scan(scanArgs...)
  33.     if err != nil {
  34.         return err
  35.     }
  37.     masterData := map[string]interface{}{}
  39.     for i, v := range columnTypes {
  41.         if z, ok := (scanArgs[i]).(*sql.NullBool); ok  {
  42.             masterData[v.Name()] = z.Bool
  43.             continue;
  44.         }
  46.         if z, ok := (scanArgs[i]).(*sql.NullString); ok  {
  47.             masterData[v.Name()] = z.String
  48.             continue;
  49.         }
  51.         if z, ok := (scanArgs[i]).(*sql.NullInt64); ok  {
  52.             masterData[v.Name()] = z.Int64
  53.             continue;
  54.         }
  56.         if z, ok := (scanArgs[i]).(*sql.NullFloat64); ok  {
  57.             masterData[v.Name()] = z.Float64
  58.             continue;
  59.         }
  61.         if z, ok := (scanArgs[i]).(*sql.NullInt32); ok  {
  62.             masterData[v.Name()] = z.Int32
  63.             continue;
  64.         }
  66.         masterData[v.Name()] = scanArgs[i]
  67.     }
  69.     finalRows = append(finalRows, masterData)
  70. }
  73. z, err := json.Marshal(finalRows)
英文:

This is a much better way to do it (Tested with Postgres). No reflect/reflection needed:

  1.     columnTypes, err := rows.ColumnTypes()
  2. if err != nil {
  3. return err
  4. }
  5. count := len(columnTypes)
  6. finalRows := []interface{}{};
  7. for rows.Next() {
  8. scanArgs := make([]interface{}, count)
  9. for i, v := range columnTypes {
  10. switch v.DatabaseTypeName() {
  11. case &quot;VARCHAR&quot;, &quot;TEXT&quot;, &quot;UUID&quot;, &quot;TIMESTAMP&quot;:
  12. scanArgs[i] = new(sql.NullString)
  13. break;
  14. case &quot;BOOL&quot;:
  15. scanArgs[i] = new(sql.NullBool)
  16. break;
  17. case &quot;INT4&quot;:
  18. scanArgs[i] = new(sql.NullInt64)
  19. break;
  20. default:
  21. scanArgs[i] = new(sql.NullString)
  22. }
  23. }
  24. err := rows.Scan(scanArgs...)
  25. if err != nil {
  26. return err
  27. }
  28. masterData := map[string]interface{}{}
  29. for i, v := range columnTypes {
  30. if z, ok := (scanArgs[i]).(*sql.NullBool); ok  {
  31. masterData[v.Name()] = z.Bool
  32. continue;
  33. }
  34. if z, ok := (scanArgs[i]).(*sql.NullString); ok  {
  35. masterData[v.Name()] = z.String
  36. continue;
  37. }
  38. if z, ok := (scanArgs[i]).(*sql.NullInt64); ok  {
  39. masterData[v.Name()] = z.Int64
  40. continue;
  41. }
  42. if z, ok := (scanArgs[i]).(*sql.NullFloat64); ok  {
  43. masterData[v.Name()] = z.Float64
  44. continue;
  45. }
  46. if z, ok := (scanArgs[i]).(*sql.NullInt32); ok  {
  47. masterData[v.Name()] = z.Int32
  48. continue;
  49. }
  50. masterData[v.Name()] = scanArgs[i]
  51. }
  52. finalRows = append(finalRows, masterData)
  53. }
  54. z, err := json.Marshal(finalRows)

答案3

得分: 10

以下是一个更好的解决方案，使用反射来处理。它可以正确处理类型（例如，字符串值true不会错误地转换为布尔类型等）。

它还可以处理可能为空的类型（仅在MySQL中进行了测试-您可能需要根据其他驱动程序进行修改）。

  1. package main
  3. import (
  4. 	"database/sql"
  5. 	"encoding/json"
  6. 	"fmt"
  7. 	"reflect"
  9. 	"github.com/go-sql-driver/mysql"
  10. )
  12. // MySQL驱动程序返回的附加扫描类型。我没有查看PostgreSQL的情况。
  14. type jsonNullInt64 struct {
  15. 	sql.NullInt64
  16. }
  18. func (v jsonNullInt64) MarshalJSON() ([]byte, error) {
  19. 	if !v.Valid {
  20. 		return json.Marshal(nil)
  21. 	}
  22. 	return json.Marshal(v.Int64)
  23. }
  25. type jsonNullFloat64 struct {
  26. 	sql.NullFloat64
  27. }
  29. func (v jsonNullFloat64) MarshalJSON() ([]byte, error) {
  30. 	if !v.Valid {
  31. 		return json.Marshal(nil)
  32. 	}
  33. 	return json.Marshal(v.Float64)
  34. }
  36. type jsonNullTime struct {
  37. 	mysql.NullTime
  38. }
  40. func (v jsonNullTime) MarshalJSON() ([]byte, error) {
  41. 	if !v.Valid {
  42. 		return json.Marshal(nil)
  43. 	}
  44. 	return json.Marshal(v.Time)
  45. }
  47. var jsonNullInt64Type = reflect.TypeOf(jsonNullInt64{})
  48. var jsonNullFloat64Type = reflect.TypeOf(jsonNullFloat64{})
  49. var jsonNullTimeType = reflect.TypeOf(jsonNullTime{})
  50. var nullInt64Type = reflect.TypeOf(sql.NullInt64{})
  51. var nullFloat64Type = reflect.TypeOf(sql.NullFloat64{})
  52. var nullTimeType = reflect.TypeOf(mysql.NullTime{})
  54. // SQLToJSON将SQL结果转换为漂亮的JSON格式。它还可以很好地处理可能为空的值。参见https://stackoverflow.com/a/52572145/265521
  55. func SQLToJSON(rows *sql.Rows) ([]byte, error) {
  56. 	columns, err := rows.Columns()
  57. 	if err != nil {
  58. 		return nil, fmt.Errorf("Column error: %v", err)
  59. 	}
  61. 	tt, err := rows.ColumnTypes()
  62. 	if err != nil {
  63. 		return nil, fmt.Errorf("Column type error: %v", err)
  64. 	}
  66. 	types := make([]reflect.Type, len(tt))
  67. 	for i, tp := range tt {
  68. 		st := tp.ScanType()
  69. 		if st == nil {
  70. 			return nil, fmt.Errorf("Scantype is null for column: %v", err)
  71. 		}
  72. 		switch st {
  73. 		case nullInt64Type:
  74. 			types[i] = jsonNullInt64Type
  75. 		case nullFloat64Type:
  76. 			types[i] = jsonNullFloat64Type
  77. 		case nullTimeType:
  78. 			types[i] = jsonNullTimeType
  79. 		default:
  80. 			types[i] = st
  81. 		}
  82. 	}
  84. 	values := make([]interface{}, len(tt))
  85. 	data := make(map[string][]interface{})
  87. 	for rows.Next() {
  88. 		for i := range values {
  89. 			values[i] = reflect.New(types[i]).Interface()
  90. 		}
  91. 		err = rows.Scan(values...)
  92. 		if err != nil {
  93. 			return nil, fmt.Errorf("Failed to scan values: %v", err)
  94. 		}
  95. 		for i, v := range values {
  96. 			data[columns[i]] = append(data[columns[i]], v)
  97. 		}
  98. 	}
  100. 	return json.Marshal(data)
  101. }

希望对你有帮助！

英文:

Here is a better solution, using reflection. It handles types correctly (e.g. a string value of true won't erroneously be turned into a bool and so on.

It also handles possibly-null types (only tested with MySQL - you will probably need to modify it for other drivers).

  1. package main
  2. import (
  3. &quot;database/sql&quot;
  4. &quot;encoding/json&quot;
  5. &quot;fmt&quot;
  6. &quot;reflect&quot;
  7. &quot;github.com/go-sql-driver/mysql&quot;
  8. )
  9. // Additional scan types returned by the MySQL driver. I haven&#39;t looked at
  10. // what PostgreSQL does.
  11. type jsonNullInt64 struct {
  12. sql.NullInt64
  13. }
  14. func (v jsonNullInt64) MarshalJSON() ([]byte, error) {
  15. if !v.Valid {
  16. return json.Marshal(nil)
  17. }
  18. return json.Marshal(v.Int64)
  19. }
  20. type jsonNullFloat64 struct {
  21. sql.NullFloat64
  22. }
  23. func (v jsonNullFloat64) MarshalJSON() ([]byte, error) {
  24. if !v.Valid {
  25. return json.Marshal(nil)
  26. }
  27. return json.Marshal(v.Float64)
  28. }
  29. type jsonNullTime struct {
  30. mysql.NullTime
  31. }
  32. func (v jsonNullTime) MarshalJSON() ([]byte, error) {
  33. if !v.Valid {
  34. return json.Marshal(nil)
  35. }
  36. return json.Marshal(v.Time)
  37. }
  38. var jsonNullInt64Type = reflect.TypeOf(jsonNullInt64{})
  39. var jsonNullFloat64Type = reflect.TypeOf(jsonNullFloat64{})
  40. var jsonNullTimeType = reflect.TypeOf(jsonNullTime{})
  41. var nullInt64Type = reflect.TypeOf(sql.NullInt64{})
  42. var nullFloat64Type = reflect.TypeOf(sql.NullFloat64{})
  43. var nullTimeType = reflect.TypeOf(mysql.NullTime{})
  44. // SQLToJSON takes an SQL result and converts it to a nice JSON form. It also
  45. // handles possibly-null values nicely. See https://stackoverflow.com/a/52572145/265521
  46. func SQLToJSON(rows *sql.Rows) ([]byte, error) {
  47. columns, err := rows.Columns()
  48. if err != nil {
  49. return nil, fmt.Errorf(&quot;Column error: %v&quot;, err)
  50. }
  51. tt, err := rows.ColumnTypes()
  52. if err != nil {
  53. return nil, fmt.Errorf(&quot;Column type error: %v&quot;, err)
  54. }
  55. types := make([]reflect.Type, len(tt))
  56. for i, tp := range tt {
  57. st := tp.ScanType()
  58. if st == nil {
  59. return nil, fmt.Errorf(&quot;Scantype is null for column: %v&quot;, err)
  60. }
  61. switch st {
  62. case nullInt64Type:
  63. types[i] = jsonNullInt64Type
  64. case nullFloat64Type:
  65. types[i] = jsonNullFloat64Type
  66. case nullTimeType:
  67. types[i] = jsonNullTimeType
  68. default:
  69. types[i] = st
  70. }
  71. }
  72. values := make([]interface{}, len(tt))
  73. data := make(map[string][]interface{})
  74. for rows.Next() {
  75. for i := range values {
  76. values[i] = reflect.New(types[i]).Interface()
  77. }
  78. err = rows.Scan(values...)
  79. if err != nil {
  80. return nil, fmt.Errorf(&quot;Failed to scan values: %v&quot;, err)
  81. }
  82. for i, v := range values {
  83. data[columns[i]] = append(data[columns[i]], v)
  84. }
  85. }
  86. return json.Marshal(data)
  87. }

答案4

得分: 0

我认为你最好的选择是使用Golang中的json库。

  1. import "encoding/json"
  3. type JSONData struct {
  4.   Values []float64 `json:"Values"`
  5.   Dates []string `json:"Dates"`
  6. }

我认为没有一种好的动态方法来实现这个，因为Golang没有办法将数据库列名和输出的JSON匹配起来。另外，我通常通过直接将类型发送到数据库库来编写数据库查询代码。

  1. var tempDate string 
  2. var tempValue float64
  3. err := rows.Scan(&tempDate, &tempValue)
  4. if err != nil {
  5.        return err
  6. }

如果你真的想自动完成这个操作，你可以研究一下Golang的代码生成。

英文:

I think the best option you have is to use json library from golang

  1. import &quot;encoding/json&quot;
  2. type JSONData struct {
  3. Values []float64 `json:&quot;Values&quot;`
  4. Dates []string `json:&quot;Dates&quot;`
  5. }

I don't think there is a good way to do this dynamically, since golang has no way of matching up the database column name and the output'd json Also as a side note I usually write the db querying code by sending the type directly to the db library

  1. var tempDate string 
  2. var tempValue float64
  3. err := rows.Scan(&amp;tempDate, &amp;tempValue)
  4. if err != nil {
  5. return err
  6. }

If you really want to do this automatically you can look into golang code generation.

huangapple
  • 本文由 发表于 2017年3月14日 06:19:26
  • 转载请务必保留本文链接：https://go.coder-hub.com/42774467.html
  • go
  • json
  • sql
  • struct
  • web-applications
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定