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.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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 tSee fiddle.