laravel/framework: [13.x] Fix `whereJsonOverlaps()` with a JSON path on MySQL

Category: Fix

If you have ever tried to use whereJsonOverlaps() with a nested JSON path on MySQL, it has never worked — the query builder happily generated SQL that the database itself refuses to execute. Pull request #61853 fixes the generated SQL so the JSON path is applied with json_extract() before the overlap check, and it replaces unit tests that were quietly blessing the broken output with corrected assertions plus a real integration test that runs against MySQL.

What was broken

whereJsonOverlaps() answers the question "do these two JSON arrays share any elements?" It works fine when you pass a whole JSON column, but with a path like options->languages it always failed on MySQL and MariaDB. Here is the minimal reproduction from the PR:

DB::table('users')->whereJsonOverlaps('options->languages', ['en', 'es'])->get();

// 1582 Incorrect parameter count in the call to native function 'json_overlaps'

The cause sits in MySqlGrammar::compileJsonOverlaps(). When Laravel splits a column like options->languages into a field and a path, the compiler was gluing the path onto the end of the second argument:

json_overlaps(`options`, ?, '$."languages"')

MySQL's JSON_OVERLAPS() accepts exactly two arguments — the JSON document and the candidates — so the trailing path produced a parameter-count error on every execution. Because the failure came from the database engine rather than PHP, a test suite would only catch it if it actually ran against a MySQL server.

The fix: extract the path first

The PR changes the compiler so that, when a path is present, the column is wrapped in json_extract() and the path is never passed to JSON_OVERLAPS():

-- before (v13.34.0)
select * from `users`
where json_overlaps(`options`, ?, '$."languages"');

-- after
select * from `users`
where json_overlaps(json_extract(`options`, '$."languages"'), ?);

Two details worth noting. First, the new code only wraps when $path !== '', so calls that pass a whole JSON column — whereJsonOverlaps('options', [...]) — compile exactly as before and were never affected. Second, the bindings do not change: the candidate array is still encoded into a single JSON binding (["en","fr"]), and json_extract() hands JSON_OVERLAPS() the nested array it should compare against.

Because whereJsonDoesntOverlap(), orWhereJsonOverlaps(), and orWhereJsonDoesntOverlap() all compile through the same method, the fix covers all four variants at once.

Why the old tests missed it

The existing unit tests were asserting the broken SQL string — the expected output matched exactly what the buggy compiler produced, so everything stayed green while the feature was unusable. The PR updates those expectations to the corrected form:

$builder->select('*')->from('users')->whereJsonOverlaps('users.options->languages', ['en', 'fr']);

// select * from `users` where json_overlaps(json_extract(`users`.`options`, '$."languages"'), ?)

On top of that, a new integration test, testWhereJsonOverlapsWithPath, actually executes the query against a real MySQL server. It is gated with #[RequiresDatabase('mysql', '>=8.0.17')], since that is the version where JSON_OVERLAPS() landed. The test seeds two rows — one holding ["en","de"], one holding ["fr"] — and asserts that both the positive and negative variants (whereJsonOverlaps / whereJsonDoesntOverlap) return exactly one matching row. This is the kind of test that would have caught the bug on day one.

Upgrade impact

• If you were hitting error 1582, upgrading past v13.34.0 fixes it with no code changes — your existing whereJsonOverlaps('col->path', ...) calls start working exactly as written.
• If you shipped a raw-SQL workaround, you can delete it. A typical workaround on 13.34.0 looked like this:

DB::table('users')
    ->whereRaw('json_overlaps(json_extract(`options`, ?), ?)', [
        '$."languages"',
        json_encode(['en', 'es']),
    ])
    ->get();

That can now simply become ->whereJsonOverlaps('options->languages', ['en', 'es']).

• If you pinned the old (broken) SQL in your own test suite, update those assertions to the json_extract(...) form.
• There is no public API change, no new methods, and no change to how pathless overlap checks compile — this is a pure bug fix that slots cleanly into a patch release.

Takeaways

• whereJsonOverlaps() with a JSON path (options->languages) always failed on MySQL/MariaDB with "Incorrect parameter count" because the path was emitted as a third argument to JSON_OVERLAPS().
• PR #61853 wraps the column in json_extract() when a path is present; all four overlap methods (whereJsonOverlaps, orWhereJsonOverlaps, whereJsonDoesntOverlap, orWhereJsonDoesntOverlap) are fixed.
• Whole-column overlap checks are unchanged, and the bindings stay identical.
• Tests that asserted the broken SQL were corrected, and a new MySQL integration test (requires 8.0.17+) now exercises the query end-to-end.
• Upgrading is a drop-in patch: no API changes, just working queries — and any whereRaw workarounds can be removed.

Sources

• Pull request #61853
• Release v13.35.0