Null Safe Operator on Json Queries

[pedrovian4]

Currently, Laravel allows us to query JSON fields using the arrow operator, for example:
User::where('options->settings->theme', 'dark')->get();

However, if the settings key is missing or null in some records, the query behavior depends heavily on the underlying database engine's handling of missing keys. I propose introducing a Null-safe JSON operator (similar to PHP’s ?->) to explicitly handle optional nested keys more expressively.

Example Usage

Instead of writing complex whereNotNull checks or manual JSON path logic:

// Current way (can be verbose or error-prone depending on DB)
User::whereNotNull('options->settings')
    ->where('options->settings->theme', 'dark')
    ->get();

// Proposed way
User::where('options->settings?->theme', 'dark')->get();

[Pola5h]

Great idea, but this might be better handled at the database level rather than Laravel's query builder.

Current Workaround (works reliably across MySQL, PostgreSQL, SQLite):

User::where(function ($query) {
    $query->whereNull('options->settings')
          ->orWhere('options->settings->theme', 'dark');
})->get();

For MySQL specifically, you can use JSON_VALUE with a raw expression:

User::whereRaw("JSON_UNQUOTE(JSON_EXTRACT(options, '$.settings.theme')) = ?", ['dark'])->get();

For PostgreSQL, the #>> operator already handles missing keys gracefully:

User::whereRaw("options #>> '{settings,theme}' = ?", ['dark'])->get();

Why Laravel may not implement this natively:

• MySQL, PostgreSQL, and SQLite handle missing JSON keys differently
• A ?-> operator would need different SQL output per database driver
• This adds significant complexity to the query grammar layer

A macro could be a good community solution in the meantime:

Builder::macro('whereJsonSafe', function ($column, $value) {
    [$path, $key] = explode('?->', $column);
    return $this->where(function ($q) use ($path, $key, $value) {
        $q->whereNotNull($path)
          ->where("$path->$key", $value);
    });
});

// Usage
User::whereJsonSafe('options->settings?->theme', 'dark')->get();

This could even be proposed as a Laravel package while the core team evaluates native support.