Search code examples
arraysdatabasepostgresqlgogo-gorm

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

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:

Image

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

Solution

  • 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?