Unmarshal Json Array of object obtained from postgresql

Question

I have a table in Postgres that is a Jsonb

Create Table Business(
    id serial not null primary key,
    id_category integer not null,
    name varchar(50) not null,
    owner varchar(200) not null,
    coordinates jsonb not null,
    reason varchar(300) not null,
    foreign key(id_category) references Category(id)
);

as you can see i store the coordinates as a jsonb

ex:

Insert into Business(id_category, name, owner, coordinates, reason) 
values 
(1,'MyName','Owner', [{"latitude": 12.1268142, "longitude": -86.2754}]','Description')

the way that I extract the data and assign it is like this.

type Business struct {
    ID int `json:"id,omitempty"`
    Name string `json:"name,omitempty"`
    Owner string `json:"owner,omitempty"`
    Category string `json:"category,omitempty"`
    Departments []string `json:"departments,omitempty"`
    Location []Coordinates `json:"location,omitempty"`
    Reason string `json:"reason,omitempty"`
}

type Coordinates struct {
    Latitude float64 `json:"latitude,omitempty"`
    Longitude float64 `json:"longitude,omitempty"`
}

func (a Coordinates) Value() (driver.Value, error) {
    return json.Marshal(a)
}

func (a *Coordinates) Scan(value []interface{}) error {
    b, ok := value.([]byte)
    if !ok {
        return errors.New("type assertion to []byte failed")
    }
    return json.Unmarshal(b, &a)
}

However, I keep receiving this message.

sql: Scan error on column index 3, name "coordinates": unsupported Scan, storing driver.Value type []uint8 into type *models.Coordinates

And the controller that I use to extract the information is this.

func (b *BusinessRepoImpl) Select() ([]models.Business, error) {
    business_list := make([]models.Business, 0)
    rows, err := b.Db.Query("SELECT business.id, business.name, business.owner, business.coordinates, business.reason_froggy, category.category FROM business INNER JOIN category on category.id = business.id_category group by business.id, business.name, business.owner, business.coordinates, business.reason_froggy, category.category")
    if err != nil {
        return business_list, err
    }
    for rows.Next() {
        business := models.Business{}
        err := rows.Scan(&business.ID, &business.Name, &business.Owner, &business.Location, &business.Reason, &business.Category)
        if err !=  nil {
            break
        }
        business_list = append(business_list, business)
    }
    err = rows.Err()
    if err != nil {
        return business_list, err
    }
    return business_list, nil
}

Can anyone please tell me how to solve this issue? Retrieve the json array of objects and assign it to the coordinates field inside Business.

That's all the code that i use, or you need something more specific? — Luis Cardoza Bird

mkopriva mkopriva · Accepted Answer · 2019-10-30T21:39:47

1.

As you can see from the documentation the Scanner interface, to be satisfied, requires the method

Scan(src interface{}) error

But your *Coordinates type implements a different method

Scan(value []interface{}) error

The types interface{} and []interface{} are two very different things.

2.

The Scanner interface must be implemented on the type of the field which you want to pass as an argument to rows.Scan. That is, you've implemented your Scan method on *Coordinates but the type of the Location field is []Coordinates.

Again, same thing, the types *Coordinates and []Coordinates are two very different things.

So the solution is to implement the interface properly and on the proper type.

Note that since Go doesn't allow adding methods to unnamed types, and []Coordinates is an unnamed type, you need to declare a new type that you'll then use in place of []Coordinates.

type CoordinatesSlice []Coordinates

func (s *CoordinatesSlice) Scan(src interface{}) error {
    switch v := src.(type) {
    case []byte:
        return json.Unmarshal(v, s)
    case string:
        return json.Unmarshal([]byte(v), s)
    }
    return errors.New("type assertion failed")
}

// ...

type Business struct {
    // ...
    Location CoordinatesSlice `json:"location,omitempty"`
    // ...
}

NOTE

If the business location will always have only one pair of coordinates store into the db as a jsonb object and change the Location type from CoordinatesSlice to Coordinates and accordingly move the Scanner implementation from *CoordinatesSlice to *Coordinates.

Unmarshal Json Array of object obtained from postgresql

2 Answers