搜索JSONB数据GORM & PostgreSQL

Question

如何使用GORM访问JSONB数据类型并搜索其中嵌套的数据？

让我们假设表产品有包含JSONB类型的info列，如下所示：

{
 "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"}]'

我和戈姆试过了，但没有用：

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子句值的双引号？

Answer 1

在SQL中构建json字符串？

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

这将创建SQL..。

SELECT * FROM products WHERE info -> 'buyers' @> '[{"name": "' || 'Jane Doe' || '"}]'

Answer 2

有时，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