select part of the JSON that was filtered in Where

Question

In the database I have a field called resources, in it I save the permissions of a user, in Json format:

[  
 {  
    "rota":"reserva",
    "sref":"oQueFazer",
    "ordem":"1",
    "recursos":[  
       "index",
       "show",
       "store",
       "update"
    ],
    "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":"home.feriado",
    "ordem":"4",
    "recursos":[  
       "index",
       "show",
       "store",
       "update",
       "destroy"
    ],
    "descricao":"Feriados",
    "controller":"FeriadosController"
 },
 {  
    "rota":"sala",
    "sref":"home.sala",
    "ordem":"5",
    "recursos":[  
       "index",
       "show"
    ],
    "descricao":"Salas",
    "controller":"SalasController"
 }
]

When I run the query below, it gives me the correct feedback from which user has the permission:

SELECT recursos from perfis
WHERE
  JSON_CONTAINS(recursos, '{"controller": "FeriadosController"}');

Almost there, with the select above I get the record line I’m looking for, the problem it shows me all the collection of json that is in the resource field. (Of course he was going to do it, that’s what I asked to be executed).

So for it to bring me the permissions that are authorized for a user in a given controller, I have selected below:

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

And the return was:

["index", "show", "store", "update"]

That is, everything OK, it shows the permissions that the user has in a given controller, THE PROBLEM is that it shows the permissions of the first line JSON collection '$[0].recursos' but that’s not quite what I’m wanting, I want to return:

["index", "show", "store", "update", "destroy"]

Which is the permission the user has for the controller FeriadosController

I mean, I’m able to scan the controller to search the json, but I’m not able to show the part of the json that was searched, someone has a solution to this, jpa has gone through this?

Answer 1

Using a combination of JSON_SEARCH(), JSON_EXTRACT(), JSON_UNQUOTE() and the string manipulation function REPLACE(), I think I got to the desired result.

Assuming the JSON structure is known, perform a search for Holidays using JSON_SEARCH() and manipulate the result to replace "controller" with "resources". Use JSON_UNQUOTE() to remove the quotes that Mysql puts in the result and then pass everything to the JSON_EXTRACT().

It’s not pretty, but it works:

SELECT
    JSON_EXTRACT(recursos,                                          -- ["index", "show", "store", "update", "destroy"]
        JSON_UNQUOTE(                                               -- $[3].recursos
            REPLACE(                                                -- "$[3].recursos"
                JSON_SEARCH(recursos, 'one', 'FeriadosController'), -- "$[3].controller"
            'controller','recursos')
        )
    ) 
FROM PERFIS
WHERE
    JSON_CONTAINS(recursos, '{"controller": "FeriadosController"}');

---

Detailing:

The function...

JSON_SEARCH(recursos, 'one', 'FeriadosController')
-- resultado: "$[3].controller"

...returns the first item ('one') found in the JSON field recursos, containing the value FeriadosController.

This results therefore in the following REPLACE():

REPLACE('"$[3].controller"','controller','recursos')
-- resultado: "$[3].recursos"

...that replaces all that is controller for recursos.

However, pass this result to the JSON_EXTRACT() results in error because he expects a path JSON, which does not support the quotes contained in the string returned by JSON_SEARCH().

To remove the quotes, Mysql then provides the function JSON_UNQUOTE():

JSON_UNQUOTE('"$[3].recursos"')
-- resultado: $[3].recursos

We finally have a path ready-to-use with JSON_EXTRACT():

JSON_EXTRACT(recursos, '$[3].recursos')
-- resultado: ["index", "show", "store", "update", "destroy"]