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

Question

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?

do you want to get the line too ? ex:you want to return this:{"code": "5", "club": "CHE", "country": "ENGLAND"}, or just the whole row? — Supun Praneeth, Mar 21 '18 at 14:28

score 7 · Accepted Answer · answered Mar 21 '18 at 14:08

7

Just use a SQL LIKE operator

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

answered Mar 21 '18 at 14:08

Christopher Francisco

15,672
28
94
206

Can you update your answer using json? So there are two solution. First, use like. Second, use json (https://mattstauffer.com/blog/new-json-column-where-and-update-syntax-in-laravel-5-3/) – moses toh Mar 25 '18 at 05:53
This is not good to search a number. if we check '1' it can find '11' or '21' and ... – AbdulAhmad Matin Jun 07 '20 at 04:05
@AbdulAhmadMatin Yes, you're right. But I've found some trick, add commas at the end parameter and before the second percent symbol. Here : `->where('column->objects', 'like', "%\"id\":${paramId},%")` – ibnɘꟻ Oct 07 '21 at 05:18

score 5 · Answer 2 · answered Mar 21 '18 at 14:37

5

Try this:

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

answered Mar 21 '18 at 14:37

Jonas Staudenmeir

24,815
6
63
109

Look at this : https://mattstauffer.com/blog/new-json-column-where-and-update-syntax-in-laravel-5-3/. Whether the reference can be used to answer this question? – moses toh Mar 25 '18 at 05:55

score 4 · Answer 3 · answered Sep 27 '22 at 13:41

4

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

answered Sep 27 '22 at 13:41

Manford Benjamin

277
4
5

score 0 · Answer 4 · answered Mar 21 '18 at 14:36

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.

score 0 · Answer 5 · answered May 13 '20 at 13:07

0

use Illuminate\Support\Facades\DB;

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

answered May 13 '20 at 13:07

William Desportes

1,412
1
22
31

score 0 · Answer 6 · answered Dec 06 '22 at 10:43

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()

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

6 Answers6

Linked