Golang:将 JSON 字符串从 MySQL 数据库解码为结构体。

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

Golang: Decode json string to struct from mysql db

问题

我正在尝试从我的数据库中获取信息,其中一个字段实际上是以字符串形式存储的 JSON,我想将其作为结构体获取。

这是我的行结构体:

// there is json flag because I use it to get data from redis too
type InfoClip struct {
	ClipId             string `json:"clipId"`
	StreamUrl          string `json:"streamUrl"`
	StartTimeCode      int    `json:"startTimeCode"`
	EndTimeCode        int    `json:"endTimeCode"`
	CreatedAt          string `json:"createdAt"`
	Metas              string `json:"metas"` // here I get a string instead of a 'metas' struct
	SourceId           string `json:"sourceId"`
	ProviderName       string `json:"providerName"`
	ProviderReference  string `json:"providerReference"`
	PublicationStatus  string `json:"publicationStatus"`
	UserId             string `json:"userId"`
	Name               string `json:"name"`
	FacebookPage       string `json:"facebookPage"`
	TwitterHandle      string `json:"twitterHandle"`
	PermaLinkUrl       string `json:"permalinkUrl"`
	Logo               string `json:"logo"`
	Link               string `json:"link"`
}

这是我的 metas 结构体:

type metas struct {
	Title      string   `json:"title"`
	Tags       []string `json:"tags"`
	Categories []string `json:"permalink"`
}

这是我尝试获取该字段的方法:

func decodeJsonSql(met string) (*metas, error) {
	m := metas{}
	if err := json.Unmarshal([]byte(met), &m); err != nil {
		fmt.Printf("Error decode metas: ", err)
		return nil, err
	} else {
		return &m, err
	}
}

func CheckIdSql(mediaId string) error {
	datab, err := sql.Open("mysql", "tcp()")
	if err != nil {
		fmt.Printf("[SQL ERROR] Cannot Open db => ", err)
		return err
	}
	if err := datab.Ping(); err != nil {
		fmt.Printf("[SQL ERROR] db connection => ", err)
		return err
	}
	fmt.Printf("[SQL ONLINE] =>", datab)
	defer datab.Close()

	q := "SELECT c.id AS clipId, c.streamUrl, c.startTimecode,  c.endTimecode, c.createdAt, s.metas,... FROM clips WHERE c.id = ?"
	rows, err := datab.Query(q, mediaId)
	if err != nil || err == sql.ErrNoRows {
		fmt.Printf("SQL Err: %s", err)
		return err
	}
	clips := InfoClip{}
	for rows.Next() {
		rows.Scan(&clips.ClipId, &clips.StreamUrl, &clips.StartTimeCode, &clips.EndTimeCode, &clips.CreatedAt, &clips.Metas, ...)
	}
	ret, err := decodeJsonSql(clips.Metas)
	if err != nil {
		return err
	}
	clips.Metas = ret
	fmt.Printf("\n\n[SQL DEBUG RESPONSE]: %v", clips)
	return nil
}

但这个过程相当繁重,肯定有更简单的方法吧?谢谢。

英文:

I'm trying to get informations from my db, and one of my fields is actually JSON stored as a string and I would like to get it as a struct.

This is my row's struct :

//there is json flag because I use it to get data from redis too
type InfoClip struct {
ClipId             string `json:clipId`
StreamUrl          string `json:streamUrl`
StartTimeCode      int `json:startTimeCode`
EndTimeCode        int `json:endTimeCode`
CreatedAt          string `json:createdAt`
Metas              string `json:metas` // here I get a string instead of a 'metas' struct
SourceId           string `json:sourceId`
ProviderName       string `json:providerName`
ProviderReference  string `json:providerReference`
PublicationStatus  string `json:publicationStatus`
UserId             string `json:userId`
Name               string `json:name`
FacebookPage       string `json:facebookPage`
TwitterHandle      string `json:twitterHandle`
PermaLinkUrl       string `json:permalinkUrl`
Logo               string `json:logo`
Link               string `json:link`
}

This is my metas struct :

type metas struct {
Title		string `json:title`
Tags		[]string `json:tags`
categories	[]string `json:permalink`
}

