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

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

CodePudding user response：

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, ",")   ")"

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?