Passer au contenu
SQLite

Modification de données JSON dans SQLite

Introduction

Après avoir stocké et interrogé des données JSON dans SQLite, vous aurez probablement besoin de les modifier. SQLite offre plusieurs fonctions pour mettre à jour, ajouter ou supprimer des éléments dans vos structures JSON sans avoir à réécrire l’intégralité du document.

Les fonctions de modification JSON

SQLite propose trois fonctions principales pour modifier des données JSON :

  1. json_set() - Remplace les valeurs existantes ou ajoute de nouveaux éléments s’ils n’existent pas
  2. json_insert() - Ajoute de nouveaux éléments uniquement si les chemins spécifiés n’existent pas déjà
  3. json_remove() - Supprime des éléments aux chemins spécifiés

Modification avec json_set()

La fonction json_set() est la plus polyvalente, car elle permet à la fois de modifier des valeurs existantes et d’ajouter de nouveaux éléments.

Syntaxe de base

json_set(json, path1, value1, path2, value2, ...)

Exemples d’utilisation

Considérons cette table d’utilisateurs avec des profils JSON :

CREATE TABLE utilisateurs (
    id INTEGER PRIMARY KEY,
    profil TEXT NOT NULL
);


INSERT INTO utilisateurs (profil) VALUES
('{"nom":"Alice", "age":28, "email":"[email protected]", "adresse":{"ville":"Paris", "code_postal":"75001"}}');

Modifier une valeur simple

-- Mettre à jour l'âge d'Alice
UPDATE utilisateurs
SET profil = json_set(profil, '$.age', 29)
WHERE json_extract(profil, '$.nom') = 'Alice';

Modifier plusieurs valeurs à la fois

-- Mettre à jour l'âge et l'email simultanément
UPDATE utilisateurs
SET profil = json_set(profil,
                     '$.age', 30,
                     '$.email', '[email protected]')
WHERE id = 1;

Modifier une valeur imbriquée

-- Mettre à jour le code postal dans l'objet adresse
UPDATE utilisateurs
SET profil = json_set(profil, '$.adresse.code_postal', '75002')
WHERE id = 1;

Ajouter un nouvel élément

-- Ajouter un numéro de téléphone
UPDATE utilisateurs
SET profil = json_set(profil, '$.telephone', '+33123456789')
WHERE id = 1;

Ajouter un objet imbriqué

-- Ajouter des préférences sous forme d'objet
UPDATE utilisateurs
SET profil = json_set(profil,
                     '$.preferences',
                     json_object('theme', 'sombre', 'notifications', 'activées'))
WHERE id = 1;

Ajout conditionnel avec json_insert()

Contrairement à json_set(), la fonction json_insert() n’écrase pas les valeurs existantes. Elle ajoute uniquement de nouveaux éléments si les chemins spécifiés n’existent pas déjà.

Syntaxe

json_insert(json, path1, value1, path2, value2, ...)

Exemples

-- Tenter d'ajouter un numéro de téléphone et de modifier l'âge
UPDATE utilisateurs
SET profil = json_insert(profil,
                        '$.telephone', '+33987654321',
                        '$.age', 40)
WHERE id = 1;

Dans cet exemple :

  • Si $.telephone n’existe pas, il sera ajouté avec la valeur spécifiée
  • Si $.age existe déjà (ce qui est le cas), sa valeur ne changera pas

C’est utile lorsque vous voulez ajouter des champs sans risquer d’écraser des données existantes.

Suppression d’éléments avec json_remove()

Pour supprimer des éléments d’un objet JSON, utilisez la fonction json_remove().

Syntaxe

json_remove(json, path1, path2, ...)

Exemples

-- Supprimer le champ email
UPDATE utilisateurs
SET profil = json_remove(profil, '$.email')
WHERE id = 1;
-- Supprimer plusieurs champs à la fois
UPDATE utilisateurs
SET profil = json_remove(profil, '$.telephone', '$.preferences.notifications')
WHERE id = 1;

Dans le deuxième exemple, nous supprimons à la fois le champ telephone et le champ notifications à l’intérieur de l’objet preferences.

Combiner plusieurs opérations

Vous pouvez enchaîner les fonctions JSON pour effectuer des opérations complexes :

-- Enchaînement de plusieurs opérations
UPDATE utilisateurs
SET profil = json_set(
                json_remove(profil, '$.preferences.theme'),
                '$.preferences.langue', 'fr',
                '$.derniere_connexion', datetime('now')
             )
WHERE id = 1;

Cette requête :

  1. Supprime le champ theme des préférences
  2. Ajoute un champ langue dans les préférences
  3. Ajoute un horodatage de dernière connexion

Travailler avec des tableaux JSON

Ajouter des éléments à un tableau

Pour ajouter un élément à un tableau existant, vous devez extraire le tableau, le modifier, puis le réinsérer :

