MYSQL CONCAT + GROUP_CONCAT + LEFT OUTER JOIN

Question

I'm trying to make a link between 2 tables on mySQL but, i think it's a little bit harder than i thought.

I have 3 tables * One which registers my rules informations * One which registers my transfers informations * One which make the pivot between the two first.

CREATE TABLE `rules` (
  `id` int,
  `Name` varchar(10)
);

INSERT INTO `rules` (`id`, `name`) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');

CREATE TABLE `pivot` (
  `id_rule` int,
  `id_transfert` int
);

INSERT INTO `pivot` (`id_rule`, `id_transfert`) VALUES
(1,1),
(1,2),
(2,1),
(2,2),
(2,3);

CREATE TABLE `transferts` (
  `id` int,
  `aeroport` varchar(50),
  `station` varchar(50)
);

INSERT INTO `transferts` (`id`, `aeroport`,`station`) VALUES
(1,'GVA','Flaine'),
(2,'GNB','La Tania'),
(3,'GNB','Flaine');

What i'm trying to do is to get all my rules with a column which gather all linked transfers as a JSON string. Like below

------------------------------------------------------------------------
|  id   |  name  |  transferts                                         |
------------------------------------------------------------------------
|  1    |   a    | {"GVA": "Flaine"}                                   |
------------------------------------------------------------------------
|  2    |   b    | {"GVA": "Flaine", "GNB": "Flaine", "La Tania"}      |
------------------------------------------------------------------------

What i do actually is this :

SELECT
      rule.id, rule.name,GROUP_CONCAT(stations.transferts SEPARATOR ",") as transferts
                FROM
                    rules rule
                LEFT OUTER  JOIN  
                    pivot pivot 
                on
                    (pivot.id_rule = rule.id)
                LEFT OUTER  JOIN  
                    (
                        SELECT id, 
                        CONCAT(aeroport, ":",
                        GROUP_CONCAT(station)
                        ) AS transferts
                        FROM transferts
                        GROUP BY aeroport
                    ) stations         
                on
                    (pivot.id_transfert = stations.id)
                GROUP BY
                    rule.id

But this is returning me a "null" value. I don't see what i'm doing wrong. Is there someone who can help me please ?

FYI, I was inspired by this link MySQL: GROUP_CONCAT with LEFT JOIN

Answer 1

With a MySQL version prior to 5.7.22 you can't use the JSON built-in functions.

You'll have to use a few imbricated GROUP_CONCAT subqueries to obtain your JSON string.

As told in the comments, your expected JSON string is not valid. The following answer will differ from your expected result, to fix this issue.

I suggest you proceed with a first query to get a column with the "aeroport" names, and another column with the associated stations formatted as a list, for each couple of "rule.id + aeroport_name".

This gives the following query:

mysql> select rules.id, name, concat ('"', aeroport, '":') as aeroport_name, group_concat('"', station, '"') as station_list
    -> from rules
    -> inner join pivot on rules.id = pivot.id_rule
    -> inner join transferts on pivot.id_transfert = transferts.id
    -> group by rules.id, aeroport_name;
+------+------+---------------+---------------------+
| id   | name | aeroport_name | station_list        |
+------+------+---------------+---------------------+
|    1 | a    | "GNB":        | "La Tania"          |
|    1 | a    | "GVA":        | "Flaine"            |
|    2 | b    | "GNB":        | "La Tania","Flaine" |
|    2 | b    | "GVA":        | "Flaine"            |
+------+------+---------------+---------------------+
4 rows in set (0,00 sec)

Then, we are going to use this query as a subquery to associate each "station_list" to its given aeroport, in a rule id context, within a single string.

This give the following encapsulation:

mysql> select id, name, group_concat(aeroport_name, '[', station_list, ']') as aeroport_list
    -> from (
    ->    select rules.id, name, concat ('"', aeroport, '":') as aeroport_name, group_concat('"', station, '"') as station_list
    ->    from rules
    ->    inner join pivot on rules.id = pivot.id_rule
    ->    inner join transferts on pivot.id_transfert = transferts.id
    ->    group by rules.id, aeroport_name
    -> ) as isolated group by id;
+------+------+----------------------------------------------+
| id   | name | aeroport_list                                |
+------+------+----------------------------------------------+
|    1 | a    | "GNB":["La Tania"],"GVA":["Flaine"]          |
|    2 | b    | "GNB":["La Tania","Flaine"],"GVA":["Flaine"] |
+------+------+----------------------------------------------+
2 rows in set (0,00 sec)

And finally, we can now add the final "{}" encapsulation to our string, by adding a top level query over this:

mysql> select id, name, concat('{', aeroport_list, '}') as conf                                                                                                                                                                                   
    -> from (
    ->    select id, name, group_concat(aeroport_name, '[', station_list, ']') as aeroport_list
    ->    from (
    ->       select rules.id, name, concat ('"', aeroport, '":') as aeroport_name, group_concat('"', station, '"') as station_list
    ->       from rules
    ->       inner join pivot on rules.id = pivot.id_rule
    ->       inner join transferts on pivot.id_transfert = transferts.id
    ->       group by rules.id, aeroport_name
    ->    ) as isolated group by id
    -> ) as full_list;
+------+------+------------------------------------------------+
| id   | name | conf                                           |
+------+------+------------------------------------------------+
|    1 | a    | {"GNB":["La Tania"],"GVA":["Flaine"]}          |
|    2 | b    | {"GNB":["Flaine","La Tania"],"GVA":["Flaine"]} |
+------+------+------------------------------------------------+
2 rows in set (0,01 sec)