CODEWITHSUNDEEP
mysqljsonlaraveleloquentlaravel-5.6
moses toh
moses toh

Reputation: 13162

How can I make query where json column in the laravel?

I use laravel 5.6

I have a field. The data type of the field is json

The value of the field (desc field) like this :

[
{"code": "1", "club": "CHE", "country": "ENGLAND"}, 
{"code": "2", "club": "BAY", "country": "GERMANY"}, 
{"code": "3", "club": "JUV", "country": "ITALY"}, 
{"code": "4", "club": "RMA", "country": "SPAIN"}, 
{"code": "5", "club": "CHE", "country": "ENGLAND"}, 
{"code": "6", "club": "BAY", "country": "GERMANY"}, 
{"code": "7", "club": "JUV", "country": "ITALY"}, 
{"code": "8", "club": "RMA", "country": "SPAIN"}, 
{"code": "CODE", "club": "CLUB", "country": "COUNTRY"}
]

I want to check the key of club have value "CHE" or not

I try like this :

->where('desc->club','=', 'CHE')->get();

But it does not work

How can I solve this problem?

Upvotes: 8

Views: 30821

Answers (6)

Hossein Piri
Hossein Piri

Reputation: 822

you can add this scope to model and use

public function scopeJsonArrayHasCondition($query , $col , $keys , $function , $as = 'temp_table')
    {
        $columns = [];
        foreach ($keys as $key){
            $columns[] = "$key VARCHAR(150) PATH '$.$key'";
        }
        $columns = implode(',',$columns);
        return $query->join(\DB::raw("JSON_TABLE($col, '$[*]' COLUMNS($columns)) as $as") , function(){})
            ->where($function);
    }

how to use

BranchContract::query()->jsonArrayHasCondition('payments',[
            'club' , 'country'
        ],function ($q){
            $q->where('temp_table.country' , 'ENGLAND')->where('temp_table.club' , 'CHE');
        })->get()

Upvotes: 0

Manford Benjamin
Manford Benjamin

Reputation: 397

MyModel::whereJsonContains('desc->club', 'CHE')->first();

Upvotes: 15

William Desportes
William Desportes

Reputation: 1701

use Illuminate\Support\Facades\DB;

MyModel::where(DB::raw('metadata->>\'$.original_text\''), 'LIKE', $originalText)->first();

Upvotes: -1

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

Try this:

->whereRaw('JSON_CONTAINS(`desc`, \'{"club":"CHE"}\')')

Upvotes: 5

Bill Karwin
Bill Karwin

Reputation: 562260

What version of MySQL are you using? The JSON operator -> is not implemented until MySQL 5.7. See https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#operator_json-column-path

Also, you aren't using the operator correctly. For the example you show, it should be:

desc->>'$[0].club' = 'CHE'

The double->> is so that the value returned does not include double-quotes.

But which club are you searching? $[0] only searches the first entry in your JSON array. If you want to find if any entry has that club = CHE, then this will work:

JSON_SEARCH(j->'$[*].club', 'one', 'CHE') IS NOT NULL

You can read more about the JSON functions here: https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html And path syntax here: https://dev.mysql.com/doc/refman/5.7/en/json-path-syntax.html

I showed expressions in raw SQL. I'll leave it to you to adapt that to Laravel.

Upvotes: 1

Christopher Francisco
Christopher Francisco

Reputation: 16268

Just use a SQL LIKE operator

->where('desc', 'like', '%"club": "CHE"%');

Upvotes: 6

Related Questions