将Golang的JSON存储到PostgreSQL中

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

Storing Golang JSON into Postgresql

问题

我想将一个包含 JSON 字段的特定结构体存储到我的数据库中。

  1. type Comp struct {
  2.     CompId          int64           `db:"comp_id" json:"comp_id"`
  3.     StartDate       time.Time       `db:"start_date" json:"start_date"`
  4.     EndDate         time.Time       `db:"end_date" json:"end_date"`
  5.     WeeklySchedule  json.RawMessage `db:"weekly_schedule" json:"weekly_schedule"`
  6. }

表的模式如下：

  1. CREATE TABLE IF NOT EXISTS Tr.Comp(
  2.     comp_id             SERIAL,
  3.     start_date          timestamp NOT NULL,
  4.     end_date            timestamp NOT NULL,
  5.     weekly_schedule     json NOT NULL,
  6.     PRIMARY KEY (comp_id)
  7. );

在我的项目中，我使用了 sqlx 和 lib/pq 驱动程序，但以下代码无法执行。它会引发 panic，报告空指针错误。DB 是一个全局的 *sqlx.DB 结构体。

  1. tx := DB.MustBegin()
  3. compFixture := Comp{
  4.     StartDate:       time.Now(),
  5.     EndDate:         time.Now().AddDate(1, 0, 0),
  6.     WeeklySchedule:  json.RawMessage([]byte("{}")),
  7. }
  8. _, err = tx.NamedExec(
  9.     `INSERT INTO 
  10.         Tr.Comp(comp_id, 
  11.             start_date, end_date, weekly_schedule) 
  12.         VALUES (DEFAULT, 
  13.             :start_date, :end_date, :weekly_schedule)  
  14.         RETURNING comp_id;`, compFixture)
  15. if err != nil {
  16.     t.Fatal("Error creating fixture.", err)
  17. }

当我从模式和 fixture 中移除 weekly_schedule 字段时，一切都可以正常运行。但是出现问题的原因是，当包含该字段时，程序会发生 panic。你有什么想法，我应该如何在数据库模式和 Go 结构体中定义 weekly_schedule 字段？

英文:

I want to store a certain struct into my database that has a JSON field within it.

  1. type Comp struct {
  2. 	CompId               int64           `db:"comp_id" json:"comp_id"`
  3. 	StartDate            time.Time       `db:"start_date" json:"start_date"`
  4. 	EndDate              time.Time       `db:"end_date" json:"end_date"`
  5. 	WeeklySchedule       json.RawMessage `db:"weekly_schedule" json:"weekly_schedule"`
  6. }

The schema for the table is:

  1. CREATE TABLE IF NOT EXISTS Tr.Comp(
  2. 	comp_id 			    SERIAL,
  3. 	start_date				timestamp NOT NULL,
  4. 	end_date				timestamp NOT NULL,
  5. 	weekly_schedule			json NOT NULL,
  6. 	PRIMARY KEY (comp_id)
  7. );

I am using sqlx and lib/pq driver in my project and the following will not execute. Instead it panics saying there is a nil pointer. DB is a global *sqlx.DB struct

  1. 	tx := DB.MustBegin()
  3.     compFixture := Comp{
  4. 		StartDate:            time.Now(),
  5. 		EndDate:              time.Now().AddDate(1, 0, 0),
  6. 		WeeklySchedule:       json.RawMessage([]byte("{}")),
  7. 	}
  8. 	_, err = tx.NamedExec(
  9. 		`INSERT INTO 
  10. 			Tr.Comp(comp_id, 
  11. 				start_date, end_date, weekly_schedule) 
  12. 			VALUES (DEFAULT, 
  13. 				:start_date, :end_date, :weekly_schedule)  
  14. 			RETURNING comp_id;`, compFixture)
  15. 	if err != nil {
  16. 		t.Fatal("Error creating fixture.", err)
  17. 	}

When I remove weekly_schedule from the schema and fixture things run fine. But for some reason, the when this field is included, the program panics. Any idea as to how I should define the weekly_schedule field in both my DB schema and Go struct?

答案1

得分: 15

sqlx在github.com/jmoiron/sqlx/types中有一个名为JSONText的类型，它可以满足你的需求。

