gorm postgres查询JSON数组中的元素

huangapple go评论85阅读模式
英文:

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"}}

huangapple
  • 本文由 发表于 2023年4月17日 22:30:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/76036257.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定