如何在一個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

請參考小提琴。