-- Ajouter un intérêt à un tableau d'intérêts
UPDATE utilisateurs
SET profil = json_set(
                profil,
                '$.interets',
                json_array(
                    SELECT value FROM json_each(json_extract(profil, '$.interets'))
                    UNION ALL
                    SELECT 'photographie'
                )
             )
WHERE id = 1 AND json_type(profil, '$.interets') = 'array';

Supprimer un élément d’un tableau

Pour supprimer un élément d’un tableau, vous pouvez utiliser une approche similaire :

-- Supprimer 'musique' des intérêts
UPDATE utilisateurs
SET profil = json_set(
                profil,
                '$.interets',
                json_array(
                    SELECT value FROM json_each(json_extract(profil, '$.interets'))
                    WHERE value != 'musique'
                )
             )
WHERE id = 1 AND json_type(profil, '$.interets') = 'array';

La fonction json_patch()

SQLite propose également la fonction json_patch() qui applique des modifications selon les spécifications du RFC 7396 pour JSON Patch.

Syntaxe

json_patch(target_json, patch_json)

Exemple

-- Appliquer un patch à un profil
UPDATE utilisateurs
SET profil = json_patch(
                profil,
                '{"age": 32, "adresse": {"ville": "Lyon"}, "preferences": null}'
             )
WHERE id = 1;

Ce patch fait trois modifications :

  1. Met à jour l’âge à 32
  2. Change la ville à Lyon (mais conserve les autres propriétés de l’adresse comme le code postal)
  3. Supprime complètement l’objet preferences (en le remplaçant par null)

Exemple complet : Système de gestion d’inventaire

Voici un exemple plus complet qui démontre comment utiliser les fonctions de modification JSON dans un système d’inventaire :

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


-- Insérer un produit initial
INSERT INTO produits (details) VALUES
('{"nom":"Smartphone XYZ", "prix":499.99, "caracteristiques":["Écran 6.5\"", "128 Go"], "stock":{"entrepot_A":45, "entrepot_B":32}}');


-- 1. Mettre à jour le prix
UPDATE produits
SET details = json_set(details, '$.prix', 449.99)
WHERE json_extract(details, '$.nom') = 'Smartphone XYZ';


-- 2. Ajouter une nouvelle caractéristique
UPDATE produits
SET details = json_set(
                details,
                '$.caracteristiques',
                json_array(
                    SELECT value FROM json_each(json_extract(details, '$.caracteristiques'))
                    UNION ALL
                    SELECT 'Batterie 5000mAh'
                )
             )
WHERE id = 1;


-- 3. Diminuer le stock après une vente
UPDATE produits
SET details = json_set(
                details,
                '$.stock.entrepot_A',
                CAST(json_extract(details, '$.stock.entrepot_A') AS INTEGER) - 5
             )
WHERE id = 1;


-- 4. Ajouter un nouveau champ pour la date de dernière mise à jour
UPDATE produits
SET details = json_set(details, '$.derniere_mise_a_jour', datetime('now'))
WHERE id = 1;


-- 5. Marquer comme indisponible si le stock total est épuisé
UPDATE produits
SET details = json_set(
                details,
                '$.disponible',
                (CAST(json_extract(details, '$.stock.entrepot_A') AS INTEGER) +
                 CAST(json_extract(details, '$.stock.entrepot_B') AS INTEGER)) > 0
             )
WHERE id = 1;

Cet exemple démontre une séquence d’opérations typiques pour la gestion d’un inventaire :

  1. Mise à jour du prix
  2. Ajout d’une caractéristique
  3. Ajustement du stock
  4. Ajout d’un horodatage de mise à jour
  5. Détermination automatique de la disponibilité

Validation après modification

Il est important de vérifier que vos opérations de modification produisent toujours du JSON valide :

-- Vérifier la validité après une mise à jour
UPDATE utilisateurs
SET profil =
    CASE
        WHEN json_valid(json_set(profil, '$.age', 'trente')) THEN json_set(profil, '$.age', 'trente')
        ELSE profil
    END
WHERE id = 1;

Bien que cet exemple spécifique produise du JSON valide, c’est une bonne pratique de vérifier la validité lors de modifications complexes.

Bonnes pratiques

  1. Utiliser les fonctions appropriées :

    • json_set() pour mettre à jour ou ajouter des champs
    • json_insert() pour ajouter uniquement des champs manquants
    • json_remove() pour supprimer des champs

  2. Transactions : Encapsulées les modifications complexes dans des transactions.

  3. Vérifier le type : Utilisez json_type() pour vérifier le type des champs avant de les modifier.

  4. Contrôle de validation : Utilisez json_valid() pour vous assurer que vos modifications produisent un JSON valide.

  5. Minimiser les mises à jour : Essayez de combiner plusieurs modifications en une seule mise à jour pour de meilleures performances.

Prochaines étapes

Maintenant que vous savez modifier des données JSON, vous pouvez explorer les fonctions d’agrégation JSON pour travailler avec des collections de données.