Query JSONB columns with GORM for operator in

Question

I have a table like below

INSERT INTO switches VALUES ('33', 60, jsonb_build_object('IDs',jsonb_build_array('11', '2'),'ID', '33', 'Name', 'switch1'));

I have a struct/model

type switches struct {
    ID         string `gorm:"primary_key; unique; json:id"`
    Generation uint32 `gorm:"type:integer; column:generation;" json:"generation" `
    // Additional gorm type jsonb set, if not present causes field to be bytea
    SwitchAttrs []byte `sql:"type:jsonb; not null; column:Switch_attrs;" gorm:"type:jsonb; json:switchAttrs"`
}

I can query in postgres

SELECT * FROM switches WHERE switch_attrs->'IDs' ? '2' 
id | generation  |       data
----+-------+------------------
33 | 60 | {"IDs": ["33","2"] }

How do I construct a query on the jsonB column for a particular key(s)? I was not able to find any documentation for using model objects to query which explains how to use "in" operator. I understand its possible to do with raw query, but wanted to see how it can be done using model object. I am trying to make queries like below but it fails :(

db = db.Where("Switch_attrs->'IDs' ?", "2").Find(&switches)

OR

db = db.Where("Switch_attrs->'IDs' ?",  []string{"2"}).Find(&switches)

OR

db = db.Where("Switch_attrs->'IDs' IN ?", []string{"2"}).Find(&switches)

OR

db = db.Where("Switch_attrs->>'IDs' ?",  "2").Find(&switches) . Note that i am querying as switch_attrs->>'IDs' . i expect the o/p as text and hence passing the value as "2".

for the last query i keep getting error as

"Severity": "ERROR", "Code": "42601", "Message": "syntax error at or near "$1"",

user_12746958 · Answer

Redefine your model as below

type switches struct {
    ID         string `gorm:"primary_key; unique; json:id"`
    Generation uint32 `gorm:"type:integer; column:generation;" json:"generation" `
    // Additional gorm type jsonb set, if not present causes field to be bytea
    SwitchAttrs SwitchAttr `sql:"type:jsonb; not null; column:Switch_attrs;" gorm:"type:jsonb; json:switchAttrs"`
}

Add this for SwitchAttr

type SwitchAttr struct {
    data struct {
        ID   *int    `json:"id"`
        Generation *string `json:"generation"`
        IDs []string `json:"IDs"`
    } `json:"data"`
}

// Column JSONB field 
func (a SwitchAttr) Value() (driver.Value, error) {
    return json.Marshal(a)
}

// simply decodes a JSON-encoded value into the struct fields.
func (a *SwitchAttr) Scan(value interface{}) error {
    b, ok := value.([]byte)
    if !ok {
        return errors.New("type assertion to []byte failed")
    }

    return json.Unmarshal(b, &a)
}

For Query,

db = db.Where("switchAttrs @> '{"data":{"IDs":}}'::jsonb").Find(&switches)

Query JSONB columns with GORM for operator in

1 Answers