Laravel join tables with JSON field, when some fields have no foreign key

I want to join 2 tables by a foreign key stored in a JSON field. The field can be empty, so i get an error, i can not sem to get rid of.

I have a model Transaction:

class Transaction extends BaseModel
{
    protected $casts = [
        'items' => 'json',
    ];

the items field is json:

[{"event_id":25,"article_nr":"000123","title":"My Event"}]

now i want to join these tables for a query:

Transaction::join('events','transactions.items->event_id', '=', 'events.id')
->get();

The problem is, that i have many transaction types, so the items field does not necessarily contain the event_id key, or can even be empty.

The SQL query from Eloquent is:

select * from `transactions` inner join `events` on json_unquote(json_extract(`transactions`.`items`, '$."event_id"')) = `events`.`id`

So for rows with empty items-field i get the error:

3141 Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0.

Is there a way to alter my join and only select rows, that contain the items->event_id key?

[edit] added SQL raw query

Answer

I got it working…

After the help from Akina (thanks), who mentioned, that all fields have to be filled with at least an empty json object:

Update transactions set items = '{}' where items = '' or items is null;

I was able to join the tables with:

Transaction::select(['transactions.*'])
                ->leftJoin('events',DB::raw("json_extract(transactions.items, '$[0].event_id')"),"=","events.id")

The '$[0].event_id' part is special to this join, because i have my event_id in an object, and this object is in an array:

[{"event_id":24}]

(Notice the outer brackets)

Without the outer array it would be easy, and i could have just done it without raw sql, like:

Transaction::leftJoin('events','transactions.items->event_id', '=', 'events.id')

[EDIT] FINAL SOLUTION

You will run into performance problems, when joining by JSON fields. My query took about 160s.

The final solution was to create a generated column event_id from the items row. The MySQL “generated column” is autofilled from its expression and automatically updated by MySQL/MariaDB, when the items field is updated.

ALTER TABLE transactions ADD event_id int(10) UNSIGNED AS (JSON_UNQUOTE(items->"$[0].event_id")) STORED;

The laravel migration for that would be

$table->bigInteger('event_id')->unsigned()->storedAs('JSON_UNQUOTE(items->"$[0].event_id")');

Now my query only takes 0.2s instead of 160s.