Left join to JSON array

Question

in the good times, it was easy to do a join to user - user_comments:

SELECT * FROM user LEFT JOIN user_comments WHERE user.id = user_comments.USER_ID

users:
1, 'name1'
2, 'name1'

users_comments
user_id, comment
1      , 'fdfddf'
1      , 'bvvccv'
2      , 'zxczxczxc'

but here comes a JSON array. Users:

1, 'username'
2, 'username2'

and comments:

'comment', [1]
'comment2', [1,2]

the 2nd column is a JSON array. So, no more records but an array entry in the 2nd column. (ok, I know its not 100% clear, but in this imaginative example lets imagine that a comments may belong to two users).

How to do a left join, and preferably count the number of users?

Answer 1

Did you want this? Hope I did not mistake your question;)

select c.comment, count(u.id) as userCount
from user_comments c
left join user u on find_in_set(u.id, replace(replace(c.USER_ID, '[', ''), ']', ''))
group by c.comment