json query with boolean argument fails

[andersryanc]

GORM Playground Link

go-gorm/playground#729

Description

When querying a property inside a json field with a bool argument, the query returns 0 rows. However, if you embed the bool into the where clause itself, you get 1 row. The strange thing is that the generated SQL shown by the debugger is identical.

Broken Example:

DB.Where("config->'enabled' = ?", true).Find(&results)

2024/04/30 11:20:10 /__REDACTED__/playground/main_test.go:108
[0.847ms] [rows:0] SELECT * FROM `accounts` WHERE config->'$.enabled' = true

Working Example:

DB.Where("config->'enabled' = true").Find(&results)

2024/04/30 11:20:10 /__REDACTED__/playground/main_test.go:108
[0.647ms] [rows:1] SELECT * FROM `accounts` WHERE config->'$.enabled' = true

I tried testing a variety of other variations as well, like using json_extract and the double arrow syntax, like config->>'$.enabled'. The same thing also happens with nested values, such as config->'$.foo.enabled' = true.

[seahm]

@andersryanc
I think in sql driver true/false=>1/0. DB.Where("config->'enabled'` = ?", true).Find(&results) would work if your json field config->'enabled' is set to 1/0 instead of true/false. For example:

[andersryanc]

I could certainly try that... but...

The thing that doesn't make sense to me is that the SQL expression itself works fine using a boolean. As you can see in my description above, the SQL that is output is identical in both cases. You can also run the SQL directly through a SQL prompt or GUI application and get the expected results. What doesn't make sense is why the GORM Where() function does not return the results when passing the boolean value as a parameter in the 2nd argument, versus when you hard code the boolean into the SQL expression itself in the 1st argument of the function (and pass no parameter in the 2nd argument).

You can see that in both cases, the SQL output is the same, while one says [rows:0] and the other says [rows:1].

[seahm]

I am not very familiar with this. But I think DB.Where("config->'enabled' = true").Find(&results) and DB.Where("config->'enabled' = ?", true).Find(&results) do not take the exact route. You will see the difference if you step into the code. The SQL output is just the logging. If you set ParameterizedQueries of your logger config to true, it will display differently.