[5.8] Querying on json columns with boolean values broken

[mvanduijker]

• Laravel Version: 5.8.0
• PHP Version: 7.3.2
• Database Driver & Version: Mysql 8

Description:

Trying to query on a database column of type json with a boolean value doesn't return results.

Example:

$query->where('myColumn->boolean', true);

There is a workaround if we make the boolean value a string, the query works again.

$query->where('myColumn->boolean', 'true');

Not really intuitive right now. Probably broken by #25732

Steps To Reproduce:

create table example
(
	field json null
);

INSERT INTO example (field) VALUES ('{"yes": true}');

DB::table('example')->where('field->yes', true)->get();

[driesvints]

Ping @staudenmeir

[staudenmeir]

Already on it.

[staudenmeir]

Boolean comparisons don't work with PostgreSQL or SQL Server either, but – as opposed to MySQL – they never have.

Unfortunately, where('field->yes', 'true') is not an ideal workaround, as it also matches "true" strings.

This is a complex issue, I'm not sure whether we can fix it.

We would have to remove the json_unquote() call for boolean values, but when the grammar compiles the SQL statement, it doesn't know what value the statement is for.

The json_unquote() call is actually only necessary for the SELECT clause, but I don't see a realistic way to compile different statements for the SELECT and the WHERE clause.

[ctxcode]

Another work around is:

$query->whereRaw('JSON_EXTRACT(myColumn, "$.boolean") = true');

[setkyar]

@LorenzV No, that does not work. It's not that simple.

[aaronhuisinga]

@staudenmeir were you able to make any more progress on this? We're stuck waiting to see if there is anything that can be done about this before finalizing our upgrade to 5.8.

[staudenmeir]

@aaronhuisinga Not really. Using whereRaw() is the best workaround at the moment.

[driesvints]

@setkyar why doesn't that work for your use case?

[driesvints]

@staudenmeir there's no way we might implement something custom for boolean values?

[cmorbitzer]

We ought to make it work. This is a silent breaking change, resulting in a potentially dangerous data situation.

Of course, it is possible to fix it. It's just that the solution may not look neat.

Perhaps instead of using json_unquote we could go back to using ->> under the hood, but make it seamless for the user by doing a transformation from -> to ->> behind the scenes depending on the driver? Not sure if that would work.

I don't think it will come to this, but rolling back #25732 would still be better since that was an annoyance but not truly a bug. If we supported non-string data types in the last Laravel version, we should continue supporting it in order to claim JSON support.

[driesvints]

@cmorbitzer we're currently not considering rolling back that PR. We should focus on fixing the current issue instead.

[staudenmeir]

I'm working on a solution, it will be ready tomorrow.

[driesvints]

@staudenmeir thanks!

[staudenmeir]

#27847