英文:
Search JSONB Data GORM & PostgreSQL
问题
如何使用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子句的单引号更改为双引号？
英文:
How to access JSONB data type and search a data that nested inside it using GORM?
Lets say the table products has info column that contain the JSONB type, like below:
{
"ID": 1,
"NAME": "Product A",
"INFO": {
"DESCRIPTION": "lorem ipsum",
"BUYERS": [
{
"ID": 1,
"NAME": "John Doe"
},
{
"ID": 2,
"NAME": "Jane Doe"
}
]
}
}
From the JSONB that stored in the PostgreSQL I want search the product by the buyers name if any of them are match.
So the PostgreSQL query that worked would be:
SELECT * FROM products WHERE info -> 'buyers' @> '[{"name": "Jane Doe"}]'
I have tried with GORM but it doesn't work:
result = db.Where("info-> 'buyers' @> '[{\"name\": ?}]'", request.body.name).Find(&products)
Where I got error and the SQL query output like below:
SELECT * FROM "products" WHERE info -> 'buyers' @> '[{"name": 'Jane Doe'}]'
ERROR: invalid input syntax for type json (SQLSTATE 22P02)
It seems the GORM query builder uses the string type with single quote instead of double quote so that cause the error of the JSONB search it should be "name": "Jane Doe" not "name": 'Jane Doe'
Therefore, how to change the single quote into double quote of the where clause value?
答案1
得分: 1
有时候在进行复杂查询或者一些尚未支持的操作时，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
英文:
Sometimes GORM generate unwanted SQL when doing complex query or something has not supported yet. To make sure GORM generate the right SQL for you, you can check it by using .ToSQL function or you can create raw query by using .Raw with .Scan to do something that GORM has not supported yet.
db.Raw("SELECT * FROM products WHERE info -> 'buyers' @> '[{\"name\": \"Jane Doe\"}]'").Scan(&result)
答案2
得分: 1
在SQL中构建JSON字符串？
result = db.Where("info -> 'buyers' @> '[{\"name\": \"" || ? || "\"}]'", request.body.name).Find(&products)
这将创建以下SQL语句：
SELECT * FROM products WHERE info -> 'buyers' @> '[{"name": "' || 'Jane Doe' || '"}]'
英文:
Build the json string inside the SQL?
result = db.Where("info -> 'buyers' @> '[{\"name\": \"' || ? || '\"}]'", request.body.name).Find(&products)
Which would create the SQL...
SELECT * FROM products WHERE info -> 'buyers' @> '[{"name": "' || 'Jane Doe' || '"}]'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论