搜索 JSONB 数据 GORM 和 PostgreSQL

Question

如何访问 JSONB 数据类型并使用 GORM 搜索嵌套在其中的数据？假设表产品有包含 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子句的值的单引号改成双引号呢？

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函数检查它，或者你可以使用.Rawwith创建原始查询.Scan来做一些 GORM 还不支持的事情。

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

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