Stockage de données JSON dans SQLite

Introduction

SQLite ne possède pas de type de données JSON natif, mais il peut stocker des données JSON sous forme de texte dans des colonnes de type TEXT. Malgré cette simplicité apparente, SQLite offre un ensemble riche de fonctions pour manipuler ces données JSON.

Créer une table pour stocker du JSON

Commençons par créer une table qui contiendra des données JSON :

CREATE TABLE utilisateurs (
    id INTEGER PRIMARY KEY,
    nom TEXT NOT NULL,
    profil TEXT NOT NULL -- Stockera des données JSON
);

Dans cet exemple, la colonne profil est définie comme TEXT et stockera nos données JSON.

Insérer des données JSON

Pour insérer des données JSON dans SQLite, il suffit d’insérer une chaîne JSON valide dans la colonne de type TEXT :

INSERT INTO utilisateurs (nom, profil)
VALUES (
    'Alice',
    '{"age": 28, "email": "[email protected]", "interests": ["programmation", "musique", "voyages"]}'
);

Ce code insère un enregistrement pour un utilisateur nommé Alice avec un profil JSON qui inclut son âge, son email et ses centres d’intérêt.

Validation du JSON

SQLite fournit la fonction json_valid() pour vérifier si une chaîne est un JSON valide :

SELECT json_valid('{"age": 28, "email": "[email protected]"}');
-- Retourne 1 (vrai)

SELECT json_valid('{"age": 28, email: "incomplet"}');
-- Retourne 0 (faux) car les guillemets manquent autour de "email"

Il est recommandé de valider vos données JSON avant de les insérer :

-- Approche sécurisée pour insérer du JSON
INSERT INTO utilisateurs (nom, profil)
SELECT 'Bob', '{"age": 35, "email": "[email protected]", "interests": ["sport", "cuisine"]}'
WHERE json_valid('{"age": 35, "email": "[email protected]", "interests": ["sport", "cuisine"]}');

Formatage des données JSON

Si vous avez besoin de formater des données en JSON, vous pouvez utiliser différentes fonctions :

json_object() : Créer un objet JSON

SELECT json_object('nom', 'Charlie', 'age', 42, 'email', '[email protected]');
-- Retourne '{"nom":"Charlie","age":42,"email":"[email protected]"}'

Cette fonction prend une série de paires clé-valeur et génère un objet JSON.

json_array() : Créer un tableau JSON

SELECT json_array('rouge', 'vert', 'bleu', 42, 3.14);
-- Retourne '["rouge","vert","bleu",42,3.14]'

Cette fonction prend une liste de valeurs et génère un tableau JSON.

json_quote() : Échapper une valeur pour JSON

SELECT json_quote('Texte avec "guillemets"');
-- Retourne '"Texte avec \"guillemets\""'

Cette fonction est utile pour échapper correctement des chaînes de caractères afin de les inclure dans du JSON.

Exemple complet : Création et stockage de données JSON

Voici un exemple complet qui utilise les fonctions de création JSON pour générer et stocker des données :

-- Créer la table
CREATE TABLE produits (
    id INTEGER PRIMARY KEY,
    details TEXT NOT NULL
);

-- Insérer un produit avec des détails JSON créés à l'aide de json_object()
INSERT INTO produits (details)
VALUES (
    json_object(
        'nom', 'Smartphone XYZ',
        'prix', 499.99,
        'caracteristiques', json_array('Écran 6.5"', '128 Go', '8 Go RAM', 'Caméra 48MP'),
        'disponible', 1,
        'dimensions', json_object('longueur', 155, 'largeur', 75, 'epaisseur', 7.8)
    )
);

Ce code crée un objet JSON complexe avec des valeurs imbriquées pour représenter les détails d’un produit.

Bonnes pratiques

Validation : Utilisez toujours json_valid() pour vérifier que vos données sont du JSON valide.
Structure cohérente : Même si JSON est flexible, essayez de maintenir une structure cohérente pour faciliter les requêtes.
Normalisation : N’utilisez pas JSON pour tout. Si certaines données sont fréquemment recherchées ou filtrées, envisagez de les stocker dans des colonnes séparées.
Indexation : Notez que SQLite ne peut pas indexer directement le contenu JSON, ce qui peut affecter les performances des requêtes.

Prochaines étapes

Maintenant que vous savez stocker des données JSON dans SQLite, l’étape suivante consiste à apprendre comment interroger et extraire ces données.