How to implement automatic conversion of position fields to JSON format every time you query a spot table?

GORM efficiently handles Geometry type data: automatic JSON conversion

When operating a database with GORM, it is often necessary to process geometry -type data, such as converting position fields in the database (assuming it is geometry type) into a more easily processed JSON format. This article will introduce a method to automatically convert position field to JSON every time you query spot table, without manually converting it every time you query.

Question: Simplify the conversion of Geometry to JSON

In the past, we might have to convert position field to a JSON string using the st_asgeojson function alone after each query. To simplify the process, we want GORM to automatically complete this transformation when querying.

Solution: Custom Scanner and Raw SQL Query

To implement automatic JSON conversion in GORM, we can combine custom scanners and Raw SQL queries:

1. Model definition and custom scanner:

   First, define the spot model and add a custom scanner for position field. This scanner is responsible for converting the byte stream returned by the database into a geojson.geometry structure. Here, assume that you have introduced github.com/paulmach/go.geojson and github.com/tidwall/gjson libraries.

    import (
       "errors"
       "github.com/paulmach/go.geojson"
       "github.com/tidwall/gjson"
   )
   
   type spot struct {
       ID uint
       Position *geojson.Geometry `gorm:"column:position"`
   }
   
   func (g *geojson.Geometry) scan(value interface{}) error {
       if value == nil {
           return nil
       }
       bytes, ok := value.([]byte)
       if !ok {
           return errors.New("failed to scan geometry")
       }
       return gjson.Unmarshal(string(bytes), g) // Use gjson for JSON deserialization}

2. Custom query functions:

   Create a custom function, use Raw SQL query, and use the st_asgeojson function to convert at the database level.

    import "gorm.io/gorm"
   
   func GetSpot(db *gorm.DB, id uint) (*spot, error) {
       var spot spot
       err := db.Raw("SELECT id, ST_AsGeoJSON(position) as position FROM spot WHERE id = ?", id).Scan(&spot).Error
       if err != nil {
           return nil, err
       }
       return &spot, nil
   }

3. Data update:

   When updating data, you need to convert the geojson.Geometry structure back to a JSON string, and then write it to the database using the ST_GeomFromGeoJSON function.

    import (
       "github.com/tidwall/gjson"
   )
   
   func UpdateSpot(db *gorm.DB, spot *spot) error {
       jsonStr, err := gjson.Marshal(spot.Position) // Use gjson for JSON serialization if err != nil {
           return err
       }
       return db.Exec("UPDATE spot SET position = ST_GeomFromGeoJSON(?) WHERE id = ?", jsonStr, spot.ID).Error
   }

Through the above steps, we have realized the automatic conversion of geometry types to JSON format in GORM, which improves code efficiency and readability. Remember to replace github.com/paulmach/go.geojson and github.com/tidwall/gjson for your actual package path. Choosing the right JSON library depends on your project requirements.

The above is the detailed content of How to implement automatic conversion of position fields to JSON format every time you query a spot table?. For more information, please follow other related articles on the PHP Chinese website!