CODEWITHSUNDEEP
phpmysqllaravelmysql-json
Farshad
Farshad

Reputation: 2000

Querying inside json column in laravel elequent

i have a model called orders which have an info json column . in that json i have a property called id . now i want to retrieve all orders with the id of 6 for example . so here is how i have done like below :

  $order = \DB::table('orders')
                ->where('info.id',$value)
               // ->where('info->id',$value)
//            ->whereRaw('JSON_EXTRACT(`info` , "$.id") = '.$value)
                ->first();

the third whereRaw is working but i think it has a bug because in my validation thirds or 4th one returns this error which is so strange :

Column not found: 1054 Unknown column '6112 ' in 'where clause' (SQL: select * from `orders` where JSON_EXTRACT(`info` , "$.id") = 6112  limit 1)

it some how mistakes the column value as the column name which is so strange because its working on first ones when i dd i get the values from query but it breakes on like 4th one . now i wanted to know if there is any easier solution to use where on json fields or what is the problem with that whereRaw

Upvotes: 6

Views: 4589

Answers (1)

W Kristianto
W Kristianto

Reputation: 9303

Laravel 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 (with the JSON1 extension).

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

$order = DB::table('orders')
        ->where('info->id', $value)
        ->first();

Additional information from the doc : JSON Where Clauses

You may use whereJsonContains to query JSON arrays. This feature is not supported by the SQLite database:

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

If your application uses the MySQL or PostgreSQL databases, you may pass an array of values to the whereJsonContains method:

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

You may use whereJsonLength method 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();

Upvotes: 9

Related Questions