Postgres JSON support

[fbjork]

Is it possible to store JSON blobs in Postgres with gorp? Any examples?

[nelsam]

You can implement "database/sql".Scanner and "database/sql/driver".Valuer to create your own json type. Value() should spit out the json string; Scan() should take the json string from the DB and unmarshal it to your type.

[GeertJohan]

@fbjork does nelsam's reply answer your question?

[jeromenerf]

TL;DR:

• best pratice: TypeConverter or Value() / Scan() implementation in gorp?
• how to deal with postgres specific json and jsonb features? using gorp?
• what about documenting this trendy use case more thoroughly?

The documentation and tests presents different possible approaches to achieve this:

1. "TypeConverter": https://github.com/go-gorp/gorp/blob/master/gorp_test.go#L285
2. Valuer / Scan : https://github.com/go-gorp/gorp/blob/master/gorp_test.go#L127 although in this case, its just a matter of storing the Person.Id in the Invoice struct rather than the whole attributes.

In the first example, the Person struct is being converted in and out the database, using a string type transport (called holder) containing its JSON representation.

However:

• obviously, json encoding constraints apply, map[int]xxx is not supported for instance
• the column type in the database is the dialect specific equivalent of "string" (text ...)
• if using postgres, the column is not set to json of jsonb, making it less interesting than it could be (see http://www.postgresql.org/docs/9.4/static/datatype-json.html)
• the type conversion might not work if the json-converted type is deeply nested in a join query

Postgres JSON features are getting better and more popular with each release, from 9.3 to the latest 9.5. In addition to being able to return a traditional dataset as a JSON representation using row_as_json, json_obect_agg, ... functions, it is also possible to store JSON as is, and yet be able to perform performant queries, such as SELECT Id, PersonJSON->>'Fname' as Name FROM TypeConversionExample if the PersonJSON column was set as jsonb.

@jmoiron 's "sqlx" features a JSONText type to deal with this (https://github.com/jmoiron/sqlx/blob/master/types/types.go), with some upstream potential bugs however (see golang/go#13905) and the same question as this one in its own issues: jmoiron/sqlx#133

As a conclusion, a possible approach could be:

1. write structs People and TypeConversionExample embedding People
2. write typeconverters or value/scan functions, to convert People via a json.RawMessage derived struct
3. create tables, either manually with SQL or via gorp then manually ALTER to specify the person column type as json(b)
4. profit!

[eBurns]

Was just working through the same issue - didn't find any more recent info than this thread unfortunately. Really good ideas here but I just hated that I was required to go back and alter the table to set the column type to jsonb.

My solution was to use embedding to extend gorp.PostgresDialect as follows:

// PostgresJSONDialect adds the JSON data types to the PostgrestDialect
type PostgresJSONDialect struct {
    gorp.PostgresDialect
}

// ToSqlType returns the SQL column type for the given Go type
func (d PostgresJSONDialect) ToSqlType(val reflect.Type, maxsize int, isAutoIncr bool) string {
    // Force JSONText to use sqlType: JSONB
    if val == reflect.TypeOf(JSONText{}) {
        return "JSONB"
    }
    return d.PostgresDialect.ToSqlType(val, maxsize, isAutoIncr)
}

Very clean and I brought the JSONText struct over from @jmoiron 's "sqlx". Haven't fully tested with that struct but once I get the other side of this project working, I'll decide if it meets the need or if I need to roll my own. But - I love that this is a minor passthrough that lets

err = Dbmap.CreateTablesIfNotExists()

do what it needs to do without any post-creation cleanup.

[YanhaoYang]

I just found this article, Handling JSONB in Go Structs. Hope it is helpful if you are looking for a solution.