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

Question

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.

Yes. strictly MySQL if possible, as other types may cause it to not work. — lyracarat03
– lyracarat03, Commented Jul 13, 2022 at 10:42
What "other types may cause it to not work" ? Are you saying you don't have have a server-side language like PHP available? — bloodyKnuckles
– bloodyKnuckles, Commented Jul 13, 2022 at 10:44

Ajax1234 · Accepted Answer · 2022-07-13 14:53:20Z

2

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.

answered Jul 13, 2022 at 14:53

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

lyracarat03 Over a year ago

I tried the above solution. but it returned me a result of {"roles" : null} instead. Is there a way to replace the current roles JSON string values instead of needing to create an object?

Ajax1234 Over a year ago

@lyracarat03 Can you share your code in a fiddle?

lyracarat03 Over a year ago

Hi, sorry I had a slight typo in my code when i was testing. it is working now. Thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related