doc 关于 JSONText 的文档。

英文:

sqlx has a type JSONText in github.com/jmoiron/sqlx/types that will do what you need

doc for JSONText

答案2

得分: 5

我不知道这个解决方案有多干净，但我最终创建了自己的数据类型JSONRaw。数据库驱动程序将其视为[]byte，但在Go代码中仍然可以像json.RawMessage一样处理。

  1. type JSONRaw json.RawMessage
  3. func (j JSONRaw) Value() (driver.Value, error) {
  4.     byteArr := []byte(j)
  6.     return driver.Value(byteArr), nil
  7. }
  9. func (j *JSONRaw) Scan(src interface{}) error {
  10.     asBytes, ok := src.([]byte)
  11.     if !ok {
  12.         return errors.New("Scan source was not []bytes")
  13.     }
  14.     err := json.Unmarshal(asBytes, &j)
  15.     if err != nil {
  16.         return errors.New("Scan could not unmarshal to []string")
  17.     }
  19.     return nil
  20. }
  22. func (m *JSONRaw) MarshalJSON() ([]byte, error) {
  23.     return *m, nil
  24. }
  26. func (m *JSONRaw) UnmarshalJSON(data []byte) error {
  27.     if m == nil {
  28.         return errors.New("json.RawMessage: UnmarshalJSON on nil pointer")
  29.     }
  30.     *m = append((*m)[0:0], data...)
  31.     return nil
  32. }

这是从encoding/json库中复制粘贴重新实现的MarshalJSON和UnmarshalJSON函数。

英文:

I don't know how clean of a solution this is but I ended up making my own data type JSONRaw. The DB driver sees it as a []btye but it can still be treated like a json.RawMessage in the Go Code.

  1. type JSONRaw json.RawMessage
  3. func (j JSONRaw) Value() (driver.Value, error) {
  4. 	byteArr := []byte(j)
  6. 	return driver.Value(byteArr), nil
  7. }
  9. func (j *JSONRaw) Scan(src interface{}) error {
  10. 	asBytes, ok := src.([]byte)
  11. 	if !ok {
  12. 		return error(errors.New("Scan source was not []bytes"))
  13. 	}
  14. 	err := json.Unmarshal(asBytes, &j)
  15. 	if err != nil {
  16. 		return error(errors.New("Scan could not unmarshal to []string"))
  17. 	}
  19. 	return nil
  20. }
  22. func (m *JSONRaw) MarshalJSON() ([]byte, error) {
  23. 	return *m, nil
  24. }
  26. func (m *JSONRaw) UnmarshalJSON(data []byte) error {
  27. 	if m == nil {
  28. 		return errors.New("json.RawMessage: UnmarshalJSON on nil pointer")
  29. 	}
  30. 	*m = append((*m)[0:0], data...)
  31. 	return nil
  32. }

This is copy paste reimplementation of MarshalJSON and UnmarshalJSON from the encoding/json library.

答案3

得分: 0

根据Go文档：

json.RawMessage是一个原始编码的JSON对象。它实现了Marshaler和Unmarshaler接口，可以用于延迟JSON解码或预先计算JSON编码。

如果在提供的示例中使用log.Printf("%#", colors)，你会发现在解码JSON对象后，'Point'成员没有被解码，而是以[]byte格式保留，直到颜色格式被修复并且'Point'被显式解码。

你是否尝试过在将WeeklySchedule解码并放入数据库之前进行解码操作？

英文:

from the go documentation:

  1. json.RawMessage is a raw encoded JSON object. It implements Marshaler and Unmarshaler and can be used to delay JSON decoding or precompute a JSON encoding.

if you log.Printf("%#", colors) in the example provided at package json json.RawMessage you can see that after unmarshalling the json object 'Point'-member isn't unmarshalled but left in []byte format until the color-format is fixed and 'Point' is unmarshalled explicitly.

Did you try something like unmarshal WeeklySchedule before putting it into the DB?

huangapple
  • 本文由 发表于 2014年9月10日 13:48:04
  • 转载请务必保留本文链接：https://go.coder-hub.com/25758138.html
  • go
  • json
  • postgresql
  • sql
  • struct
匿名

发表评论

匿名网友

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

确定