使用 GORM 在 PostgreSQL 中查询 JSON 数组的元素

对于一个Golang开发者来说，牢固扎实的基础是十分重要的，golang学习网就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《使用 GORM 在 PostgreSQL 中查询 JSON 数组的元素》，主要介绍了，希望对大家的知识积累有所帮助，快点收藏起来吧，否则需要时就找不到了！

在我的 golang 项目中，我将 postgres 与 gorm 结合使用，并且有一个包含以下 json 的属性列：

{"email": ["[email protected]", "[email protected]", "[email protected]"], "mail_folder": "some_folder"}
{"email": ["[email protected]", "[email protected]", "[email protected]"], "mail_folder": "some_folder"}

所以我需要获取包含电子邮件 [email protected] 的记录，这是第一个记录。我可以使用以下查询在 sql 编辑器中使用纯 sql 来提取它：

select * from authors a where attributes @> '{"email": ["[email protected]"]}';

但在 gorm 中，我不断收到错误的 json 语法错误等。我尝试使用 raw() 查询或使用

Where(fmt.Sprintf("attributes ->> 'email' = '[\"%v\"]'", email)).

但它也不起作用。任何如何修复它的想法都将受到欢迎。谢谢。

正确答案

postgresql 中的 sampledb：

create table authors
(
    id         serial,
    dummy      text,
    attributes jsonb
);

insert into authors (dummy, attributes)
values ('eee', '{
  "email": [
    "<a target='_blank'  href='https://www.17golang.com/gourl/?redirect=MDAwMDAwMDAwML57hpSHp6VpkrqbYLx2eayza4KafaOkbLS3zqSBrJvPsa5_0Ia6sWuR4Juaq6t9nq6ycZqKgG2svpXKqIBkrtu-i2LMmbrbrZuueqbGeYaeyYCkppKihqKu3LOijnmMlbN4cpSSt89pkqp5qLBkep6yo6Nkf42hpLLdyqKBrIXRsot-lpHdz3Y' rel='nofollow'>[email protected]</a>",
    "<a target='_blank'  href='https://www.17golang.com/gourl/?redirect=MDAwMDAwMDAwML57hpSHp6VpkrqbYLx2eayza4KafaOkbLS3zqSBrJvPsa5_0Ia6sWuR4Juaq6t9nq6ycZqKgG2svpXKqIBkrtu-i2LMmbrbrZuueqbGeYaeyYCkppKihqKu3LOijnmMlbN4cpSSt89pkqp5qLBkep6yo6Nkf42hpLLdyqKBrIXRsot-lpHdz3Y' rel='nofollow'>[email protected]</a>",
    "<a target='_blank'  href='https://www.17golang.com/gourl/?redirect=MDAwMDAwMDAwML57hpSHp6VpkrqbYLx2eayza4KafaOkbLS3zqSBrJvPsa5_0Ia6sWuR4Juaq6t9nq6ycZqKgG2svpXKqIBkrtu-i2LMmbrbrZuueqbGeYaeyYCkppKihqKu3LOijnmMlbN4cpSSt89pkqp5qLBkep6yo6Nkf42hpLLdyqKBrIXRsot-lpHdz3Y' rel='nofollow'>[email protected]</a>"
  ],
  "mail_folder": "some_folder"
}'),
       ('zzz', '{
         "email": [
           "<a target='_blank'  href='https://www.17golang.com/gourl/?redirect=MDAwMDAwMDAwML57hpSHp6VpkrqbYLx2eayza4KafaOkbLS3zqSBrJvPsa5_0Ia6sWuR4Juaq6t9nq6ycZqKgG2svpXKqIBkrtu-i2LMmbrbrZuueqbGeYaeyYCkppKihqKu3LOijnmMlbN4cpSSt89pkqp5qLBkep6yo6Nkf42hpLLdyqKBrIXRsot-lpHdz3Y' rel='nofollow'>[email protected]</a>",
           "<a target='_blank'  href='https://www.17golang.com/gourl/?redirect=MDAwMDAwMDAwML57hpSHp6VpkrqbYLx2eayza4KafaOkbLS3zqSBrJvPsa5_0Ia6sWuR4Juaq6t9nq6ycZqKgG2svpXKqIBkrtu-i2LMmbrbrZuueqbGeYaeyYCkppKihqKu3LOijnmMlbN4cpSSt89pkqp5qLBkep6yo6Nkf42hpLLdyqKBrIXRsot-lpHdz3Y' rel='nofollow'>[email protected]</a>",
           "<a target='_blank'  href='https://www.17golang.com/gourl/?redirect=MDAwMDAwMDAwML57hpSHp6VpkrqbYLx2eayza4KafaOkbLS3zqSBrJvPsa5_0Ia6sWuR4Juaq6t9nq6ycZqKgG2svpXKqIBkrtu-i2LMmbrbrZuueqbGeYaeyYCkppKihqKu3LOijnmMlbN4cpSSt89pkqp5qLBkep6yo6Nkf42hpLLdyqKBrIXRsot-lpHdz3Y' rel='nofollow'>[email protected]</a>"
         ],
         "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\": [\"<a target='_blank'  href='https://www.17golang.com/gourl/?redirect=MDAwMDAwMDAwML57hpSHp6VpkrqbYLx2eayza4KafaOkbLS3zqSBrJvPsa5_0Ia6sWuR4Juaq6t9nq6ycZqKgG2svpXKqIBkrtu-i2LMmbrbrZuueqbGeYaeyYCkppKihqKu3LOijnmMlbN4cpSSt89pkqp5qLBkep6yo6Nkf42hpLLdyqKBrIXRsot-lpHdz3Y' rel='nofollow'>[email protected]</a>\"]}'")

    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": ["[电子邮件受保护] ", "[电子邮件受保护]", "[电子邮件受保护]"], "mail_folder": "some_folder"}}

本篇关于《使用 GORM 在 PostgreSQL 中查询 JSON 数组的元素》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于Golang的相关知识，请关注golang学习网公众号！