How to scan jsonb objects from Postgresql to golang structs

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}