将Postgresql jsonb查询响应字符串解组到golang嵌套结构中

Question

I am trying to read some data from postgresql both jsonb and non-jsonb columns then unmarshal the response string I am getting to a nested struct.

I am able to retrieve tha data as string.But I can't figure out how to unmarshal it into the struct.

package main
import (
    "database/sql"
    "fmt"
    "log"
    "strconv"

    _ "github.com/lib/pq"
)

type Token struct {
    Name     string
    Value    string
    Path     string
    HttpOnly bool
}

type Session struct {
    Phishlet   string                       `json:"phishlet,omitempty"`
    LandingURL string                       `json:"landing_url,omitempty"`
    Username   string                       `json:"username,omitempty"`
    Password   string                       `json:"password,omitempty"`
    Custom     map[string]string            `json:"custom,omitempty"`
    Tokens     map[string]map[string]*Token `json:"tokens,omitempty"`
    SessionId  string                       `json:"session_id,omitempty"`
    UserAgent  string                       `json:"useragent,omitempty"`
    RemoteAddr string                       `json:"remote_addr,omitempty"`
    CreateTime int64                        `json:"create_time,omitempty"`
    UpdateTime int64                        `json:"update_time,omitempty"`
}

type Sessions struct {
    Id      int64
    UserId  []byte
    Session Session
}



