英文:
Retrieve JSON column data from MySQL using go
问题
我正在尝试使用MySQL、go和sqlx从列中检索JSON数据。我在MySQL表的一个列中有以下JSON结构。
[{"name": "Abc", "ids": [108, 33, 34, 35]}]
我使用以下代码来检索数据：
sqlstr := `SELECT field1, field2, data FROM myTable`
records := []DataStruct{}
var err = *new(error)
err = store.DB().Select(&records, sqlstr)
if err != nil {
return nil, err
}
for _, entry := range records {
mainResponse := MainResponse{}
mainResponse = MainResponse {
Field1: entry.Field1,
Field2: entry.Field2,
Data: entry.Data,
}
}
我的结构如下：
type MainResponse struct {
Data []Data `db:"data"`
Field1 string `db:"field1"`
Field2 string `db:"field2"`
}
type DataStruct struct {
Data []Data `db:"data"`
Field1 string `db:"field1"`
Field2 string `db:"field2"`
}
type Data struct {
Name store.NullString `db:"name"`
Ids []int `db:"ids"`
}
当运行选择查询时，我在err变量中得到以下错误：
sql: 在列索引0上扫描错误，名称"data"：不支持的扫描，将driver.Value类型[]uint8存储到类型*[]test.Data中。
非常感谢您的帮助。
英文:
I am trying to retrieve JSON data from a column using MySQL and go and sqlx. I have the below JSON structure in a MySQL table column named data.
[{"name": "Abc", "ids": [108, 33, 34, 35]}]
I do the following to retrieve the data
sqlstr := `SELECT field1, field2, data FROM myTable`
records := []DataStruct{}
var err = *new(error)
err = store.DB().Select(&records, sqlstr)
if err != nil {
return nil, err
}
for _, entry := range records {
mainResponse := MainResponse{}
mainResponse = MainResponse {
Field1: entry.Field1,
Field2: entry.Field2,
Data: entry.Data,
}
My structures are as below
type MainResponse struct {
Data []Data `db:"data"`
Field1 string `db:"field1"`
Field2 string `db:"field2"`
}
type DataStruct struct {
Data []Data `db:"data"`
Field1 string `db:"field1"`
Field2 string `db:"field2"`
}
type Data struct {
Name store.NullString `db:"name"`
Ids []int `db:"ids"`
}
I get the below error in err variable when the select query runs
sql: Scan error on column index 0, name "data": unsupported Scan, storing driver.Value type []uint8 into type *[]test.Data
Any help is really appreciated
答案1
得分: 1
你可以实现Scanner接口。
type DataStruct struct {
Data DataList `db:"data"`
Field1 string `db:"field1"`
Field2 string `db:"field2"`
}
type DataList []Data
func (ls *DataList) Scan(src any) error {
var raw []byte
switch src := src.(data) {
case string:
raw = []byte(src)
case []byte:
raw = src
default:
return nil // 或者返回一个错误，例如 fmt.Errorf("type %T not supported by Scan")
}
return json.Unmarshal(raw, ls)
// 注意：根据 store.NullString 的实现方式和相应的 JSON 值，解组可能会失败
}
英文:
You can implement the Scanner interface.
type DataStruct struct {
Data DataList `db:"data"`
Field1 string `db:"field1"`
Field2 string `db:"field2"`
}
type DataList []Data
func (ls *DataList) Scan(src any) error {
var raw []byte
switch src := src.(data) {
case string:
raw = []byte(src)
case []byte:
raw = src
default:
return nil // or return an error, e.g. fmt.Errorf("type %T not supported by Scan", src)
}
return json.Unmarshal(raw, ls)
// NOTE: keep in mind that the unmarshal may fail
// depending on how store.NullString is implemented
// and the corresponding JSON value.
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论