Search JSONB Data GORM & PostgreSQL

Question

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

假设表products有包含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"}]'

我已经尝试了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函数来检查它，或者您可以使用.Raw和.Scan来创建原始查询来做GORM尚未支持的事情。

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

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