select part of the JSON that was filtered in the where

Question

select part of the JSON that was filtered in the where

Navigation

#1 by (1 votes)

1

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

[  
 {  
    "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 return of which user is allowed:

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

Almost there, with select up I get the record line I'm looking for, the problem is that it shows me the whole json collection that is in the resources field. (Of course he was going to do this, that's what I asked to run).

So for it to bring me the permissions that are allowed for a user in a given controller, I made the select 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 certain controller, THE PROBLEM is that it show the permissions of the JSON collection of the first line '$[0].recursos' but that's not quite what I'm wanting, I want to return :

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

What is the permission that the user has for the controller FeriadosController

So, I'm getting the controller to scan json, but I'm not able to show the part of json that was searched, does anyone have a solution for that, jpa went through this?

mysql json

asked by anonymous 26.10.2017 / 03:42

1 answer

Freight calculation Post office + PHP How do I pass an array in HTML attributes and not show single quotes?

score 1 · Accepted Answer

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

Assuming the JSON structure is known, search HolidaysController using JSON_SEARCH() and manipulate the result to replace "controller" with "resources". Use JSON_UNQUOTE() to remove the quotation marks that MySQL places in the result and then move everything to 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 recursos field, which contains the value FeriadosController .

This results, therefore, in the following REPLACE ():

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

... that replaces everything that is controller with recursos .

However, passing this result to JSON_EXTRACT() results in an error because it expects a path JSON, which does not support the quotation marks contained in the string returned by JSON_SEARCH() .

To get quotes, MySQL then provides the JSON_UNQUOTE() function:

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

Finally we have a path ready for use with JSON_EXTRACT() :

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