This is how I'm trying to get this field

func decodeJsonSql (met string) (*metas, error) {
m := metas{}
if err := json.Unmarshal([]byte(met), &m); err != nil {
fmt.Printf("Error decode metas: ", err)
return nil, err
} else {
return &m, err
}
} 
func CheckIdSql(mediaId string) (error){
datab, err := sql.Open("mysql", "tcp()")
if err != nil {
fmt.Printf("[SQL ERROR] Cannot Open db => ", err)
return err
}
if err := datab.Ping(); err != nil {
fmt.Printf("[SQL ERROR] db connection => ", err)
return err
}
fmt.Printf("[SQL ONLINE] =>", datab)
defer datab.Close()
q := "SELECT c.id AS clipId, c.streamUrl, c.startTimecode,  c.endTimecode, c.createdAt, s.metas,... FROM clips WHERE c.id = ?"
rows, err := datab.Query(q, mediaId)
if err != nil || err == sql.ErrNoRows {
fmt.Printf("SQL Err: %s", err)
return err
}
clips := InfoClip{}
for rows.Next() {
rows.Scan(&clips.ClipId, &clips.StreamUrl, &clips.StartTimeCode, &clips.EndTimeCode, &clips.CreatedAt, &clips.Metas, ...)
}
ret, err := decodeJsonSql(clips.Metas)
if err != nil{
return err
}
clips.Metas = ret
fmt.Printf("\n\n[SQL DEBUG RESPONSE]: %v", clips)
return nil
}

But this process is pretty heavy, surely there is an easier way?
Thanks.

答案1

得分: 13

你可以让你的metas结构体实现sql.Scanner接口。

实现大致如下:

func (m *metas) Scan(src interface{}) error {
    strValue, ok := src.(string)

    if !ok {
        return fmt.Errorf("metas字段必须是字符串类型,而不是%T", src)
    }

    return json.Unmarshal([]byte(strValue), m)
}

之后,你可以将其作为InfoClip字段,并直接将其传递给Scan,不再需要decodeJsonSql

type InfoClip struct {
    // [...]
    Metas metas `json:"metas"`
    // [...]
}

然后,在查询时使用datab.QueryRow代替datab.Query(因为你只期望一个结果):

q := "SELECT c.id AS clipId, c.streamUrl, c.startTimecode, c.endTimecode, c.createdAt, s.metas,... FROM clips WHERE c.id = ?"
row := datab.QueryRow(q, mediaId)
clips := InfoClip{}
err := row.Scan(&clips.ClipId, &clips.StreamUrl, &clips.StartTimeCode, &clips.EndTimeCode, &clips.CreatedAt, &clips.Metas) // [...]
if err != nil {
    fmt.Printf("SQL错误:%s", err)
    return err
}

顺便提一下,如你所见,我将datab.Query替换为datab.QueryRow,因为你只期望一个结果。

英文:

You can make your metas struct implement the sql.Scanner interface

It should look something like this:

func (m *metas) Scan(src interface{}) error {
strValue, ok := src.(string)
if !ok {
return fmt.Errorf("metas field must be a string, got %T instead", src)
}
return json.Unmarshal([]byte(strValue), m)
}

After that you can use it as an InfoClip field and pass it directly to Scan and drop the decodeJsonSql:

type InfoClip struct {
// [...]
Metas metas `json:metas`
// [...]
}

and

q := "SELECT c.id AS clipId, c.streamUrl, c.startTimecode,  c.endTimecode, c.createdAt, s.metas,... FROM clips WHERE c.id = ?"
row := datab.QueryRow(q, mediaId)
clips := InfoClip{}
err := row.Scan(&clips.ClipId, &clips.StreamUrl, &clips.StartTimeCode, &clips.EndTimeCode, &clips.CreatedAt, &clips.Metas) // [...]
if err != nil {
fmt.Printf("SQL Err: %s", err)
return err
}

(BTW, as you can see, I replaced datab.Query with datab.QueryRow as you are expecting only one result)

huangapple
  • 本文由 发表于 2015年10月17日 01:36:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/33176367.html
匿名

发表评论

匿名网友

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

确定