mysql - 选择在 where 中过滤的 JSON 部分

Question

在数据库中，我有一个名为资源的字段，在其中我保留用户的权限，格式为 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"
 }
]

当我运行下面的查询时，它会正确返回哪个用户拥有权限:

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

几乎就在那里，通过上面的选择，我得到了我正在寻找的记录行，问题是它向我显示了资源字段中的整个 json 集合。 (当然他要这样做，这就是我要求运行的)。

因此，为了给我带来特定 Controller 中用户所允许的权限，我进行了以下选择:

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

返回是:

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

也就是说，一切正常，它显示了用户在给定 Controller 上拥有的权限，问题是它显示了第一行的 JSON 集合的权限 '$[0].recursos' 但这并不完全是我想要的，我想返回:

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

用户对 Controller 有什么权限HolidaysController

也就是说，我能够扫描 Controller 以在json中搜索，但我无法显示搜索到的json部分，有人对此有解决方案，jpa经历过这个吗？

Answer 1

一种选择是使用以下查询(根据需要进行调整):

SELECT
  JSON_EXTRACT(
    `recursos`,
    JSON_UNQUOTE(
      REPLACE(
        JSON_SEARCH(
          `recursos`,
          'one',
          'FeriadosController',
          NULL,
          '$[*].controller'
        ), 'controller', 'recursos'
      )
    )
  ) `permissions`
FROM
  `perfis`
WHERE
  JSON_CONTAINS(recursos, '{"controller": "FeriadosController"}');

参见db-fiddle .