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, Dec 12 '21 at 19:04
@sberry got an empty array but i should receive at least one column from table — rejs, Dec 12 '21 at 19:17

score 3 · Accepted Answer · answered Dec 13 '21 at 07:13

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?

love you man! your sample is gold, thanks! – hkcoyant Oct 25 '22 at 01:44 — hkcoyant, Oct 25 '22 at 01:44

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

1 Answers1