How To Use JSON Where Clauses With Example Using Laravel

admin_img Posted By Bajarangi soft , Posted On 23-09-2020

Laravel also supports querying JSON column types on databases that provide support for JSON column types. Currently, this includes MySQL 5.7, PostgreSQL, SQL Server 2016, and SQLite 3.9.0.

How JSON Where Clauses are used in Laravel with an example

JSON Where Clauses
To query a JSON column, use the -> operator:

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

$users = DB::table('users')
->where('preferences->dining->meal', 'salad')
->get();
 

You may use whereJsonContains to query JSON arrays (not supported on SQLite):

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

MySQL and PostgreSQL support whereJsonContains with multiple values:

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


You may use whereJsonLength to query JSON arrays by their length:

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

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

Related Post