CODEWITHSUNDEEP
mysqlmysql-json
JJT
JJT

Reputation: 91

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

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

Upvotes: 0

Views: 640

Answers (1)

Akina
Akina

Reputation: 42612

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

Upvotes: 1

Related Questions