How to scan jsonb objects from Postgresql to golang structs

Question

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

Answer 1

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}