Query JSONB columns with GORM using model

user_10364572 0
Viewed 9396

I have a struct/model 

type User struct {
        gorm.Model
        Name string         `gorm:"unique;not null" json:"name"`
        Data postgres.Jsonb `json:"data"`
}

I can query in postgres

db=# select id,name,data from users where data @> '{"foo": "bar"}';
id | name  |       data
----+-------+------------------
6 | user01 | {"foo": "bar"}
7 | user02 | {"foo": "bar"}
8 | user03 | {"foo": "bar"}

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. I understand its possible to do with raw query, but wanted to see how it can be done using model object ie.

users := []model.User{}
db.Find(&users, map[string]interface{}{"foo": "bar"})

http://gorm.io/docs/dialects.html

http://gorm.io/docs/query.html

go-gorm
3 Answers

In your example you are not specifying which field the map will filter. Try

db.Find(&users, "data @> ?", map[string]interface{}{"foo": "bar"})
user_4156280

You make the query like this :

users := []model.User{}
db.Where("data ->> 'foo' = ?", "bar").Find(&users)
user_16204750

I don't know if this works for jsonp but datatypes worked for me with JSON saved as a string

import "gorm.io/datatypes"

db.Where(datatypes.JSONQuery("data").Equals("bar", "foo")).Find(&users)
user_22497250
Related