How to check the MySQL JSON column is not contains the value in laravel?

Question

I used to find whether the searching item is exist in the JSON datatype column or not.

I use,

 TABLE::whereRaw('json_contains(list, \'["value"]\')')->get();

this to return those are having the "value" in "list" JSON column.

But, how can i use reverse method to list, those are not having this value in json column in laravel.

Thank you !

Answer 1

You can use not:

TABLE::whereRaw('not json_contains(list, \'["value"]\')')->get();

In Laravel 5.6.24 you can use whereJsonDoesntContain():

TABLE::whereJsonDoesntContain('list', 'value')->get();

Answer 2

DB::table('table')->whereNull('list->key')->get();