英文:
gorm postgres query json array for element
问题
在我的 Golang 项目中,我使用 GORM 连接到 PostgreSQL 数据库,并且我有一个包含以下 JSON 数据的 attributes 列:
{"email": ["eee@ccc.cc", "rrr@bbb.cc", "ttt@ccc.zz"], "mail_folder": "some_folder"}
{"email": ["zzz@ccc.cc", "aaa@bbb.cc", "bbb@ccc.zz"], "mail_folder": "some_folder"}
所以我需要获取包含 email 为 eee@ccc.cc
的记录,它是第一个记录。我可以在 SQL 编辑器中使用以下查询语句提取它:
select * from authors a where attributes @> '{"email": ["eee@ccc.cc"]}';
但是在 GORM 中,我一直遇到错误的 JSON 语法错误等问题。我尝试使用 Raw() 查询或者使用以下方式:
Where(fmt.Sprintf("attributes ->> 'email' = '[\"%v\"]'", email))。
但是都没有成功。有什么办法可以解决这个问题吗?谢谢。
英文:
in my golang project I use postgres with gorm and I have an attributes column with the following json:
{"email": ["eee@ccc.cc", "rrr@bbb.cc", "ttt@ccc.zz"], "mail_folder": "some_folder"}
{"email": ["zzz@ccc.cc", "aaa@bbb.cc", "bbb@ccc.zz"], "mail_folder": "some_folder"}
So I need to get a record which contains email eee@ccc.cc
which is the first one. I can extract it with plain sql in sql editor with the following query:
select * from authors a where attributes @> '{"email": ["eee@ccc.cc"]}';
but in gorm I keep getting wrong json syntax errors etc. I tried to do it with Raw() query or with
Where(fmt.Sprintf("attributes ->> 'email' = '[\"%v\"]'", email)).
but it did not work either. Any ideas how to fix it would be welcome. Thank you.
答案1
得分: 0
在postgreSQL中的Sampledb:
创建表authors
(
id serial,
dummy text,
attributes jsonb
);
插入数据到authors (dummy, attributes)
VALUES ('eee', '{
"email": [
"eee@ccc.cc",
"rrr@bbb.cc",
"ttt@ccc.zz"
],
"mail_folder": "some_folder"
}'),
('zzz', '{
"email": [
"zzz@ccc.cc",
"aaa@bbb.cc",
"bbb@ccc.zz"
],
"mail_folder": "some_folder"
}');
这段代码运行良好:
package main
import (
"fmt"
postgres2 "github.com/jinzhu/gorm/dialects/postgres"
"gorm.io/driver/postgres"
"gorm.io/gorm"
"log"
)
var (
dsn = "host=localhost user=postgres password=secret dbname=sampledb port=5432 sslmode=disable TimeZone=europe/istanbul"
)
type Author struct {
Id int `gorm:"primaryKey"`
Dummy string
Attributes postgres2.Jsonb `gorm:"type:jsonb;default:'{}'"`
}
var DB *gorm.DB
func main() {
DB = initDb()
listAuthors()
}
func listAuthors() {
var authors []Author
DB.Find(&authors, "attributes @> '{\"email\": [\"eee@ccc.cc\"]}'")
for _, a := range authors {
fmt.Printf("%d %s %s\n", a.Id, a.Dummy, a.Attributes)
}
}
func initDb() *gorm.DB {
db, err := gorm.Open(postgres.Open(dsn))
if err != nil {
log.Fatal("couldn't connect to db")
}
return db
}
对于示例数据的打印输出:
1 eee {{ "email": ["eee@ccc.cc", "rrr@bbb.cc", "ttt@ccc.zz"],
"mail_folder": "some_folder"}}
英文:
Sampledb in postgreSQL:
Create Table authors
(
id serial,
dummy text,
attributes jsonb
);
insert into authors (dummy, attributes)
VALUES ('eee', '{
"email": [
"eee@ccc.cc",
"rrr@bbb.cc",
"ttt@ccc.zz"
],
"mail_folder": "some_folder"
}'),
('zzz', '{
"email": [
"zzz@ccc.cc",
"aaa@bbb.cc",
"bbb@ccc.zz"
],
"mail_folder": "some_folder"
}');
This works fine:
package main
import (
"fmt"
postgres2 "github.com/jinzhu/gorm/dialects/postgres"
"gorm.io/driver/postgres"
"gorm.io/gorm"
"log"
)
var (
dsn = "host=localhost user=postgres password=secret dbname=sampledb port=5432 sslmode=disable TimeZone=europe/istanbul"
)
type Author struct {
Id int `gorm:"primaryKey"`
Dummy string
Attributes postgres2.Jsonb `gorm:"type:jsonb;default:'{}'"`
}
var DB *gorm.DB
func main() {
DB = initDb()
listAuthors()
}
func listAuthors() {
var authors []Author
DB.Find(&authors, "attributes @> '{\"email\": [\"eee@ccc.cc\"]}'")
for _, a := range authors {
fmt.Printf("%d %s %s\n", a.Id, a.Dummy, a.Attributes)
}
}
func initDb() *gorm.DB {
db, err := gorm.Open(postgres.Open(dsn))
if err != nil {
log.Fatal("couldn't connect to db")
}
return db
}
For the sample data prints:
> 1 eee {{"email": ["eee@ccc.cc", "rrr@bbb.cc", "ttt@ccc.zz"],
> "mail_folder": "some_folder"}}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论