How to convert MySQL JSON array to comma separated string and link to related value

Question

I have following id in column

 [1,2]

which the account of ID 1 is Kenvin, and ID2 is Charles How can i get the info like this in a new column:

['Kenvin','charles']

table A

id	account
1	kenvin
2.	charles

table B

id	title	target	table_a_ids
1	abc	4	[1,2]
2	xyz	1	[]
3	ggg	4	[2]

target output: (add a display columns as table_a_accounts in table B)

id	title	target	table_a_ids	table_a_accounts
1	abc	4.	[1,2]	['kenvin','charles']
2	xyz	1	[]	[]
3	ggg	4	[2]	['charles']

Answer 1

SELECT B.id, B.title, B.target, B.table_a_ids, 
       JSON_ARRAYAGG(A.account) table_a_accounts
FROM B
LEFT JOIN A ON A.id MEMBER OF (B.table_a_ids)
GROUP BY B.id, B.title, B.target, B.table_a_ids

If your server is 5.7 then use

SELECT B.id, B.title, B.target, B.table_a_ids, 
       JSON_ARRAYAGG(A.account) table_a_accounts
FROM B
LEFT JOIN A ON JSON_CONTAINS(CAST(B.table_a_ids AS JSON), CAST(A.id AS JSON))
GROUP BY B.id, B.title, B.target, B.table_a_ids

If B.table_a_ids is JSON datatype then remove according CAST().

https://dbfiddle.uk/?rdbms=mysql_8.0&rdbms2=mysql_5.7&fiddle=9aaac43c22b4d82d2752df8d87f221a0

If [null] is not safe for you then use REPLACE().