首页 > Golang > Go问答

将 PostgreSQL JSONB 查询结果反序列化到 Golang 嵌套结构中

来源：stackoverflow

时间：2024-03-03 10:09:24 365浏览收藏

“纵有疾风来，人生不言弃”，这句话送给正在学习Golang的朋友们，也希望在阅读本文《将 PostgreSQL JSONB 查询结果反序列化到 Golang 嵌套结构中》后，能够真的帮助到大家。我也会在后续的文章中，陆续更新Golang相关的技术文章，有好的建议欢迎大家在评论留言，非常感谢！

问题内容

我正在尝试从 postgresql jsonb 和非 jsonb 列读取一些数据，然后解组我得到的嵌套结构的响应字符串。

我能够以字符串形式检索数据。但我不知道如何将其解组到结构中。

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\n", v)
            case "user_id":
                fmt.printf("userid: %s\n", 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\n", 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
    )

我希望在成功解组到结构后输出是这样的

[
  {
    "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
  }
]

而是从数据库中以 string 类型获取它，如下所示。

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
  }
]]

解决方案

我认为这是 pq 驱动程序的预期行为 - 请参阅：https://github.com/lib/pq/issues/348

有人建议在问题线程中使用 sqlx -- 我没有尝试过：https://github.com/lib/pq/issues/348#issuecomment-282742349

您还可以将驱动程序切换为 pgx: https://github.com/jackc/pgx——从此驱动程序的文档中：

pgx 包含对 Go 类型与 PostgreSQL JSON 和 JSONB 之间编组和解组的内置支持。

我过去使用 pgx 进行 jsonb 解组——效果很好。

文中关于的知识介绍，希望对你的学习有所帮助！若是受益匪浅，那就动动鼠标收藏这篇《将 PostgreSQL JSONB 查询结果反序列化到 Golang 嵌套结构中》文章吧，也可关注golang学习网公众号了解相关技术文章。

声明：本文转载于：stackoverflow 如有侵犯，请联系study_golang@163.com删除