如何将PostgreSQL中的jsonb对象扫描到Golang结构体中

huangapple go评论82阅读模式
英文:

How to scan jsonb objects from Postgresql to golang structs

问题

首先,我尝试了过去与我的问题相关的stackoverflow答案中的解决方案,但没有任何有效的方法,这就是为什么我将其作为一个单独的问题提出的原因。

我在golang中有两个结构体:

type otherPayments struct {
	DebitTo     int    `json:"debit_To" binding:"required"`
	CreditFrom  int    `json:"credit_from" binding:"required"`
	OverallType string `json:"overall_type" binding:"required"`
}

type advanceAndRoomPayment struct {
	PmID        int    `json:"pm_id" binding:"required"`        //Payment method id
	PmName      string `json:"pm_name" binding:"required"`      //Payment method name
	DebitTo     int    `json:"debit_To" binding:"required"`     //The ledger to debit from
	CreditFrom  int    `json:"credit_from" binding:"required"`  //The ledger to credit from
	OverallType string `json:"overall_type" binding:"required"` //Overall transaction type
}

我在booking_settings postgresql表中有5个SQL列:

  • initial列,类型为otherPaymentsJSONB
  • cancellation列,类型为otherPaymentsJSONB
  • updation列,类型为otherPaymentsJSONB
  • advance_payment列,类型为advanceAndRoomPaymentJSONB []
  • room_payment列,类型为advanceAndRoomPaymentJSONB []

SELECT查询如下:

SELECT initial, cancellation, updation, advance_payment, room_payment FROM booking_settings WHERE hotel_id = $1

我正在使用的sql包是https://jmoiron.github.io/sqlx/

我试图将上述列扫描到它们相应的结构变量中,到目前为止,我只能扫描initial,cancellation和updation,但无法扫描JSONB []advance_payment和room_payment

非常感谢任何帮助,谢谢。

英文:

Firstly, I've tried solutions from past stackoverflow answers whose questions were related to mine, and nothing has worked, that's why i am asking it as a separate question.

I have two structs in golang

type otherPayments struct {
	DebitTo     int    `json:"debit_To" binding:"required"`
	CreditFrom  int    `json:"credit_from" binding:"required"`
	OverallType string `json:"overall_type" binding:"required"`
}

type advanceAndRoomPayment struct {
	PmID        int    `json:"pm_id" binding:"required"`        //Payment method id
	PmName      string `json:"pm_name" binding:"required"`      //Payment method name
	DebitTo     int    `json:"debit_To" binding:"required"`     //The ledger to debit from
	CreditFrom  int    `json:"credit_from" binding:"required"`  //The ledger to credit from
	OverallType string `json:"overall_type" binding:"required"` //Overall transaction type
}

And i have 5 SQL columns within my booking_settings postgresql table

  • initial column, type = otherPayments, JSONB
  • cancellation, type = otherPayments, JSONB
  • updation, type = otherPayments, JSONB
  • advance_payment type = advanceAndRoomPayment, JSONB []
  • room_payment, type = advanceAndRoomPayment, JSONB []

The SELECT query is as follows

SELECT initial, cancellation, updation advance_payment, room_payment FROM booking_settings WHERE hotel_id = $1

The sql package i am using is https://jmoiron.github.io/sqlx/

I am trying to scan above columns into their appropriate struct vars, so far i could only manage to scan initial, cancellation and updation but not the JSONB [] advance_payment and room_payment

Any help is really appreciated, thank you

答案1

得分: 1

只是以防万一你不知道,jsonb[]PostgreSQL数组类型,其元素类型是jsonb。它不是"json数组"类型。

如果你想在列中存储JSON数据,无论你期望该数据包含标量、对象还是数组 JSON值,都应该使用json/jsonb类型。

所以,除非你选择了特定的用例来使用PostgreSQL数组类型,否则最好将列的类型从jsonb[]更改为jsonb


如果你不能或不想更改列类型,那么你仍然可以在SELECT查询中将PostgreSQL数组转换为JSON数组,然后在你自定义的Go sql.Scanner实现中使用json.Unmarshal来解码数据库数据。

SELECT to_jsonb(advance_payment) FROM booking_settings WHERE hotel_id = $1
-- 或者
SELECT array_to_json(advance_payment)::jsonb FROM booking_settings WHERE hotel_id = $1
type advanceAndRoomPaymentList []advanceAndRoomPayment

func (ls *advanceAndRoomPaymentList) Scan(src any) error {
    var data []byte
    switch v := src.(type) {
    case string:
        data = []byte(v)
    case []byte:
        data = v
    }
    return json.Unmarshal(data, ls)
}

