0

SQL newbie needs help :)

One MySQL table is account_user. Field account_group is JSON array with parent ID's from another table account_group

I want to select users, replacing ID's from account_group field with group names from account_group table.

What I have accomplished yet is:

SELECT
account_user.name,
account_user.email,
account_user.hash,
account_group.name AS `group`

FROM account_user
JOIN account_group ON account_group.id MEMBER OF (JSON_EXTRACT(account_group, '$'))
WHERE account_user.email = 'pavlov@kubik.com.ua'

SELECT result

Is it possible to combine such result set, leaving only unique fields and combining group field to comma separated list or JSON array like this ?

George
  • 25,988
  • 10
  • 79
  • 133
Дмитрий Павлов
  • 1
  • 1
    Just use GROUP_CONCAT(account_group.name) – Thallius Aug 02 '21 at 16:00
  • Thanks for the tip! This solved my problem: `SELECT account_user.id, account_user.name, account_user.email, account_user.hash, GROUP_CONCAT(account_group.name SEPARATOR ', ') AS group FROM account_user JOIN account_group ON account_group.id MEMBER OF (JSON_EXTRACT(account_group, '$')) WHERE account_user.email = 'pavlov@kubik.com.ua' GROUP BY account_user.id` – Дмитрий Павлов Aug 03 '21 at 09:13

0 Answers0