Framework class-based casts handle JSON null differently than string-based casts

[Propaganistas]

Laravel Version

13.7.6

PHP Version

8.5.2

Database Driver & Version

MySQL 9.3.0

Description

I've noticed that there's a difference between string-based casts such as array and object vs class-based casts such as AsArrayObject and AsCollection if the target column is of type JSON.

When persisting null, they act differently:

$model->jsonColumn = null;
$model->save();

Cast	Database value
array / object	null
AsArrayObject / AsCollection	"null" (JSON type)

When working with retrieved models all works fine because the JSON type is properly converted back to plain null. But things get skewed when you want to query for the json column being entirely null:

Cast	Query	MySQL	SQLite
array / object	whereNull(jsonColumn)	✅	✅
AsArrayObject / AsCollection	whereNull(jsonColumn)	❌	❌
AsArrayObject / AsCollection	where(jsonColumn, '=', '"null"')	❌	❌
AsArrayObject / AsCollection	whereJsonLength(jsonColumn, '<=', 0)	❌	✅
AsArrayObject / AsCollection	whereRaw('JSON_TYPE(jsonColumn) = "NULL"')	✅	❌

The main difference is that the string-based casts don't cast if the value is null. They keep it as null in the database as well, resulting in whereNull to be able to query it. The class-based casts do apply their casting, in this case to JSON string "null".

Given that whereNull(jsonColumn->property) IS supported, I would have expected whereNull(jsonColumn) to be supported as well. And also note the difference in results between MySQL en SQLite (haven't checked Postgres or SQL Server yet), denoting a database driver dependency in codebases if the grammars are left as-is.

Steps To Reproduce

See https://github.com/Propaganistas/framework-issue-61204 for a reproducible application.

---

Schema::create('foos', function (Blueprint $table) {
    $table->id();
    $table->json('items')->nullable();     // but nullable isn't even required here because it will always contain a json type string, even "null"
    $table->timestamps();
});

class Foo extends Model
{
    protected $guarded = [];

    protected function casts(): array
    {
        return [
            'items' => AsCollection::class,
        ];
    }
}

$foo = new Foo;
$foo->items = null;
$foo->save();

Foo::whereNull('items')->exists(); // false

[lazerg]

Just a heads up: this exact fix was already tried in #42352 (short-circuiting AsCollection/AsArrayObject::set() on null), merged, then reverted same day in #42354. Taylor's reasoning: #42352 (comment) — it could break existing apps whose json column is NOT NULL, since they rely on null being stored as the JSON string "null" rather than SQL NULL.

The author suggested targeting a major version instead, but that never happened, the behavior is unchanged through 10.x/11.x/12.x/13.x. So this looks like intentional (if debatable) backward-compat behavior rather than an oversight, might be worth reviving as a v14 breaking change proposal instead of a patch.

[Propaganistas]

I searched for similar issues but didn't seem to find that one.
Anyway, there are at least two ways to look at this:

1. "correctly" nulling to the database (as already PRed)
2. enhancing whereNull grammar to support JSON type null at the root as well

In terms of framework experience, either option is fine I'd say. Both would allow to query as one would expect.
In terms of strict database correctness the first one is of course the way to go but I totally acknowledge the breaking nature of it.

I think this issue differs from the PR in the sense that the PR focusses solely on the database effect of those casts. I'm trying to uncover its effects leading to other framework parts getting unexpectedly dysfunctional.

Let's hope this becomes a candidate for consideration.

[lazerg]

On option 2, here is where that would actually land in the grammar.

The nested case works because of MySqlGrammar::whereNull()/whereNotNull() (MySqlGrammar.php#L79-L110), added in #32417 for #31473. It is the only whereNull override in the entire grammar layer. Postgres, SQLite and SQL Server never override it, their nested paths just happen to work because ->>, json_extract() and json_value() return SQL NULL for a JSON null, which MySQL's JSON_EXTRACT does not.

The gate on that override is isJsonSelector(), which is literally str_contains($value, '->') (Grammar.php#L186). That -> is the only signal the grammar has that a column is JSON at all. A bare whereNull('options') carries no marker and the query builder holds no column type info at compile time, so there is nothing to branch on. Same reason there is no escape hatch today: whereNull('options->') compiles to a broken path on all four drivers, MySQL emits json_extract("options", '$.""') (with backtick quoting).

Branching unconditionally is not safe either. json_type() on a non-JSON value is a hard error in SQLite:

$ sqlite3 :memory: "select json_type('hello');"
Error: stepping, malformed JSON

and whereNull is on the hot path for soft deletes and relation constraints, so every one of those compiles through the same method.

There is also no existing helper to reuse. The JSON type introspection that does exist is all path based and goes through wrapJsonFieldAndPath() (CompilesJsonPaths.php#L16), which returns an empty path for a bare column: SQLiteGrammar::compileJsonContainsKey() L241 uses json_type, PostgresGrammar::compileJsonContainsKey() L293 uses jsonb_typeof. SqlServerGrammar has no JSON type function anywhere, only openjson, json_value and json_query.

So option 2 is not a small grammar tweak. It needs new root-level SQL in all four grammars plus some way for the builder to know the column is JSON, either an explicit opt-in method or cast info plumbed down from Eloquent.

References checked against 13.x at a659f09.