How to handle JSONB with int64 within a map?

[joelpresence]

Hi,

First, thanks for this awesome library - we are using it in production and it works great! I really appreciate you making this available. :-)

I'm looking for guidance on how to handle int64 values in a jsonb Postgres column that stores a map. The problem is, values are marshaled by go as int64 when written to Postgres but are unmarshaled back as float64. As long as the original int64 can be encoded in 53 bits or fewer, there's no loss of precision. However, for values larger than 2^53, especially 64-bit ids, the loss of precision produces wrong results - go rounds off the int64 when it converts it back to a float 64 during unmarshaling from Postgres.

Example:

// Table is
// id: bigserial
// some_data: jsonb

// The field some_data may hold a Person or a Pet ...

type Person struct {
  PersonId int64
  Name string
}

type Pet struct {
  PetId int64
  Species string
}

p1 := Person{PersonId: 1499366092322157987, Name: "Joel"}

// p1 Encodes to JSONB as
//
// '{"PersonId": 1499366092322157987, "Name": "Joel"}'

// Now read the row just written with p1 back into p2
var someData interface{}

err := p.Pool.QueryRow("select some_data from some_table limit1").Scan(&someData)

// Now map someData back to a Person or a Pet as appropriate.
// The issue is that someData will be a map[string]interface{}
// and both someData["PersonId"] and someData["PetId"] will be float64 not int64.
// This is the flaw in go's json unmarshaling, decoding an integer into a float.

// p2 converted from someData will be:
// PersonId: 1499366092322158080
// Name: "Joel"

// Notice how the PersonId got rounded off by go's json unmarshaling and the meaningful value
// of the PersonId is lost - the original PersonId is 1499366092322157987 not the go-rounded value
// 1499366092322158080.  This PersonId is 60 bits, so it gets rounded because it can't fit in the mantissa of a float64 which reserves only 53 bits for the mantissa.

One way around this is:

var someData2 []byte
err = p.Pool.QueryRow("select some_data from some_table limit1").Scan(&someData2)


// [1:] is to skip the '\x01' byte at index zero which is returned by Postgres?
decoder := json.NewDecoder(bytes.NewReader(someData2[1:]))
decoder.UseNumber()
var p2v2 interface{}
decoder.Decode(&p2v2)

// Now p2v2["PersonId"] is the original, correct int64 value 1499366092322157987
// but stored as a json.Number.  However, when mapped back to a Person struct
// it comes back as an int64.

However, this feels fragile since I'm relying on byte 0 being the sentinel \x01 which could change I guess?

Any other ideas?

Note that the some_data field can stored different types of structs, so I can't unmarshal directly back to a struct. The table design can't be changed at this point.

Is there a better way to handle this?

[jackc]

That's an interesting problem. This stems from JavaScript where there was no distinction between integers and floats. Support for JSON numbers greater than 2^53 is implementation defined (https://tools.ietf.org/html/rfc7159#section-6). Technically, the Go decoder is conforming to the standard. Obviously, that doesn't help in your case.

There are a few potential solutions.

1. I haven't run into this exact issue with Go/PostgreSQL/pgx, but I have run into similar issues with passing big numbers in JSON/JS. I chose to treat all large numbers as strings and encode/decode at the endpoints. This has the advantage of guaranteeing that it is explicitly handled everywhere, rather than relying on the behavior of different encoders and decoders all behaving identically.
2. The '0x1' byte is the version for the binary encoding of JSONB. If you configure pgx to use the text protocol for JSONB then that byte will no longer be there -- so you wouldn't need to worry about that version byte.
3. A custom type could be implemented that implemented the proper pgx decoder interfaces and encapsulated the JSON decoder configuration (something like type BigNumJSON map[string]interface{} where BigNumJSON implements the appropriate interface for pgx v2orv3`)

I would probably lean toward 1 if you will have clients in multiple languages accessing this data and 3 if your only priority is Go.

[joelpresence]

Thanks @jackc !

I'm doing something 1 using go's Decoder.UseNumber:

// Read the JSONB into a *[]byte
// Then use the method DecodeJSONBMap below to decode into an interface

// Query into &mapBytes
var mapBytes []byte

// Decode into map[string]interface using DecodeJSONBMap
// Any numbers will remain as json.Number (alias for string)
var mapInterface interface{}

err := DecodeJSONBMap(mapBytes, &mapInterface)

// Use the mapstructure methods to map from map[string]interface{}
// to my own custom struct - mapstructure will map the json.Number into the
// ultimate struct type for each number, so int64 as json.Number get properly mapped
// to int64 on a struct without loss of precision.
//
// See https://github.com/mitchellh/mapstructure

// Here's my custom decoder:

func DecodeJSONBMap(data []byte, decodeInto interface{}) error {
	var decoder *json.Decoder
	if data[0] == []byte(jsonbByteMarker)[0] {
		// Postgres marks bytes with a version byte at index 0,
		// so remove this since it's not valid JSON.
		decoder = json.NewDecoder(bytes.NewReader(data[1:]))
	} else {
		// The caller might have already removed the version byte.
		decoder = json.NewDecoder(bytes.NewReader(data))
	}
	decoder.UseNumber()
	return decoder.Decode(decodeInto)
}

Would there be any possibility to push the Decoder options down into pgx so that it could use UseNumber as well?

[jackc]

How pgx handles JSON is overridable with custom types.. The exact details of that vary between v2 and v3.

In v3 you would basically just duplicate the entire pgtype/json.go file and replace the two spots json.Unmarshal is called with your own decoder. Then register your new type.

In v2 you would need a type that implements PgxScanner.