Is there a way to use JSON_EXTRACTOR in a prepared statement's where clause in golang/mySQL?

Question

I have a schema similar to

CREATE TABLE IF NOT EXISTS test_table (
    id CHAR(36) NOT NULL,
    json JSON,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB;`

with which I would like to filter a select query based on the contents of the json field. We are using Go with sqlx which extends golang/sql so I would expect to be able to do something similar to this:

query := "SELECT * FROM test_table WHERE json->'$.alive' = ?"
    rows, err := db.QueryxContext(ctx, query, true)
    if err != nil {
        fmt.Println(err)
    }
defer rows.Close()
for rows.Next() {
        var id int
        var json string
        rows.Scan(&id, &json)
    }

This yields no rows implying no results from the query (that is the rows object is empty). However, running the same query directly on the instance with DataGrip does yield the rows I would expect from the query.

I've tried recreating this example with the bare minimum setup and the issue still presents though does not error.

I can get around this with using a non-prepared(?) statement like

query := "SELECT * FROM test_table WHERE json->'$.alive' = true"

and update that with go/fmt however I would prefer to avoid mixing prepared and non-prepared where clauses when the JSON extractor syntax should be working. Any help/insight would be much appreciated.

env:

go1.19.11 linux/amd64
mysql8.0.33 for Linux on x86_64
github.com/go-sql-driver/mysql v1.6.0
github.com/jmoiron/sqlx v1.3.5

Edit: Tried also using "true" as a string over a boolean value, 1 (since MySQL uses a TINYINT in the background), and the longhand JSON_EXTRACTOR to no avail.

Answer 1

The issue you're encountering might be due to how the MySQL driver handles boolean values. By default, MySQL doesn't have a BOOLEAN data type, and instead, it uses TINYINT(1) to represent boolean values, where 1 represents true and 0 represents false.

In your case, when you're trying to bind the true value to your SQL query, it's possible that the driver is converting that to a 1, but the JSON->'$.alive' operator in MySQL is returning a boolean JSON value, not a numeric value.

One possible workaround might be to bind the value as a string, like this:

query := "SELECT * FROM test_table WHERE json->'$.alive' = ?"
rows, err := db.QueryxContext(ctx, query, "true")
if err != nil {
    fmt.Println(err)
}
defer rows.Close()
for rows.Next() {
    var id int
    var json string
    rows.Scan(&id, &json)
}

Another possible solution might be to compare the result of JSON_EXTRACT as a string:

query := "SELECT * FROM test_table WHERE JSON_EXTRACT(json, '$.alive') = ?"
rows, err := db.QueryxContext(ctx, query, "true")
if err != nil {
    fmt.Println(err)
}
defer rows.Close()
for rows.Next() {
    var id int
    var json string
    rows.Scan(&id, &json)
}