Depuis la version 5.7.8 de MySQL, il est possible de gérer des données au format JSON. Grâce à deux fonctions, nous allons pouvoir exporter le résultat d'une requête en JSON.

Les deux fonctions qui nous intéressent sont : JSON_ARRAYAGG et JSON_OBJECT.

La première a été introduite dans MySQL version 5.7.22, et permet d'gréger un ensemble de résultats en un seul tableau JSON.

JSON_OBJECT est apparu quant à elle un peu plus tôt, à l'arrivée du JSON dans MySQL (5.7.8) et permet de convertir un jeu de résultats en liste de clé/valeur (objet JSON).

Dans cet article et pour l'exemple, je vais utiliser une table basique contenant des utilisateurs :

Pour vous faire gagner du temps, voici le code SQL de création de la table :

CREATE TABLE `users` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `prenom` VARCHAR(255) NOT NULL,
    `nom` VARCHAR(255) NOT NULL,
    `departement` VARCHAR(3) NOT NULL,
    `mail` VARCHAR(255) NOT NULL,
    `nb_connexions` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Et le code d'insertion des données :

INSERT INTO `users` (`id`, `prenom`, `nom`, `departement`, `mail`, `nb_connexions`) VALUES
    (1, 'Jean', 'BOMBEUR', '78', 'jean.bombeur@mail.com', 120),
    (2, 'Alain', 'TERRIEUR', '64', 'a.terrieur@mail.fr', 2),
    (3, 'Alex', 'TERRIEUR', '54', 'alexterrieur@mail.ru', 35),
    (4, 'Mélanie', 'ZETOFRAIS', '02', 'melanisette@mail.fr', 43),
    (5, 'Milène', 'MICOTON', '35', 'milenemicoton@mail.fr', 24),
    (6, 'Larry', 'BAMBEL', '95', 'larrybambel@mail.az', 14),
    (7, 'Jacques', 'OUZI', '24', 'j.ouzi@mail.be', 64),
    (8, 'Axel', 'AIR', '33', 'axelair@mail.fr', 7),
    (9, 'Paul', 'AROHIDE', '91', 'paularoide@mail.zf', 54),
    (10, 'Gérard', 'MENSOIF', '13', 'gegemensoif@mail.fr', 123);

Je souhaite obtenir en une seule requête un tableau JSON de la liste des utilisateurs présents dans la table, suivant cette structure :

[
  {
    "id": 1,
    "nom": "BOMBEUR",
    "mail": "jean.bombeur@mail.com",
    "prenom": "Jean",
    "departement": "78",
    "nb_connexions": 120
  },
  {
    "id": 2,
    "nom": "TERRIEUR",
    "mail": "a.terrieur@mail.fr",
    "prenom": "Alain",
    "departement": "64",
    "nb_connexions": 2
  },
  {
    "id": 3,
    "nom": "TERRIEUR",
    "mail": "alexterrieur@mail.ru",
    "prenom": "Alex",
    "departement": "54",
    "nb_connexions": 35
  },
  ...
]

Nous allons écrire la requête pour transformer chaque ligne en un objet JSON grâce à la fonction JSON_OBJECT :

SELECT 
    JSON_OBJECT(
        'id', id, 
        'prenom', prenom, 
        'nom', nom, 
        'departement', departement, 
        'mail', mail, 
        'nb_connexions', nb_connexions
    ) AS users_json
FROM users;

Voici le résultat obtenu :

Si vous souhaitez obtenir qu'un seul résultat contenant un tableau d'objets JSON, utlisez en plus JSON_ARRAYAGG :

SELECT 
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', id, 
            'prenom', prenom, 
            'nom', nom, 
            'departement', departement, 
            'mail', mail, 
            'nb_connexions', nb_connexions
        )
    ) AS users_json
FROM users;

Résultat :

Après un formattage du JSON retourné :

[
  {
    "id":1,
    "nom":"BOMBEUR",
    "mail":"jean.bombeur@mail.com",
    "prenom":"Jean",
    "departement":"78",
    "nb_connexions":120
  },
  {
    "id":2,
    "nom":"TERRIEUR",
    "mail":"a.terrieur@mail.fr",
    "prenom":"Alain",
    "departement":"64",
    "nb_connexions":2
  },
  {
    "id":3,
    "nom":"TERRIEUR",
    "mail":"alexterrieur@mail.ru",
    "prenom":"Alex",
    "departement":"54",
    "nb_connexions":35
  },
  {
    "id":4,
    "nom":"ZETOFRAIS",
    "mail":"melanisette@mail.fr",
    "prenom":"Mélanie",
    "departement":"02",
    "nb_connexions":43
  },
  {
    "id":5,
    "nom":"MICOTON",
    "mail":"milenemicoton@mail.fr",
    "prenom":"Milène",
    "departement":"35",
    "nb_connexions":24
  },
  {
    "id":6,
    "nom":"BAMBEL",
    "mail":"larrybambel@mail.az",
    "prenom":"Larry",
    "departement":"95",
    "nb_connexions":14
  },
  {
    "id":7,
    "nom":"OUZI",
    "mail":"j.ouzi@mail.be",
    "prenom":"Jacques",
    "departement":"24",
    "nb_connexions":64
  },
  {
    "id":8,
    "nom":"AIR",
    "mail":"axelair@mail.fr",
    "prenom":"Axel",
    "departement":"33",
    "nb_connexions":7
  },
  {
    "id":9,
    "nom":"AROHIDE",
    "mail":"paularoide@mail.zf",
    "prenom":"Paul",
    "departement":"91",
    "nb_connexions":54
  },
  {
    "id":10,
    "nom":"MENSOIF",
    "mail":"gegemensoif@mail.fr",
    "prenom":"Gérard",
    "departement":"13",
    "nb_connexions":123
  }
]

Selon votre problématique, vous pouvez enregistrer la requête SQL dans un fichier query.sql et l'exécuter en ligne de commande pour en sortir un fichier JSON directement (voyez la fonction JSON_PRETTY également) :

mysql -u root -p < query.sql | tail -1 > output.json