map[string]interface{} mapping not working for database/sql

Hi!

I am trying to map a json object from the database to a map[string]interface{}

I am doing this because I want to reuse the struct and there can be an arbitrary number of columns queried from the database.

I managed to get it working with pgx driver, however, with database/sql I am getting an error that I don't understand ...

Scan error on column index 2, name "data": unsupported Scan, storing driver.Value type []uint8 into type *map[string]interface {}

I managed to get all the code in a single file, here it is:


package main

import (
    "context"
    "database/sql"
    "fmt"
    "github.com/georgysavva/scany/pgxscan"
    "github.com/georgysavva/scany/sqlscan"
    "github.com/jackc/pgx/v4"
    _ "github.com/lib/pq"
    "time"
)

type MetricMap struct {
    Time     time.Time
    DeviceId string
    Data     map[string]interface{}
}

func main() {
    fmt.Println("Starting timescale playground")
    ctx := context.Background()
    connStr := "postgres://postgres:postgres@localhost:5430/postgres?sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        fmt.Println(err)
        return
    }
    sqlJson := `
        select time, device_id, json_build_object('ph_con_tot', ph_con_tot , 'ph_gen_tot', ph_gen_tot) as "data"
        from metrics_computer_wide
          WHERE device_id = '9d5eaae0-421b-11ec-9949-7f0fdad2c99c' and ph_con_tot is not null and 
          time > '2022-04-01' and time <= '2022-04-05' limit 10
        `
    //sqlJson = "SELECT time, device_id, json_build_object('ph_con_tot',ph_con_tot,'ph_gen_tot',ph_gen_tot) as data FROM metrics_base WHERE device_id = '08c210ca-7077-4907-8ea7-a98b77d4df0c' AND time >= '2022-05-02 13:13:56' AND time <= '2022-05-02 13:14:56'"
    var metrics []MetricMap

    err = sqlscan.Select(ctx, db, &metrics, sqlJson)
    if err != nil {
        fmt.Println(err)
    } else {
        for _, metric := range metrics {
            fmt.Printf("%s %s %f %f\n", metric.Time, metric.DeviceId, metric.Data["ph_con_tot"], metric.Data["ph_gen_tot"])
        }
    }

    // Now with PGX Scan
    dbPgx, err := pgx.Connect(ctx, connStr)
    if err != nil {
        fmt.Println(err)
        return
    }
    ctxPgx := context.Background()

    pgxscan.Select(ctxPgx, dbPgx, &metrics, sqlJson)
    if err != nil {
        fmt.Println(err)
    } else {
        for _, metric := range metrics {
            fmt.Printf("%s %s %f %f\n", metric.Time, metric.DeviceId, metric.Data["ph_con_tot"], metric.Data["ph_gen_tot"])
        }
    }

}

What am I doing wrong? I tried to debug the problem going deeper into your library, but still I don't understand where some magic happens ..

Thank you very much!

georgysavva / scany

map[string]interface{} mapping not working for database/sql #82