go-gorm postgres方言：管理jsonb插入的结构并找到正确使用json标签的方法

Question

Been doing a lot of searching, and although i can find a bunch of good articles that explain how to work with the pq package directly. I'm at a loss of working in context of go-gorm and the postgresql dialect.

If in checks.go I use the ChecksMap it doesn't let me insert but will let me find. If i use postgres.jsonb it lets me insert and query, but the found records will be of jsonb.

Gorm uses the struct of the pointer to determine the db table and schema. This is causing headaches when using a generic searchHandler utility which returns a json response from the API. For any non jsonb types gorm works with the proper structs and uses the json tags, but for jsonb since it doesn't have a reference to the jsonb's "struct" it can't use the json tags. This causes the return API json to have capitalized keys.

{
   results: {
      id: "123",
      someId: "456",
      results: [
         {
            Description: "foobar"
         }
      ]
   }
}

Is there an elegant way to handling this sort of thing so the jsonb results column will be of the correct struct and use the lowercased json tags? Am i just trying to do things which shouldn't be done within the context of go-gorm?

POSTGRESQL DDL

CREATE TABLE checks (
   id        text,
   some_id   text,
   results   jsonb
);

checks.go

type CheckRules struct {
   Description   string `json:"description"`
}

type ChecksMap   map[string]CheckRules

type Checks struct {
   ID            string           `gorm: "primary_key", json:"id"`
   SomeID        *string          `json:"someId"`
   Results       postgres.jsonb   `json:"results"`                   // <-- this
   // results    ChecksMap        `gorm:"type:jsonb" json:"results"` // <-- or this
}

// func (cm *ChecksMap) Value() (driver.Value, error) {...}
// func (cm *ChecksMap) Scan(val interface{}) error {...}

insertChecks.go

var resultsVal = getResultsValue() // simplified
resJson, _ := json.Marshal(resultsVal)

checks := Checks{
   SomeID: "123",
   Results: postgres.Jsonb{ RawMessage: json.RawMessage(resJson) }
}

err := db.Create(&checks).Error
// ... some error handling

getChecks.go

var checks Checks

err := db.Find(&checks).Error
// ... some error handling

searchHandler.go

func SearchHandler(db *gorm.DB, model, results interface{}) func(c echo.Context) error {
   return func(c echo.Context) error {
      err := db.Find(results).Error
      // ... some error handling

      jsnRes, _ := json.Marshal(results) // <-- uppercase "keys"

      return c.JSON(http.StatusOK, struct {
         Results interface{} `json:"results"`
      }{
         Results: string(jsnRes),
      })
   }
}

Answer 1

You can use the custom ChecksMap type but implement the driver.Valuer interface on its value receiver, not the pointer reciever.

So, instead of:

func (cm *ChecksMap) Value() (driver.Value, error) { ...

You would write this:

func (cm ChecksMap) Value() (driver.Value, error) {
    if cm == nil {
        return nil, nil
    }
    return json.Marshal(cm)
}

---

Alternatively, you can probably make it work with the pointer implementation but then you'll have to turn the field into a pointer, e.g:

type Checks struct {
   ID      string     `gorm: "primary_key", json:"id"`
   SomeID  *string    `json:"someId"`
   Results *ChecksMap `json:"results"`
}

(although I haven't tested this so I'm not 100% sure how gorm will handle this case)