使用 GORM 和 PostgreSQL 进行 JSONB 数据搜索

大家好，我们又见面了啊~本文《使用 GORM 和 PostgreSQL 进行 JSONB 数据搜索》的内容中将会涉及到等等。如果你正在学习Golang相关知识，欢迎关注我，以后会给大家带来更多Golang相关文章，希望我们能一起进步！下面就开始本文的正式内容~

如何使用 gorm 访问 jsonb 数据类型并搜索嵌套在其中的数据？

假设表 products 具有包含 jsonb 类型的信息列，如下所示：

{
 "id": 1,
 "name": "product a",
 "info": {
  "description": "lorem ipsum",
  "buyers": [
   {
   "id": 1,
   "name": "john doe"
   },
   {
   "id": 2,
   "name": "jane doe"
   }
  ]
 }
}

从存储在 postgresql 中的 jsonb 中，我想通过买家姓名搜索产品（如果其中任何一个匹配）。

所以有效的 postgresql 查询是：

select * from products where info -> 'buyers' @> '[{"name": "jane doe"}]'

我尝试过 gorm，但不起作用：

result = db.where("info-> 'buyers' @> '[{\"name\": ?}]'", request.body.name).find(&products)

我遇到错误的地方和 sql 查询输出如下：

SELECT * FROM "products" WHERE info -> 'buyers' @> '[{"name": 'Jane Doe'}]'
ERROR: invalid input syntax for type json (SQLSTATE 22P02)

看来 gorm 查询生成器使用带单引号而不是双引号的字符串类型，因此导致 jsonb 搜索错误，它应该是 "name": "jane doe" 而不是 "name": 'jane doe'

那么如何将where子句值的单引号改为双引号呢？

正确答案

在 sql 中构建 json 字符串？

result = db.where("info -> 'buyers' @> '[{\"name\": \"' || ? || '\"}]'", request.body.name).find(&products)

这将创建 sql...

select * from products where info -> 'buyers' @> '[{"name": "' || 'jane doe' || '"}]'

有时，gorm 在执行复杂查询或尚不支持的操作时会生成不需要的 sql。为了确保 gorm 为您生成正确的 sql，您可以使用 .tosql 函数进行检查，也可以使用 .raw 和 .scan 来创建原始查询来执行 gorm 尚不支持的操作。

db.Raw("SELECT * FROM products WHERE info -> 'buyers' @> '[{\"name\": \"Jane Doe\"}]'").Scan(&result)

https://gorm.io/docs/sql_builder.html

以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于Golang的相关知识，也可关注golang学习网公众号。