搜索 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