MySQL 5 join from integers in json array

Question

Is there any way to accomplish this with MySQL 5?

I have two tables users and roles

users

 desc users;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| data  | json    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)


 select * from users;
 +------+------------------------------------+
 | id   | data                               |
 +------+------------------------------------+
 |    1 | {"name": "cat", "roleIds": [2, 3]} |
 |    2 | {"name": "dog", "roleIds": [1, 4]} |
 |    3 | {"name": "mouse", "roleIds": [5]}  |
 +------+------------------------------------+
 3 rows in set (0.00 sec)

roles

desc roles;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| role_id | int(11)     | NO   |     | 0       |       |
| name    | varchar(64) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

select * from roles;
+---------+-------------+
| role_id | name        |
+---------+-------------+
|       1 | Admin       |
|       2 | Supervisor  |
|       3 | Provisioner |
|       4 | Operator    |
|       5 | Relief      |
+---------+-------------+
5 rows in set (0.01 sec)

I want to amend the json data in the users table with the role names with results looking like this

+------+------------------------------------------------------------------------------+
| id   | data                                                                         |
+------+------------------------------------------------------------------------------+
|    1 | {"name": "cat", "roleIds": [2, 3], "roleNames": "[Supervisor, Provisioner]"} |
|    2 | {"name": "dog", "roleIds": [1, 4], "roleNames": "[Admin, Operator]"}         |
|    3 | {"name": "mouse", "roleIds": [5], "roleNames": "[Relief]"}                   |
+------+------------------------------------------------------------------------------+

That would be that much easier if the schema was properly normalized... — sticky bit
– sticky bit, Commented Dec 19, 2020 at 1:03

GMB · Accepted Answer · 2020-12-19 01:21:45Z

1

Here is one approach using json_set(), json_contains() and json_arrayagg(), which are all available in MySQL 5.7 (the latter was introduced in version 5.7.22):

select u.id,
    json_set(
        u.data,
        '$.roleNames', (
            select json_arrayagg(r.name)
            from roles r
            where json_contains(u.data, cast(r.role_id as json), '$.roleIds')
        )
    ) as data
from users u

Demo on DB Fiddle:

id	data
1	{"name": "cat", "roleIds": [2, 3], "roleNames": ["Supervisor", "Provisioner"]}
2	{"name": "dog", "roleIds": [1, 4], "roleNames": ["Admin", "Operator"]}
3	{"name": "mouse", "roleIds": [5], "roleNames": ["Relief"]}

It should be pinpointed that MySQL does not guarantee the order in which elements appear in arrays generated by json_arrayagg(). This is a limitation that we have to live with as of the current database version.

edited Dec 19, 2020 at 1:21

answered Dec 19, 2020 at 1:06

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

slashdottir Over a year ago

Unfortunately I found out I have to use MySQL 5.7.12. JSON_ARRAYAGG didn't come out till 5.7.22. Is there any way to do it without that command? Thank you!

Collectives™ on Stack Overflow

MySQL 5 join from integers in json array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related