CODEWITHSUNDEEP
mysqlgogo-gormmysql-json
andersryanc
andersryanc

Reputation: 979

GORM fails to query boolean json field in MySQL

NOTE: I have only tested this issue with MySQL.

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.

The results below are of type []Account:

type Account struct {
    gorm.Model
    UserID sql.NullInt64
    Number string
    Config AccountConfig `gorm:"type:json;serializer:json"`
}

type AccountConfig struct {
    Enabled bool   `json:"enabled"`
    Foo     string `json:"foo"`
    Bar     int64  `json:"bar"`
}

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.

Also, see my issue on github in the GORM repo. I provided a range of test cases in my pull request linked in the issue.

Upvotes: 1

Views: 132

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562871

In MySQL, a boolean field in a JSON document is returned either as a scalar JSON document or a scalar string value. You can see this with the mysql client with the --column-type-info option:

$ mysql --column-type-info

Using -> returns a JSON document, even though that JSON document consists of only a single scalar value.

mysql> select config->'$.enabled' from Accounts;
Field   1:  `config->'$.enabled'`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       JSON
Collation:  utf8mb4_0900_ai_ci (255)
Length:     4294967292
Max_length: 4
Decimals:   31
Flags:      BINARY 

+---------------------+
| config->'$.enabled' |
+---------------------+
| true                |
+---------------------+

Using ->> returns an "unquoted" value, which turns the JSON value into a binary string.

mysql> select config->>'$.enabled' from Accounts;
Field   1:  `config->>'$.enabled'`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  utf8mb4_0900_ai_ci (255)
Length:     4294967295
Max_length: 4
Decimals:   31
Flags:      BINARY 

+----------------------+
| config->>'$.enabled' |
+----------------------+
| true                 |
+----------------------+

Either way, the result of extracting a boolean from JSON does not yield an SQL boolean value, so it won't be comparable to true or a Go boolean true.

I got it to work this way:

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

I'm passing the Go string value "true", which will compare to the SQL binary string 'true' returned from the JSON extract operator.

I would strongly recommend you use normal columns instead of JSON. There is little advantage to using JSON, and it introduces a lot of weird complexity like this case.

Upvotes: 0

Related Questions