Clasura Where em campo tipo json no (Laravel + Eloquent)

Question

The problem is the following, I have to bring from a select the permissions that a profile has, only that the permissions are in a json field, and because it is several permissions we have a collection of data, I need to do this within a middleware:

 public function handle($request, Closure $next, $rotaSolicitada){

    try{
        $usuario = JWTAuth::parseToken()->authenticate();
    }catch (\Exception $e){
        return response()->json(['error'=> true, 'mensagem'=> 'Não foi possível autenticar no sistema', 'data'=> null], 500);
    }

    $permissao = Perfil::with('usuario')
                    ->where('id', $usuario->id)
                    ->where('recursos->rota', 'reserva')
                    ->get();

    //aqui é só para exibir o retorno (teste)   
    dump($permissao);
    return response()->json($permissao);



    $recursoSolicitado = str_replace( '@','',strstr($request->route()->getActionName(), '@'));


    return $next($request);
}

my database is like this:

    public function up()
{
    Schema::create('perfis', function (Blueprint $table) {
        $table->increments('id');
        $table->string('descricao', 45);
        $table->json('recursos')->nullable();
        $table->char('status',1);
        $table->timestamps();
    });
}

Direct return of the select (select * from perfils)

Json format of the resources column I want to search for:

[{
"rota": "reserva",
"sref": "oQueFazer",
"ordem": "1",
"recursos": ["index", "show"],
"descricao": "Reservar",
"controller": "ReservasController"
}, {
    "rota": "reserva",
    "sref": "oQueFazer",
    "ordem": "2",
    "recursos": ["index", "show"],
    "descricao": "Reservas",
    "controller": "ReservasController"
}, {
    "rota": "usuario",
    "sref": "oQueFazer",
    "ordem": "3",
    "recursos": ["index", "show"],
    "descricao": "Usuários",
    "controller": "UsuariosController"
}, {
    "rota": "feriado",
    "sref": "oQueFazer",
    "ordem": "4",
    "recursos": ["index", "show"],
    "descricao": "Feriados",
    "controller": "FeriadosController"
}, {
    "rota": "sala",
    "sref": "home.sala",
    "ordem": "5",
    "recursos": ["index", "show"],
    "descricao": "Salas",
    "controller": "SalasController"
}]

In short, I want to make a wherein that field json, named after recursos. I’d like to show you all recursos of json where the rota = 'reservas.

I tried it and it didn’t work:

 $permissao = Perfil::with('usuario')
                    ->where('id', $usuario->id)
                    ->where('recursos->rota', 'reserva')
                    ->get();

Answer 1

For an explanation of the site the logic would be with JSON_CONTAINS

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             1 |
+-------------------------------+

and on this other link about JSON Paths, explains how to work with wildcard (*) being that a array for a particular search would be $.[*] and the name of the field. The return of JSON_CONTAINS can be 1 or 0 if the query is contained in the document JSON or NULL if the arguments are null or if the document section JSON is not identified.

An example in your code I believe is:

$permissao = Perfil::with('usuario')
                ->where('id', $usuario->id)
                ->whereRaw('JSON_CONTAINS(recursos, "reserva", $.[*]rota)=1')
                ->get();

• JSON_CONTAINS
• 12.16.3 Functions That Search JSON Values

Complementing the answer, follows the solution:

SELECT JSON_EXTRACT(recursos, '$[0].recursos') permite from perfis
WHERE
JSON_CONTAINS(recursos, '{"controller": "FeriadosController"}');