googleapis / go-sql-spanner

Google Cloud Spanner driver for Go's database/sql package.
Apache License 2.0
104 stars 24 forks source link

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

Open egonelbre opened 2 months ago

egonelbre commented 2 months ago

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 commented 2 months ago

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.