How to create query that checks if array contains value? golang gorm

Question

This is how model looks:

type Board struct {
    Id     uint `gorm:"primaryKey;autoIncrement;unique" json:"id"`
    Owner  uint `json:"owner"`
    Name string `json:"name"`
    Contributors datatypes.JSON `gorm:"type:jsonb" json:"contributors"`
    GeneratedLink string `gorm:"default:''" json:"generated_link"`
    Todos datatypes.JSON `gorm:"type:jsonb" json:"todos"`
}

This is how contributors value looks in the postgresql column:

and how to make query that checks that contributors array contains for example 20? i tried to do like this: database.DB.Where("contributors IN ?", 20).Find(&contBoards) but got error: ERROR: syntax error at or near "$1" (SQLSTATE 42601)

Please any ideas, any options. P.S using gorm, postgresql

IN takes a slice as an argument. Try something like ("contributors IN ?", []int{20}) — sberry
– sberry, Commented Dec 12, 2021 at 19:04
@sberry got an empty array but i should receive at least one column from table — rejs
– rejs, Commented Dec 12, 2021 at 19:17

Ashutosh Singh · Accepted Answer · 2021-12-13 07:13:08Z

You use IN operator in the WHERE clause to check if a value matches any value in a list of values.

IN expects an explicit list of values (or a subquery).

I have created a sample scenario for your case as follows :

contributors := []int{20, 25, 27}

var tmp []string

for _, v := range contributors {
    tmp = append(tmp, fmt.Sprint(v))
}

query := "SELECT * from table_name where contributors in (" + strings.Join(tmp, ",") + ")"

OR

ANY works with arrays. This can be useful if you have the list of values already in an array.

With ANY operator you can search for only one value.

select * from table_name where value = ANY(contributors);

If you want to search multiple values, you can use @> operator.

@> is the "contains" operator.

Defined for several data types as follows :

arrays: http://www.postgresql.org/docs/current/static/functions-array.html

range types: http://www.postgresql.org/docs/current/static/functions-range.html

geometric types: http://www.postgresql.org/docs/current/static/functions-geometry.html

JSON (and JSONB): http://www.postgresql.org/docs/current/static/functions-json.html

For better understanding you can refer this link : Postgres: check if array field contains value?

smilingky · Accepted Answer · 2023-11-07 15:39:04Z

1

there are examples in gorm doc

db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)

answered Nov 7, 2023 at 15:39

smilingky

3993 silver badges4 bronze badges

Collectives™ on Stack Overflow

How to create query that checks if array contains value? golang gorm

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related