如果你有很多查询引用了PostgreSQL数组列,并且你不想更新每一个查询来进行转换,你可以自己解析PostgreSQL数组,然后解组各个元素,或者将这个工作委托给第三方实现。

这是一个使用pq.GenericArray未经测试示例:

// 我没有测试过下面的代码,但我认为它应该可以工作,
// 如果不能工作,那么可能需要在某些地方进行小的调整...
type advanceAndRoomPaymentList []advanceAndRoomPayment

func (ls *advanceAndRoomPaymentList) Scan(src any) error {
    return pq.GenericArray{ls}.Scan(src)
}

// 为切片的元素类型实现Scanner
func (a *advanceAndRoomPayment) Scan(src any) error {
    var data []byte
    switch v := src.(type) {
    case string:
        data = []byte(v)
    case []byte:
        data = v
    }
    return json.Unmarshal(data, a)
}

如果你想自己解析PostgreSQL数组,那么你需要了解用于表示这种数组的语法。你可以在这里找到相关的文档here

> 数组值的外部文本表示由根据数组元素类型的I/O转换规则解释的项组成,以及指示数组结构的装饰。装饰由花括号({和})包围数组值以及相邻项之间的分隔符字符组成。分隔符字符通常是逗号(,),但也可以是其他字符:它由数组元素类型的typdelim设置确定。在PostgreSQL分发的所有标准数据类型中,除了类型box使用分号(;)外,都使用逗号。

所以,例如,如果你有一个包含json对象、json数组、json字符串和json布尔值的pg数组,并且你选择了它,那么将传递给sql.Scanner实现的数组表示将类似于:

{"{\"foo\": \"bar\"}","[\"foo\", \"bar\"]","\"foo bar\"",true}
英文:

Just in case you're not aware, jsonb[] is a PostgreSQL Array type whose element type is jsonb. It is NOT a "json array" type.

If you want to store JSON data in a column you should use the json/jsonb types regardless of whether you expect that data to contain a scalar, an object, or an array JSON value.

So, unless you chose the PostgreSQL Array type with some specific use-case in mind, it may be better if you just change the column's type from jsonb[] to jsonb.


If you can't, or don't want to, change the column type, then you can still convert the PostgreSQL array into a JSON array within the SELECT query, and then, in your custom Go sql.Scanner implementation, use json.Unmarshal to decode the db data.

SELECT to_jsonb(advance_payment) FROM booking_settings WHERE hotel_id = $1
-- or
SELECT array_to_json(advance_payment)::jsonb FROM booking_settings WHERE hotel_id = $1
type advanceAndRoomPaymentList []advanceAndRoomPayment

func (ls *advanceAndRoomPaymentList) Scan(src any) error {
    var data []byte
    switch v := src.(type) {
    case string:
        data = []byte(v)
    case []byte:
        data = v
    }
    return json.Unmarshal(data, ls)
}

If you have many queries referencing the PostgreSQL array columns and you don't want to update each and every one to do the conversion, you can either parse the PostgreSQL Array yourself and then unmarshal the individual elements, or you can delegate that work to some 3rd party implementation.

Here's an untested example using pq.GenericArray:

// I haven't tested the following but I'd assume it ought to work,
// if not, then perhaps maybe small tweaks are needed here and there...
type advanceAndRoomPaymentList []advanceAndRoomPayment

func (ls *advanceAndRoomPaymentList) Scan(src any) error {
    return pq.GenericArray{ls}.Scan(src)
}

// implement Scanner for the element type of the slice
func (a *advanceAndRoomPayment) Scan(src any) error {
    var data []byte
    switch v := src.(type) {
    case string:
        data = []byte(v)
    case []byte:
        data = v
    }
    return json.Unmarshal(data, a)
}

If you want to do the parsing of the PostgreSQL Array yourself then you'll need to understand the syntax used to represent such arrays. You can find the relevant docs here:

> The external text representation of an array value consists of items
> that are interpreted according to the I/O conversion rules for the
> array's element type, plus decoration that indicates the array
> structure. The decoration consists of curly braces ({ and }) around
> the array value plus delimiter characters between adjacent items. The
> delimiter character is usually a comma (,) but can be something else:
> it is determined by the typdelim setting for the array's element type.
> Among the standard data types provided in the PostgreSQL distribution,
> all use a comma, except for type box, which uses a semicolon (;).

So, for example, if you have pg-array that contains a json-object, a json-array, a json-string, and a json-bool and you select that, then the array representation that will be passed to the sql.Scanner implementation will look something like:

{"{\"foo\": \"bar\"}","[\"foo\", \"bar\"]","\"foo bar\"",true}

huangapple
  • 本文由 发表于 2022年9月5日 16:19:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/73606458.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定