Fetching value in the json array in MySQL, Laravel-CodePudding

I have that kinda table and data :

Table Name : data

 ------ ----------------- -------- ---------- 
| id   | number          | name   | surname  |
 ------ ----------------- -------- ---------- 
|    1 | [1, 2, 3, 4, 5] |   John |      Doe |
|    2 | [1, 2, 4, 8]    |  James |     Webb |
|    3 | [3, 4, 5]       |  Jenny |     Test |
 ------ ----------------- -------- ----------

For example, I want to fetch the rows in the number column with the value 3 :

 ------ ----------------- -------- ---------- 
| id   | number          | name   | surname  |
 ------ ----------------- -------- ---------- 
|    1 | [1, 2, 3, 4, 5] |   John |      Doe |
|    3 | [3, 4, 5]       |  Jenny |     Test |
 ------ ----------------- -------- ----------

I tried that with Laravel but didn't work. :

DB::table('data')
            ->whereRaw('FIND_IN_SET(?, number)', [3])
            ->get();

How can I solve that problem? Thanks for your answers.

CodePudding user response：

You can use toJson(), to convert the collection to json object in Laravel.

$_data = DB::table('data')
            ->whereRaw('FIND_IN_SET(?, number)', [3])
            ->get()->toJson();
dd($_data);

Have a look at the [documentation] https://laravel.com/docs/9.x/responses#json-responses

CodePudding user response：

Thanks for your answers. I found the answer to my question as follows :

I made the Array values as strings. Like that :

Table Name : data

 ------ --------------------------- -------- ---------- 
| id   | number                    | name   | surname  |
 ------ --------------------------- -------- ---------- 
|    1 | ["1", "2", "3", "4", "5"] |   John |      Doe |
|    2 | ["1", "2", "4", "8"]      |  James |     Webb |
|    3 | ["3", "4", "5"]           |  Jenny |     Test |
 ------ --------------------------- -------- ----------

And then I used to this codes. :

 $_data = DB::table('data')
            ->where('number', 'LIKE', '%"' . "3" . '"%')
            ->get();

Also you can do with SQL commands. Like that :

SELECT * 
  FROM data
  WHERE number LIKE '%"3"%';

Exactly like I want, it turned to me this :

 ------ --------------------------- -------- ---------- 
| id   | number                    | name   | surname  |
 ------ --------------------------- -------- ---------- 
|    1 | ["1", "2", "3", "4", "5"] |   John |      Doe |
|    3 | ["3", "4", "5"]           |  Jenny |     Test |
 ------ --------------------------- -------- ----------