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']

Convertion not needed. WHERE table2.id MEMBER OF table1.array — Akina
– Akina, Commented Jun 16, 2022 at 16:30
@Akina nono.. for example.. in table1, column id >>1 , 2, column name >> kenvin, charles. in table 2, column id >> 1, column target_id >> [1,2]. How can i get a new column in table 2 which display as ['kenvin','charles'] — JJT
– JJT, Commented Jun 16, 2022 at 16:49
Show this everything in the question text. As code-formatted CREATE TABLE + INSERT INTO, and desired output as formatted table. — Akina
– Akina, Commented Jun 16, 2022 at 17:37

Akina · Accepted Answer · 2022-06-17 04:31:01Z

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().

answered Jun 17, 2022 at 4:31

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related