Q: scanning JSONB from []byte

[kmansoft]

Hi,

I'm considering converting a project from sql/db to pgx.

I store a lot of JSON types in my database using JSONB column type.

My Go structs have type converters like these:

func (s *Struct2) Value() (driver.Value, error) {
	json := jsoniter.ConfigDefault
	return json.Marshal(s)
}

func (s *Struct2) Scan(value interface{}) error {
	if value == nil {
		...
		return nil
	}

	b, ok := value.([]byte)
	if !ok {
		return errors.New("type assertion to []byte] failed") <<< --- this fails with pgx
	}

	json := jsoniter.ConfigDefault
	return json.Unmarshal(b, s)
}

The scanning fails with PGX because the data type passed into Scan is a string not []byte.

Rather than updating my code everywhere (error prone) I'd like to be able to scan JSONB columns as []byte - is this possible?

I've found the following code in scanPlanSQLScanner, any way to make it execute the branch for BinaryFormatCode?

func (plan *scanPlanSQLScanner) Scan(src []byte, dst any) error {
	scanner := dst.(sql.Scanner)
	if src == nil {
		// This is necessary because interface value []byte:nil does not equal nil:nil for the binary format path and the
		// text format path would be converted to empty string.
		return scanner.Scan(nil)
	} else if plan.formatCode == BinaryFormatCode {
		return scanner.Scan(src)  <<<<< ---- Can I have this
	} else {
		return scanner.Scan(string(src)) <<<<<<< --- Not this?
	}
}

I would even argue that maybe this is a bug and JSONB columns should be scanned as []byte by default - but even if I have to call some sort of configuration function somehow, without the change to default behavior, that would be OK too.

PS - I know that pgx can do JSON conversion automatically but there are some subtle differences and I'd like to continue with my custom conversion functions.

[jackc]

Part of the reason for the string instead of []byte is that is the one way for the scanner to know whether the value is in the PostgreSQL text format or the PostgreSQL binary format. I don't think it is actually used at this point, but that distinction was made to allow future optimization where the binary format could be enabled for more types.

I don't recommend this, but if you really need this alternative behavior, then you could copy json.go into your own project, make the changes, and then register the type on the pgx.Conn. In that way you can basically change anything you want about the pgx type system.