func main() {

    db, err := sql.Open("postgres", "postgres://user:pass@localhost:port/db")
    if err != nil {
        log.Fatal(err)
    }

    rows, err := db.Query("SELECT id, user_id,session FROM sessions ORDER BY id")
    defer rows.Close()
    columns, err := rows.Columns()
    if err != nil {
        log.Fatal(err)
    }
    count := len(columns)
    values := make([]interface{}, count)
    scanArgs := make([]interface{}, count)
    for i := range values {
        scanArgs[i] = &values[i]
    }

    data := make(map[string][]interface{})

    for rows.Next() {
        err = rows.Scan(scanArgs...)
        if err != nil {
            panic(fmt.Sprintf("rows.Scan: %v", err))
        }
        for i, v := range values {
            switch col := columns[i]; col {
            case "id":
                fmt.Printf("Id: %v
", v)
            case "user_id":
                fmt.Printf("UserId: %s
", v)
            case "session":
                x := v.([]byte)
                if nx, ok := strconv.ParseFloat(string(x), 64); ok == nil {
                    data[columns[i]] = append(data[columns[i]], nx)
                } else if b, ok := strconv.ParseBool(string(x)); ok == nil {
                    data[columns[i]] = append(data[columns[i]], b)
                } else if "string" == fmt.Sprintf("%T", string(x)) {
                    data[columns[i]] = append(data[columns[i]], string(x))
                } else {
                    fmt.Printf("Failed on if for type %T of %v
", x, x)
                }
            }
            fmt.Println(data)
        }
    }
}

//Database schema
create table if not exists sessions(
            id bigserial not null primary key,
            user_id uuid not null references users(id)
            session jsonb
    )

I expect the output to be this after successfully unmarshaling to the struct

[
  {
    "id": 1,
    "user_id": "c610ceb9-640f-43d2-8e26-a80be6afb831",
    "phishlet": "niko",
    "landing_url": "https://niko.com/ke/login",
    "username": "MyUser",
    "password": "my_p@55",
    "custom": {},
    "tokens": {
      ".niko.com": {
        "_niko_sess": {
          "Name": "_niko_sess",
          "Value": "BAh7CiIKZmxhc2hJQzonQWN0aW9uQ29udHJvbGxlcjo6Rmxhc2g6OkZsYXNo%250ASGFzaHsABjoKQHVzZWR7ADoPY3JlYXRlZF9hdGwrCG6lp1hqAToMY3NyZl9p%250AZCIlYjk4MGFiZDEyNGEzZTI0NjE0MzYzZDkyNTRmOWZiYzQ6B2lkIiVhOTA3%250AMjJjOGFkNDdlNzFiZmYyNmEwZjU2ZDBhYTYyYToJdXNlcmwrCQDgV68MtUMP--48e2dcf262fa4895366025210c10ff4c922a3fca",
          "Path": "/",
          "HttpOnly": true
        },
        "auth_token": {
          "Name": "auth_token",
          "Value": "e9b42a3b76907dff7b55117627a3be40e4c557ce",
          "Path": "",
          "HttpOnly": true
        },
        "nkt": {
          "Name": "nkt",
          "Value": "FdRf8fAkS8bEthMBUaY2gNqbTjFdZcUFh8wJBbR4",
          "Path": "",
          "HttpOnly": true
        },
        "nikd": {
          "Name": "nikd",
          "Value": "u=1099921800094539776",
          "Path": "",
          "HttpOnly": false
        }
      }
    },
    "session_id": "cd5c9c35b96cfdaac5e299b21c40a9fbad667e1710c6f1efb972008ac3bac55c",
    "useragent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:66.0) Gecko/20100101 Firefox/66.0",
    "remote_addr": "197.232.6.251",
    "create_time": 1556265484,
    "update_time": 1556265594
  },
  {
    "id": 2,
    "user_id": "d52673ef-cf4e-4081-855f-d7dd91762633",
    "phishlet": "niko",
    "landing_url": "https://niko.com/ke/login",
    "username": "MyUser",
    "password": "my_p@55",
    "custom": {},
    "tokens": {
      ".niko.com": {
        "_niko_sess": {
          "Name": "_niko_sess",
          "Value": "BAh7CiIKZmxhc2hJQzonQWN0aW9uQ29udHJvbGxlcjo6Rmxhc2g6OkZsYXNo%250ASGFzaHsABjoKQHVzZWR7ADoPY3JlYXRlZF9hdGwrCG6lp1hqAToMY3NyZl9p%250AZCIlYjk4MGFiZDEyNGEzZTI0NjE0MzYzZDkyNTRmOWZiYzQ6B2lkIiVhOTA3%250AMjJjOGFkNDdlNzFiZmYyNmEwZjU2ZDBhYTYyYToJdXNlcmwrCQDgV68MtUMP--48e2dcf262fa4895366025210c10ff4c922a3fca",
          "Path": "/",
          "HttpOnly": true
        },
        "auth_token": {
          "Name": "auth_token",
          "Value": "e9b42a3b76907dff7b55117627a3be40e4c557ce",
          "Path": "",
          "HttpOnly": true
        },
        "nkt": {
          "Name": "nkt",
          "Value": "FdRf8fAkS8bEthMBUaY2gNqbTjFdZcUFh8wJBbR4",
          "Path": "",
          "HttpOnly": true
        },
        "nikd": {
          "Name": "nikd",
          "Value": "u=1099921800094539776",
          "Path": "",
          "HttpOnly": false
        }
      }
    },
    "session_id": "cd5c9c35b96cfdaac5e299b21c40a9fbad667e1710c6f1efb972008ac3bac55c",
    "useragent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:66.0) Gecko/20100101 Firefox/66.0",
    "remote_addr": "197.232.6.251",
    "create_time": 1556265484,
    "update_time": 1556265594
  }
]

but instead am getting it from the db as type string like this.

map[session:[
  {
    "phishlet": "niko",
    "landing_url": "https://niko.com/ke/login",
    "username": "MyUser",
    "password": "my_p@55",
    "custom": {},
    "tokens": {
      ".niko.com": {
        "_niko_sess": {
          "Name": "_niko_sess",
          "Value": "BAh7CiIKZmxhc2hJQzonQWN0aW9uQ29udHJvbGxlcjo6Rmxhc2g6OkZsYXNo%250ASGFzaHsABjoKQHVzZWR7ADoPY3JlYXRlZF9hdGwrCG6lp1hqAToMY3NyZl9p%250AZCIlYjk4MGFiZDEyNGEzZTI0NjE0MzYzZDkyNTRmOWZiYzQ6B2lkIiVhOTA3%250AMjJjOGFkNDdlNzFiZmYyNmEwZjU2ZDBhYTYyYToJdXNlcmwrCQDgV68MtUMP--48e2dcf262fa4895366025210c10ff4c922a3fca",
          "Path": "/",
          "HttpOnly": true
        },
        "auth_token": {
          "Name": "auth_token",
          "Value": "e9b42a3b76907dff7b55117627a3be40e4c557ce",
          "Path": "",
          "HttpOnly": true
        },
        "nkt": {
          "Name": "nkt",
          "Value": "FdRf8fAkS8bEthMBUaY2gNqbTjFdZcUFh8wJBbR4",
          "Path": "",
          "HttpOnly": true
        },
        "nikd": {
          "Name": "nikd",
          "Value": "u=1099921800094539776",
          "Path": "",
          "HttpOnly": false
        }
      }
    },
    "session_id": "cd5c9c35b96cfdaac5e299b21c40a9fbad667e1710c6f1efb972008ac3bac55c",
    "useragent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:66.0) Gecko/20100101 Firefox/66.0",
    "remote_addr": "197.232.6.251",
    "create_time": 1556265484,
    "update_time": 1556265594
  }]]
  map[session:[{
    "phishlet": "niko",
    "landing_url": "https://niko.com/ke/login",
    "username": "MyUser",
    "password": "my_p@55",
    "custom": {},
    "tokens": {
      ".niko.com": {
        "_niko_sess": {
          "Name": "_niko_sess",
          "Value": "BAh7CiIKZmxhc2hJQzonQWN0aW9uQ29udHJvbGxlcjo6Rmxhc2g6OkZsYXNo%250ASGFzaHsABjoKQHVzZWR7ADoPY3JlYXRlZF9hdGwrCG6lp1hqAToMY3NyZl9p%250AZCIlYjk4MGFiZDEyNGEzZTI0NjE0MzYzZDkyNTRmOWZiYzQ6B2lkIiVhOTA3%250AMjJjOGFkNDdlNzFiZmYyNmEwZjU2ZDBhYTYyYToJdXNlcmwrCQDgV68MtUMP--48e2dcf262fa4895366025210c10ff4c922a3fca",
          "Path": "/",
          "HttpOnly": true
        },
        "auth_token": {
          "Name": "auth_token",
          "Value": "e9b42a3b76907dff7b55117627a3be40e4c557ce",
          "Path": "",
          "HttpOnly": true
        },
        "nkt": {
          "Name": "nkt",
          "Value": "FdRf8fAkS8bEthMBUaY2gNqbTjFdZcUFh8wJBbR4",
          "Path": "",
          "HttpOnly": true
        },
        "nikd": {
          "Name": "nikd",
          "Value": "u=1099921800094539776",
          "Path": "",
          "HttpOnly": false
        }
      }
    },
    "session_id": "cd5c9c35b96cfdaac5e299b21c40a9fbad667e1710c6f1efb972008ac3bac55c",
    "useragent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:66.0) Gecko/20100101 Firefox/66.0",
    "remote_addr": "197.232.6.251",
    "create_time": 1556265484,
    "update_time": 1556265594
  }
]]

Answer 1

I think this is the expected behavior of the pq driver -- see this: https://github.com/lib/pq/issues/348

There is a suggestion to use sqlx in the issue thread -- I've not tried it: https://github.com/lib/pq/issues/348#issuecomment-282742349

You could also switch the driver to pgx: https://github.com/jackc/pgx -- from this driver's docs:

pgx includes built-in support to marshal and unmarshal between Go types and the PostgreSQL JSON and JSONB.

I've used pgx for jsonb unmarshalling in the past -- with good results.