[Improvement] Filter mysql table data from json field (in array)

[lkmadushan]

This is what you have in Laravel now.

// SQL tags -> {"name": "javascript"}
SELECT * FROM books WHERE tags->"$.name" = 'javascript'

// Builder
DB::table('books')->where('tags->name', 'javascript')->toSql();

What I am suggesting is make it easier to search in json array,

// SQL tags ->  {"name": ["javascript", "php", "java"]}
SELECT * FROM books WHERE JSON_CONTAINS(tags->"$.name", '["javascript", "php"]');

// Builder
DB::table('books')->where('tags->name', ['javascript', 'php'])->toSql();

// Eloquent
Book::whereJsonContains('tags->name', ['javascript', 'php']);

// or
Book::jsonContains('tags->name', ['javascript', 'php']);

I would like to make a pull request for this. Please let me know your suggestions.

[yoosuf]

@taylorotwell looks a like this an awesome way of filtering some contents

[ntzm]

Why not use whereIn?

[lkmadushan]

@ntzm as method name?

[sepehr]

Why not use whereIn? — @ntzm on Nov 10, 2016, 3:05 PM GMT+3:30

The proposed functionality is totally different from whereIn. See JSON_CONTAINS docs.

[joemugen]

@lkmadushan Just facing the same need , a whereIn like in an array stored in a JSON column type (no keys)
For now, I'm using a scope doing a raw request D:

Your approach is interesting but does it work with this kind of structure: ["foo", "bar", "laravel"]
If you do this:

Book::whereJsonContains('tags', ['javascript', 'php']);

You'll get all records havings tags javascript AND php or I'm wrong? If yes, what's your idea to manage OR case?

Thanks :)

[royduin]

I'd the same "issue", what I've done for now in my shop model:

public function products()
{
    return Product::whereRaw('JSON_CONTAINS(shops,"'.$this->id.'")');
}

In the json column on the products table there is an array or shop id's: [1,2,3,4].

Downside of this is it's not a real relation so I can't use Shop::withCount('products');, tips? I'm going to experiment with this: https://github.com/johnnyfreeman/laravel-custom-relation, let's see if it fits me needs. Edit: nope, didn't work out well so I've created a hasManyJson() relation.

Something like this would be nice to have in the framework:

whereJsonContains($column, $value, $comparison = 'and')

And it would be really awesome if hasMany()could work with this.

[Mittler1]

This feature would be awesome! Searched around the net and there is no really satisfying solution yet.

[staudenmeir]

The next release will contain whereJsonContains(): laravel/framework#24330

[staudenmeir]

@driesvints Can be closed.

[BNhashem16]

How to search in json?
$posts = Post::where('country_id' , $search_country)->where('page_id' , $page->id)->whereJsonContains('title',['like','%'.$search_post.'%'])->paginate(12);

[pardeep-k]

To see if the template_ids JSON field contains "any" of the values in a needle array, you gonna need to utilize multiple OR'd JSON_CONTAINS conditions which requires MySQL 5.7:
for more stackoverflow

 $ids = ['1', '3', '48'];
 Post::where('accessable_to', 1)
->where(function ($query) use ($ids) {
    $firstId = array_shift($ids);

    $query->whereRaw(
        'JSON_CONTAINS(template_ids, \'["' . $firstId . '"]\')'
    );

    foreach ($ids as $id) {
        $query->orWhereRaw(
            'JSON_CONTAINS(template_ids, \'["' . $id . '"]\')'
        );
    }

    return $query;
});
return Post::paginate(3);

[ch-dani]

$posts = Post::where('country_id' , $search_country)->where('page_id' , $page->id)->whereJsonContains('title',['like','%'.$search_post.'%'])->paginate(12);

Also not working on my end. Please guide if someone has solution.

[ikramooe]

$posts = Post::where('country_id' , $search_country)->where('page_id' , $page->id)->whereJsonContains('title',['like','%'.$search_post.'%'])->paginate(12);

Also not working on my end. Please guide if someone has solution.

any solution !