Search JSONB Data GORM & PostgreSQL-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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)

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