Stocker et Interroger JSON dans SQLite : Exemples et Meilleures Pratiques

JSON, ou JavaScript Object Notation, est un format pour stocker et échanger des données. C’est un format léger, basé sur du texte et lisible par l’homme qui est facile à analyser et générer pour les machines. Dans SQLite, JSON peut être stocké et interrogé de plusieurs façons différentes.

Utilisation de l’Extension JSON1

Une façon de stocker JSON dans SQLite est d’utiliser l’extension JSON1, qui fournit des fonctions pour encoder et décoder JSON. Pour utiliser l’extension JSON1, vous devez d’abord l’activer en exécutant la commande suivante :

SELECT load_extension('/path/to/json1/extension');

Une fois l’extension activée, vous pouvez créer une table qui a une colonne de type JSON en utilisant l’instruction SQL suivante :

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data JSON
);

Insertion de Données JSON

Pour insérer une valeur JSON dans cette table, vous pouvez utiliser la fonction json_encode fournie par l’extension JSON1, comme ceci :

INSERT INTO users (id, data) VALUES (1, json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
}));

Interrogation de Données JSON

Pour interroger cette table, vous pouvez utiliser la fonction json_extract, qui vous permet d’extraire des valeurs de la colonne JSON en spécifiant un chemin JSON. Par exemple, pour obtenir l’adresse e-mail de l’utilisateur avec l’id 1, vous pouvez utiliser la requête suivante :

SELECT json_extract(data, '$.email') AS email
FROM users
WHERE id = 1;

Stocker JSON dans BLOB

Une autre façon de stocker et d’interroger JSON dans SQLite est d’utiliser le type BLOB. Dans ce cas, vous pouvez créer une table avec une colonne BLOB comme ceci :

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data BLOB
);

Insertion de JSON dans une Colonne BLOB

Pour insérer une valeur JSON dans cette table, vous pouvez utiliser la fonction json_encode pour encoder la valeur comme une chaîne, puis utiliser la fonction hex pour la convertir en BLOB, comme ceci :

INSERT INTO users (id, data) VALUES (1, hex(json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
})));

Interrogation de JSON dans une Colonne BLOB

Pour interroger cette table, vous pouvez utiliser la fonction JSON_EXTRACT fournie par l’extension JSON1, mais vous devez convertir la valeur BLOB en chaîne en utilisant la fonction unhex, comme ceci :

SELECT json_extract(unhex(data), '$.email') AS email
FROM users
WHERE id = 1;

Résumé

En conclusion, SQLite fournit deux façons de stocker et d’interroger les valeurs JSON : en utilisant l’extension JSON1 et en utilisant le type BLOB. Les deux approches ont leurs propres avantages et inconvénients, et celle que vous choisissez dépendra de vos besoins spécifiques.

Si vous devez convertir des données JSON en instructions SQL INSERT, consultez notre outil gratuit Convertisseur JSON vers SQL.