How to replace JSON of multiple nested array values ​​in one SELECT statement

Question

I have a table field named values whose current JSON values ​​are as follows:

{"role": ["1","2","3","4"]}

I have another table named roles as follows

id	Role Name
1	administrator
2	finance
3	Payroll
4	Accountant

I tried using the JSON_REPLACE function to replace the ID number in the values JSON string with the role name.

Basically the result should be like this

{"Role": ["Administrator","Finance","Salary","Account"]}

But I can't do it like JSON_REPLACE('["1","2","3","4"]', '$[0]', Admin, '$[1]', Finance) Same as using JSON_REPLACE because the number of IDs and role names may vary, that's why I need to know how to do this in a single SELECT statement.

Answer 1

You can use 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

See fiddle.