SQL Many to Many JSON

Question

Here is a lot of similar questions on this regard but I still haven't found how should I build query in that simpliest case:

3 tables: Users, Groups, User_Groups;

id | name      id | title        user_id | group_id 
-----------    -----------       ------------------
1  | Bob       1  | Developers         1 |       2
2  | Jimmy     2  | Admins             1 |       1
3  | Jack                              2 |       1
                                       3 |       2

I want to include additional field users as objects array when I get all groups:

id  | title       | users
------------------------------------------------------------------
  1 | Developers  | [{"id": 1, "name": "Bob"}, {"id": 2, "name": "Jimmy"}]
  2 | Admins      | [{"id": 1, "name": "Bob"}, {"id": 3, "name": "Jack"}]

Oh if you are using MySQL 8.0 you should be able to use `JSON_OBJECT` and `JSON_ARRAY` and i advice you to also look into `JSON_TABLE` not totally related to this question but it is nice to know it exists. — Raymond Nijland, Dec 13 '18 at 16:56
i readed the question wrong i didn't see you needed a result like that.. But waht MySQL version? The modern MySQL versions 5.7.9+ can do it with JSON_OBJECT and JSON_ARRAY.. And on older MySQL versions can do it with a combination with `GROUP_CONCAT`, `CONCAT` and `GROUP BY` — Raymond Nijland, Dec 13 '18 at 16:57
I still haven't seen any use of JSON in SQL that wouldn't be much easier to do in application code. — Bill Karwin, Dec 13 '18 at 17:00
True @BillKarwin also using MySQL to generated JSON data might add overhead and might be using a larger max packet size.. But if you use Mysql 8.0 as document store (under water innodb table with a json datatype column) what is a pretty big table and you would need to join data from other tables with a key/value from JSON data i know you would like the `JSON_TABLE` — Raymond Nijland, Dec 13 '18 at 17:09

Bill Karwin · Accepted Answer · 2018-12-13T16:59:32.980

1

This should work on MySQL 5.7 or later:

SELECT g.id, g.title,
  CONCAT('[',
    GROUP_CONCAT(
      JSON_OBJECT('id', u.id, 'name', u.name)
    ),
  ']') AS users
FROM Groups g
JOIN User_Groups ug ON ug.group_id = g.id
JOIN Users u ON ug.user_id = u.id
GROUP BY g.id

OK I've tested it on a MySQL 5.7 sandbox. I got this output:

+----+------------+-------------------------------------------------------+
| id | title      | users                                                 |
+----+------------+-------------------------------------------------------+
|  1 | Developers | [{"id": 1, "name": "Bob"},{"id": 2, "name": "Jimmy"}] |
|  2 | Admins     | [{"id": 1, "name": "Bob"},{"id": 3, "name": "Jack"}]  |
+----+------------+-------------------------------------------------------+

P.S.: If anyone asks "but how do we do this on 5.6 without JSON functions?" I would suggest you fetch the unformatted data into your app and format JSON using code. It's not a good use of your time to figure out how to format JSON using only SQL expressions.

edited Dec 13 '18 at 16:59

answered Dec 13 '18 at 16:47

Bill Karwin

538,548
86
673
828

Thank you. I should get more familiar with sql syntax:) – loonskai Dec 13 '18 at 16:53
1

@RaymondNijland I find a reference to `JSON_OBJECT` in the release notes for 5.7.9, which was the first GA release. https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-9.html – Bill Karwin Dec 13 '18 at 16:56
"how do we do this on 5.6 without JSON functions?" On older MySQL versions you can do simply it with a combination with GROUP_CONCAT, CONCAT and GROUP BY but you right it's messy because you need to know how to write valid JSON syntax.here is a [example](https://stackoverflow.com/questions/49281063/how-to-produce-a-valid-json-output-from-multiple-mysql-tables/49281625#49281625) – Raymond Nijland Dec 13 '18 at 17:02
@RaymondNijland, If you're going to delete your comments, I'm not going to bother replying to them. – Bill Karwin Dec 13 '18 at 17:07
thats fair enough, the last comments are there to stay. – Raymond Nijland Dec 13 '18 at 17:11

SQL Many to Many JSON

1 Answers1