英文:
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
列,类型为otherPayments
,JSONB
cancellation
列,类型为otherPayments
,JSONB
updation
列,类型为otherPayments
,JSONB
advance_payment
列,类型为advanceAndRoomPayment
,JSONB []
room_payment
列,类型为advanceAndRoomPayment
,JSONB []
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}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论