Laravel whereJsonContains and WhereJsonOverlaps eloquent methods.

Sometimes there are situations when you have to store data in JSON format, yes it happens. And when it happens it's hard to search in that JSON data. Luckily laravel has a solution to this problem.

whereJsonContains to the rescue

$users = DB::table('users')
            ->whereJsonContains('options->languages', 'en')
            ->get();

// or

$users = DB::table('users')
            ->whereJsonContains('options->languages', ['en', 'de'])
            ->get();

The limitation of this function is, that it will match the first value and bring the results. I will not check if both the items are there or not.

So, Benjamin Ayles decided to made

whereJsonOverlaps
whereJsonDoesntOverlap

User::whereJsonOverlaps('languages', ['en', 'fr'])->exists();
User::whereJsonDoesntOverlap('languages', ['en', 'fr'])->exists();

these compare two JSON documents. Returns true (1) if the two documents have any common key-value pairs or array elements. If both arguments are scalars, the function performs a simple equality test. If either argument is NULL, the function returns NULL.

Here is the PR

As of now, these are only available for MySql. But may be added for other DB in due time.

More from laravel 11 hidden gems.