json type in postgresql

[nerdzeu]

Hi, can you please suggest me the correct way to declare a field in a struct that is of json type in postrgresql?

Thank you

[jinzhu]

Hi @nerdzeu

Sorry, I haven't used the json type, does https://github.com/lib/pq/ support it with golang's generic interface? If yes, then it would be easy to support it with gorm.

[nerdzeu]

Hi @jinzhu and thank you for the fast reply.

I'll check in lib/pq as soon as possible (now it's 3 am in Italy, I'll do tomorrow) and if I find a solution I will add a working sample in the README.md of this (great) proect.

Anyway, I have the same necessity for a filed of postrgres type 'inet' (used to store users ip addres in ipv4 or ipv6 format). Maybe declaring the field of type net.IP can be a valid choice?

[jinzhu]

Hi @nerdzeu

You could set the sql type like this:

type User struct {
    Ip      string  `sql:"type:inet;"`
}

I am not sure if pg could scan net.IP into database or not (did a quick search, haven't find any information about this), if it doesn't support, you need to write a scanner. (something like this commit https://github.com/jinzhu/gorm/commit/663c06cfb1bc0bb9d0135185d41180517fbb2ddb)

Have a good night!

[1xch]

I have an existing db I'm learning with; this seems to work:

type Node struct{
    Raw      string `sql:"json"`
}

[jinzhu]

Great, going to close this issue now. I think any database type could be supported with Scanner.

[collinglass]

the example @thrisp used is not a valid example of this working. JSON/JSONB fields are used for structs , maps, slices and not as a string. I think this should be looked at more.

The way I do it in my code using raw golang sql package:

tagsJSON, err := json.Marshal(moment.Tags)
if err != nil {
    return context.NewErrorJSON(ds.Col(), err)
}

// Update the database
err = stmt.QueryRow(
    moment.Id,
    ...
    tagsJSON,
}

[rookie7799]

+1

[wenweih]

Like that:

type BtcVin struct {
    gorm.Model
    Txid             string `gorm:"type:varchar(100);not null"`
    vout             int32
    Sequence         int32
    Witness          pq.StringArray  `gorm:"type:varchar(200)[]"`
    ScriptSig        json.RawMessage `sql:"type:json"`
    BtcTransactionID int             `gorm:"index"`
}

func GetBtcVin(vin btcjson.VinM) *LtcVin {
    scriptSig, _ := json.Marshal(vin.ScriptSig)

    mTxVin := BtcVin{
        Txid:      vin.Txid,
        vout:      int32(vin.Vout),
        Sequence:  int32(vin.Sequence),
        Witness:   vin.Witness,
        ScriptSig: scriptSig,
    }

    return &mTxVin
}

ScriptSig in BtcVin struct is a json column , and json.RawMessage is an []byte datatype.

We should note that, When new a BtcVin strcut object, the ScriptSig field should full fill a []byte object.

As json.Marshal(v interface{}) take any type object and return []byte err, so we can get scriptSig, _ := json.Marshal(vin.ScriptSig) and assign the scriptSig to ScriptSig field.

Argument value in Marshal method(vin.ScriptSig) is an pointer to a struct object