JavaScript Object Notation avec SQL

#json#json_contains#json_extract#mysql#sql#structured-data#strukturierte-daten

Les bases de données conviennent parfaitement au stockage structuré de données. Il arrive toutefois que, pour diverses raisons, cela ne suffise pas et que l'on veuille plutôt stocker des structures de données plus complexes via SQL. On veut par exemple enregistrer dans la base des métadonnées d'une image (les fameuses données EXIF) et, pour simplifier, supposons qu'il s'agisse des valeurs suivantes :

Distance focale
Temps d'exposition
Informations sur l'auteur

Nous pourrions soit créer des champs de table pour chaque valeur

CREATE TABLE `fotos` (
  `brennweite` INT,
  `belichtungszeit` INT,
  `urheber` VARCHAR
);

soit écrire les données au format JSON dans une colonne de table

{"Brennweite": 55, "Belichtungszeit": 120, "Urheber": "devdave"}

Le véritable avantage du format JSON est que les instances d'objets se (dé)sérialisent rapidement vers/depuis JSON. Si l'on a par exemple en PHP un objet

class Foto {
  public $brennweite = 55;
  public $belichtungszeit = 120;
  public $urheber = "devdave";
}
$foto = new Foto();

nous pouvons le sérialiser rapidement via

json_encode($foto)

et avoir l'instance sous forme de chaîne JSON, que l'on peut enregistrer dans la base. Répercuter dans la base chaque modification de la structure de la classe Foto est assez fastidieux et laisse de la place à des erreurs. Il serait donc judicieux, dans ce cas, d'enregistrer les données directement au format JSON dans la base. Mais comment accéder ensuite à ces données ?

1. JSON_EXTRACT(%DATEN%, %PFAD%)

Avec JSON_EXTRACT, il est possible d'extraire des données de la chaîne JSON. Pour cela, on indique en premier paramètre la chaîne JSON qui fournit nos données. Dans notre cas, ce serait le nom de la colonne contenant les métadonnées. Sous « chemin », on indique le chemin vers l'attribut à renvoyer. Supposons que notre chaîne JSON ressemble à ceci :

{"Brennweite": 55, "Belichtungszeit": 120, "Urheber": "devdave"}

nous pouvons alors accéder à la valeur « Urheber » (auteur) ainsi :

SELECT
JSON_EXTRACT('{
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Urheber": "devdave"
}', '$.Urheber');

On peut de même accéder à des niveaux plus profondément imbriqués. Si le champ « Urheber » se trouve sous « Personen », on peut y accéder ainsi :

SELECT
JSON_EXTRACT('{
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Personen": ["Urheber": "devdave", "Model": "Hannah"]
}', '$.Personen.Urheber');

On accède aux listes de valeurs par indice. Si nous avons plusieurs auteurs, nous pouvons accéder au premier (= « devdave ») ainsi :

SELECT JSON_EXTRACT(
  '{"Brennweite": 55, "Belichtungszeit": 120,
  "Personen": ["Urheber": {"devdave", "john wayne"},
  "Model": "Hannah"]}',
  '$.Personen.Urheber[0]');

2. JSON_CONTAINS(%HAYSTACK%, %NEEDLE%, '%PATH%')

JSON_CONTAINS renvoie des informations sur le nombre de fois où une valeur se trouve sous une clé. Comme toujours, on indique d'abord la chaîne JSON, puis l'élément recherché (ci-dessous 120 ou Max), puis le chemin (hypothétique) vers l'élément.

JSON_CONTAINS({
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Personen": [
    "Urheber": {"devdave", "john wayne"},
    "Model": "Hannah"
  ]
},
120,
'$.Belichtungszeit');

renvoie 1.

JSON_CONTAINS({
  "Brennweite": 55,
  "Belichtungszeit": 120,
  "Personen": [
    "Urheber": {"devdave", "john wayne"},
    "Model": "Hannah"
  ]
},
"Max",
'$.Personen.Urheber[0]');

renvoie 0.

Sources : dev.mysql.com/doc/refman/8.0/en/json-search-functions.html