4

Inserting, reading, and updating JSON data in postgres using Golang GORM

 1 year ago
source link: https://blog.davidvassallo.me/2022/12/14/inserting-reading-and-updating-json-data-in-postgres-using-golang-gorm/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Inserting, reading, and updating JSON data in postgres using Golang GORM

This is a “Frankenstein” approach which worked well using a combination of methods found online (see references). On their own, each approach had a limitation, but together they ticked all the boxes.

Assuming you have the following table defined as a struct within you Go program:

type User struct {
Name           string
Email           string
Attributes     JSONB  `gorm:"type:jsonb"`
}

Note the “attributes” field, which is given a type of JSONB. This JSONB is not to be confused with the “type:jsonb” that comes later on in the line. The first JSONB is a Go type, while the latter jsonb is a gorm tag which instructs GORM to ensure that this field is given a database type of “jsonb” in postgres.

While postgres has a field type of “jsonb”, Go does not have a “JSONB” type, so we need to define it ourselves:

type JSONB map[string]interface{}

However, now we need to tell GORM how to read and write data from this custom type. The GORM docs mention that the “Scan” and “Value” methods need to be implemented for a type, so off we go:

// Value Marshal
func (jsonField JSONB) Value() (driver.Value, error) {
return json.Marshal(jsonField)
}
// Scan Unmarshal
func (jsonField *JSONB) Scan(value interface{}) error {
data, ok := value.([]byte)
if !ok {
return errors.New("type assertion to []byte failed")
}
return json.Unmarshal(data,&jsonField )
}

The above simply uses the inbuilt GO “json” library to marshal/unmarshal data from/to the field. So far, we’ve wired GORM up to accept and work with our JSON field. Next, we need to perform actual insert/update/query actions

Inserting and Updating Data

Recall that we defined our “Attributes” field to be a map[string]interface. Maps in Go must be initialized when first used:

user := User{}
if user.Attributes == nil {
//in this example, this IF branch will always match
user.Attributes = make(map[string]interface{}, 0)
}
user.Attributes["key1"] = "value1"
// Inserting Data
db.Model(&User{}).Create(&user)
// Updating Data
db.Model(&User{}).Where("id = ?", dbUser.ID).UpdateColumn("attributes", &user.Attributes)

Querying Data

When querying data, the easiest approach I found was to use GORM datatypes:

var users []User
db.Model(&User{}).
Where(datatypes.JSONQuery("attributes").HasKey("key1")).
Where(datatypes.JSONQuery("attributes").Equals("value1", "key1")).
Find(&users)

Note the use of datatype.JSONQuery to build out the actual JSON query:

  • HasKey will filter for those rows whose JSON field contain the “key1” key
  • Equals will filter for those rows where the JSON key is set to the appropriate value. What I found a bit counter-intuitive is that the expected value comes first, followed by the key name second.

References


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK