Unable to scan JSON data into `[]byte` or `json.RawMessage`

[egonelbre]

Is your feature request related to a problem? Please describe.

Currently it's not possible to scan the JSON column into []byte or json.RawMessage. This means that there's either going to be an additional unnecessary roundtrip due to spanner.NullJSON.

Small example:

	rows, err := db.QueryContext(ctx, "SELECT (JSON '{}')")
	if err != nil {
		return fmt.Errorf("failed to execute query: %v", err)
	}
	defer rows.Close()

	var msg []byte
	for rows.Next() {
		if err := rows.Scan(&msg); err != nil {
			return fmt.Errorf("failed to scan row values: %v", err)
		}
		fmt.Printf("%s\n", msg)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("failed to execute query: %v", err)
	}

Fails with:

2024/08/21 19:02:37 failed to scan row values: sql: Scan error on column index 0, name "": unsupported Scan, storing driver.Value type spanner.NullJSON into type *[]uint8

Describe the solution you'd like

Support scanning into json.RawMessage, []byte.

It probably would be useful to support scanning directly into spanner.GenericColumnValue as well -- letting the user take control over the rows.Next logic.

Describe alternatives you've considered

It would be possible the make the query convert the json to a bytes value, which would complicate the queries themselves.

[egonelbre]

After looking at the code, the implementation might be somewhat difficult due to how database/sql works.

However, one approach would be. First make *rows.Next return []byte instead of spanner.NullJSON -- this makes sure that it can be scanned into []byte and json.RawMessage. Before that, however implement Scan method for spanner.NullJSON, this will make sure that previous code scanning into spanner.NullJSON will continue to work.

[egonelbre]

Seems that this would require golang/go#67546 to be added.