0

I have in my database a column which store a json and I would like to recover the value of a certain object of my json in order to test it

So my column "JSON" contains that 

{
"type":"index",
"data ":{"index":2}
}

And if i receive 2, i need to get this column which contain the index 2 to delete it I have tried this:



$column = Table::where('JSON["data"]["index"]','=', '2' )
                ->first();
 Table::dropColumn($column);

But it doesn't work, beacause i can't get the index

Gavroch
  • 81
  • 1
  • 8
  • does casting the column to an array help? https://laravel.com/docs/5.8/eloquent-mutators#attribute-casting (look a little further down for JSON -> array casting) – Vlad Visinescu Apr 10 '19 at 12:09
  • You could try something like this: `Table::where('json_columnname','like', '%"data ":{"index":2}%' ) ->first();` Or install a package which adds support for json queries like answered here https://stackoverflow.com/questions/42425667/mariadb-json-support-in-laravel – Techno Apr 10 '19 at 12:10
  • @RobBiermann tried this but the result is null! – Gavroch Apr 10 '19 at 12:22

3 Answers3

2

If you are using latest laravel version then,

$column = Table::where('JSON->data->index','2')->first();

Should work.

You can refer official laravel documentation for json where clause here.

Rahul
  • 18,271
  • 7
  • 41
  • 60
0

Simply use SQL Like

->where('JSON', 'like', '%"index": "2"%');
Prashant Prajapati
  • 999
  • 8
  • 14
0
   Since you gave JSON value as a string, it was unable to get the index value.                                                                                  
   Can you please try like below,                                        
       $column = Table::whereRaw(JSON["data"]["index"], '=', '2' )
                 ->first();                       
                 Table::dropColumn($column);
Nandy
  • 92
  • 5