英文:
How to create query that checks if array contains value? golang gorm
问题
这是模型的样子:
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"`
}
这是postgresql列中contributors值的样子:
如何查询包含20的contributors数组?我尝试像这样做:database.DB.Where("contributors IN ?", 20).Find(&contBoards)
,但是出现错误:ERROR: syntax error at or near "$1" (SQLSTATE 42601)
请问有什么想法或选项吗?
附注:使用gorm和postgresql。
英文:
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
答案1
得分: 3
你在WHERE
子句中使用IN
运算符来检查一个值是否与值列表中的任何值匹配。
IN
需要一个明确的值列表(或子查询)。
我为你的情况创建了一个示例场景,如下所示:
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
与数组一起使用。如果你已经有了值列表的数组,这将非常有用。
使用ANY
运算符,你可以搜索一个值。
select * from table_name where value = ANY(contributors);
如果你想搜索多个值,可以使用@>
运算符。
@>
是“包含”运算符。
定义如下的几种数据类型:
数组:http://www.postgresql.org/docs/current/static/functions-array.html
范围类型:http://www.postgresql.org/docs/current/static/functions-range.html
几何类型:http://www.postgresql.org/docs/current/static/functions-geometry.html
JSON(和JSONB):http://www.postgresql.org/docs/current/static/functions-json.html
为了更好地理解,你可以参考这个链接:https://stackoverflow.com/questions/39643454/postgres-check-if-array-field-contains-value
英文:
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 : https://stackoverflow.com/questions/39643454/postgres-check-if-array-field-contains-value
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论