How to JSON replace multiple nested array values in one single SELECT statement-CodePudding

I have table field called values which has a current JSON value of the following:

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

I also have another table called roles as below

id	role_name
1	Admin
2	Finance
3	Payroll
4	Accountant

I am trying to use the JSON_REPLACE function to replace the id numbers in the values JSON string with the role names.

Basically it should have a result like this

{"roles": ["Admin","Finance","Payroll","Account"]}

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

CodePudding user response：

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.