如何在一个SELECT语句中替换多个嵌套数组值的JSON

Question

我有一个名为 values 的表字段，其当前 JSON 值如下：

{"角色": ["1","2","3","4"]}

我还有另一个名为 roles 的表，如下

id	角色名称
1	管理员
2	金融
3	工资单
4	会计师

我尝试使用 JSON_REPLACE 函数将 values JSON 字符串中的 ID 号替换为角色名称。

基本上应该有这样的结果

{"角色": ["管理员","财务","工资","账户"]}

但我不能像 JSON_REPLACE('["1","2","3","4"]', '$[0]', Admin, '$[1]', Finance) 一样使用 JSON_REPLACE因为 ID 的数量和角色名称可能会有所不同，这就是为什么我需要知道如何在单个 SELECT 语句中执行此操作。

Answer 1

您可以使用json_table：

select json_object('role', (select json_arrayagg(r1.role_name) 
  from json_table(t.value, '$.roles[*]' columns(role int path '$')) r 
  join roles r1 on r.role = r1.id)) 
from vals t

请参阅小提琴。