2014-09-10 05:48
浏览 246

将Golang JSON存储到Postgresql中

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

type Comp struct {
    CompId               int64           `db:"comp_id" json:"comp_id"`
    StartDate            time.Time       `db:"start_date" json:"start_date"`
    EndDate              time.Time       `db:"end_date" json:"end_date"`
    WeeklySchedule       json.RawMessage `db:"weekly_schedule" json:"weekly_schedule"`

The schema for the table is:

    comp_id                 SERIAL,
    start_date              timestamp NOT NULL,
    end_date                timestamp NOT NULL,
    weekly_schedule         json NOT NULL,
    PRIMARY KEY (comp_id)

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

    tx := DB.MustBegin()

    compFixture := Comp{
        StartDate:            time.Now(),
        EndDate:              time.Now().AddDate(1, 0, 0),
        WeeklySchedule:       json.RawMessage([]byte("{}")),
    _, err = tx.NamedExec(
        `INSERT INTO 
                start_date, end_date, weekly_schedule) 
            VALUES (DEFAULT, 
                :start_date, :end_date, :weekly_schedule)  
            RETURNING comp_id;`, compFixture)
    if err != nil {
        t.Fatal("Error creating fixture.", err)

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?

图片转代码服务由CSDN问答提供 功能建议


  type Comp struct {
 CompId int64`db:“ comp_id” json:“ comp_id”`
 StartDate time.Time`db:“ start_date” json:“ start_date”`
 EndDate时间 .Time`db:“ end_date” json:“ end_date”`
 WeeklySchedule json.RawMessage`db:“ weekly_schedule” json:“ weekly_schedule”`
 <  p>该表的架构为: 
 comp_id SERIAL,
起始日期时间戳NOT NULL,
结束日期时间戳NOT NULL  ,
 weekly_schedule json NOT NULL,

我在我的项目中使用了sqlx和lib / pq驱动程序 以下将不会执行。 相反,它惊慌地说有一个nil指针。 DB是全局 * sqlx.DB 结构

  tx:= DB.MustBegin()
 compFixture:= Comp {
 StartDate  :time.Now(),
 WeeklySchedule:json.RawMessage([] byte(“ {}”)),
  _,err = tx.NamedExec(
返回comp_id  ;`,compFixture)
 if err!= nil {

从架构中删除 weekly_schedule 并完成一切。 但是由于某种原因,当包含此字段时,程序会出现紧急情况。 关于如何在数据库架构和Go结构中定义 weekly_schedule 字段的任何想法?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • doulv8162 2014-09-11 21:43

    sqlx has a type JSONText in that will do what you need

    doc for JSONText

    打赏 评论
  • douda5227 2014-09-10 06:29

    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.

    type JSONRaw json.RawMessage
    func (j JSONRaw) Value() (driver.Value, error) {
        byteArr := []byte(j)
        return driver.Value(byteArr), nil
    func (j *JSONRaw) Scan(src interface{}) error {
        asBytes, ok := src.([]byte)
        if !ok {
            return error(errors.New("Scan source was not []bytes"))
        err := json.Unmarshal(asBytes, &j)
        if err != nil {
            return error(errors.New("Scan could not unmarshal to []string"))
        return nil
    func (m *JSONRaw) MarshalJSON() ([]byte, error) {
        return *m, nil
    func (m *JSONRaw) UnmarshalJSON(data []byte) error {
        if m == nil {
            return errors.New("json.RawMessage: UnmarshalJSON on nil pointer")
        *m = append((*m)[0:0], data...)
        return nil

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

    打赏 评论
  • dosc9472 2014-09-10 08:39

    from the go documentation:

    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?

    打赏 评论

相关推荐 更多相似问题