Format JSONB pour les performances dans SQLite

Introduction

À partir de SQLite 3.45.0 (janvier 2024), une nouvelle fonctionnalité a été introduite pour améliorer significativement les performances lors de l’utilisation de JSON : le format JSONB. Ce format binaire permet de stocker la représentation interne du JSON directement dans la base de données, évitant ainsi les coûts d’analyse et de rendu lors de la lecture et de la modification des données JSON.

Avantages du format JSONB

Le format JSONB offre plusieurs avantages par rapport au JSON texte traditionnel :

Performance : Peut être plusieurs fois plus rapide pour les opérations de lecture et d’écriture
Espace disque : Utilise généralement moins d’espace de stockage
Validation préalable : Les données sont déjà validées et analysées
Cohérence : Garantit que les données sont toujours du JSON valide

Prérequis

Pour utiliser JSONB, vous devez :

Utiliser SQLite version 3.45.0 ou ultérieure
Comprendre les concepts de base du JSON dans SQLite

Fonctions JSONB disponibles

SQLite propose des versions JSONB de la plupart des fonctions JSON standard :

Fonction JSON	Équivalent JSONB
`json()`	`jsonb()`
`json_array()`	`jsonb_array()`
`json_extract()`	`jsonb_extract()`
`json_insert()`	`jsonb_insert()`
`json_replace()`	`jsonb_replace()`
`json_set()`	`jsonb_set()`
`json_remove()`	`jsonb_remove()`
`json_object()`	`jsonb_object()`
`json_patch()`	`jsonb_patch()`
`json_group_array()`	`jsonb_group_array()`
`json_group_object()`	`jsonb_group_object()`

Conversion entre JSON et JSONB

Convertir du texte JSON vers JSONB

Pour convertir une chaîne JSON en format JSONB, utilisez la fonction jsonb() :

-- Convertir une chaîne JSON en JSONB
SELECT jsonb('{"nom":"Alice", "age":28}');

Cette fonction renvoie un BLOB contenant la représentation binaire JSONB de l’objet JSON fourni.

Convertir JSONB vers du texte JSON

Pour convertir du JSONB en texte JSON standard, vous pouvez utiliser la fonction json() :

-- Convertir du JSONB en texte JSON
SELECT json(jsonb('{"nom":"Alice", "age":28}'));

Stockage de données JSONB

Création d’une table pour stocker du JSONB

Contrairement au JSON texte qui est stocké dans des colonnes TEXT, JSONB est stocké dans des colonnes BLOB :

-- Créer une table pour stocker des données JSONB
CREATE TABLE utilisateurs_jsonb (
    id INTEGER PRIMARY KEY,
    nom TEXT NOT NULL,
    profil BLOB NOT NULL -- Stockera des données JSONB
);

Insertion de données JSONB

-- Insérer des données JSONB
INSERT INTO utilisateurs_jsonb (nom, profil)
VALUES (
    'Alice',
    jsonb('{"age": 28, "email": "[email protected]", "interests": ["programmation", "musique", "voyages"]}')
);

Vérification du type de données

Vous pouvez utiliser la fonction json_valid() avec un paramètre spécial pour vérifier si une valeur est au format JSONB :

-- Vérifier si une colonne contient du JSONB valide
SELECT json_valid(profil, 4) FROM utilisateurs_jsonb;

Le second paramètre 4 indique à json_valid() de vérifier si la valeur est au format JSONB. Si c’est le cas, la fonction renvoie 1 (vrai).

Interrogation et manipulation de données JSONB

Extraction de données

Les fonctions d’extraction JSONB fonctionnent exactement comme leurs équivalents JSON :

-- Extraire des données de JSONB
SELECT jsonb_extract(profil, '$.age') AS age FROM utilisateurs_jsonb;

-- Les opérateurs -> et ->> fonctionnent aussi avec JSONB
SELECT profil -> '$.age' AS age FROM utilisateurs_jsonb;
SELECT profil ->> '$.email' AS email FROM utilisateurs_jsonb;

Modification de données JSONB

De même, les fonctions de modification JSONB sont utilisées de la même manière que leurs homologues JSON :

-- Mettre à jour une valeur dans JSONB
UPDATE utilisateurs_jsonb
SET profil = jsonb_set(profil, '$.age', '29')
WHERE nom = 'Alice';

-- Ajouter un nouvel élément
UPDATE utilisateurs_jsonb
SET profil = jsonb_set(profil, '$.telephone', '"555-123-4567"')
WHERE nom = 'Alice';

-- Supprimer un élément
UPDATE utilisateurs_jsonb
SET profil = jsonb_remove(profil, '$.email')
WHERE nom = 'Alice';

Comparaison de performances

L’avantage principal de JSONB est la performance. Voici un exemple qui compare les performances entre JSON et JSONB :

-- Créer des tables pour tester les performances
CREATE TABLE test_json (id INTEGER PRIMARY KEY, data TEXT);
CREATE TABLE test_jsonb (id INTEGER PRIMARY KEY, data BLOB);

-- Insérer un grand nombre d'enregistrements
WITH RECURSIVE nums(n) AS (
    SELECT 1
    UNION ALL
    SELECT n+1 FROM nums WHERE n < 10000
)
INSERT INTO test_json
SELECT n, json_object('id', n, 'value', 'test'||n, 'array', json_array(1, 2, 3, 4, 5)) FROM nums;

-- Même chose pour JSONB
WITH RECURSIVE nums(n) AS (
    SELECT 1
    UNION ALL
    SELECT n+1 FROM nums WHERE n < 10000
)
INSERT INTO test_jsonb
SELECT n, jsonb_object('id', n, 'value', 'test'||n, 'array', jsonb_array(1, 2, 3, 4, 5)) FROM nums;

-- Tester les performances (utiliser la commande .timer on dans sqlite3)
-- Test JSON textuel
SELECT COUNT(*) FROM test_json WHERE json_extract(data, '$.id') > 5000;

-- Test JSONB
SELECT COUNT(*) FROM test_jsonb WHERE jsonb_extract(data, '$.id') > 5000;

Dans la plupart des cas, la version JSONB sera significativement plus rapide, surtout pour les opérations qui impliquent l’accès à des éléments spécifiques dans de grands objets JSON.

Taille de stockage

JSONB est généralement plus compact que le JSON texte, ce qui peut être important pour les grandes bases de données :

-- Comparer la taille de stockage
SELECT
    'JSON' AS format,
    SUM(LENGTH(data)) AS taille_totale
FROM test_json
UNION ALL
SELECT
    'JSONB' AS format,
    SUM(LENGTH(data)) AS taille_totale
FROM test_jsonb;

Cas d’utilisation pratiques pour JSONB

1. Applications avec de nombreuses opérations JSON

Si votre application effectue fréquemment des opérations d’extraction ou de modification de données JSON, JSONB peut offrir un gain de performance significatif :

-- Exemple de table pour une application de commerce électronique
CREATE TABLE produits_jsonb (
    id INTEGER PRIMARY KEY,
    details BLOB -- JSONB
);

-- Insérer un produit avec des spécifications détaillées
INSERT INTO produits_jsonb (id, details) VALUES (
    1,
    jsonb('{
        "nom": "Smartphone XYZ",
        "prix": 499.99,
        "specifications": {
            "processeur": "Octa-core 2.4 GHz",
            "ram": "8 GB",
            "stockage": "128 GB",
            "ecran": "6.5 pouces",
            "camera": "48 MP"
        },
        "couleurs_disponibles": ["noir", "blanc", "bleu"],
        "options": {
            "memoire": ["64GB", "128GB", "256GB"],
            "garantie": ["1 an", "2 ans", "3 ans"]
        },
        "evaluations": [
            {"utilisateur": "user123", "note": 4.5, "commentaire": "Excellent produit!"},
            {"utilisateur": "user456", "note": 5.0, "commentaire": "Parfait!"}
        ]
    }')
);

2. Traitement par lots et analyses de données

Pour les opérations de traitement par lots qui analysent ou modifient de grandes quantités de données JSON, JSONB offre des avantages significatifs :

-- Calculer la note moyenne pour chaque produit
SELECT
    id,
    jsonb_extract(details, '$.nom') AS nom,
    (
        SELECT AVG(CAST(note.value AS REAL))
        FROM json_each(jsonb_extract(details, '$.evaluations')) AS eval,
             json_each(eval.value) AS props
        WHERE props.key = 'note'
    ) AS note_moyenne
FROM produits_jsonb;

3. APIs et services web

JSONB est particulièrement utile pour les applications qui servent de passerelle entre une base de données relationnelle et des API JSON :

-- Générer des réponses API optimisées
SELECT
    jsonb_object(
        'produit', jsonb_extract(details, '$'),
        'meta', jsonb_object(
            'derniere_mise_a_jour', datetime('now'),
            'version', '1.0'
        )
    ) AS api_response
FROM produits_jsonb
WHERE id = 1;

Limitations et considérations

Bien que JSONB offre des avantages significatifs, il présente quelques limitations à prendre en compte :

Compatibilité des versions : JSONB n’est disponible qu’à partir de SQLite 3.45.0.
Format propriétaire : Le format JSONB de SQLite est spécifique à SQLite et n’est pas compatible avec d’autres bases de données comme PostgreSQL.
Flexibilité d’interrogation : Bien que plus rapide, JSONB dans SQLite n’offre pas encore toutes les améliorations d’interrogation que l’on trouve dans d’autres systèmes (comme les index sur les propriétés JSON).
Fonctions avancées : Certaines fonctions avancées peuvent ne pas être disponibles en version JSONB.

Bonnes pratiques

Conversion intelligente : Convertissez en JSONB les données JSON fréquemment accédées ou les grandes structures.
Stockage mixte : Vous pouvez utiliser à la fois JSON et JSONB dans la même base de données selon les besoins.
Tests de performance : Effectuez des tests comparatifs pour vérifier les gains de performance dans votre cas d’utilisation spécifique.
Migration progressive : Si vous avez une base de données existante avec du JSON, migrez progressivement vers JSONB.

Exemple de migration de JSON vers JSONB

Voici comment migrer progressivement une table existante de JSON vers JSONB :

-- Ajouter une colonne JSONB à une table existante
ALTER TABLE utilisateurs ADD COLUMN profil_jsonb BLOB;

-- Mettre à jour la nouvelle colonne avec les données converties
UPDATE utilisateurs
SET profil_jsonb = jsonb(profil)
WHERE json_valid(profil);

-- Vérifier la migration
SELECT
    id,
    json_extract(profil, '$.nom') AS nom_json,
    jsonb_extract(profil_jsonb, '$.nom') AS nom_jsonb
FROM utilisateurs
LIMIT 5;

-- Une fois la migration validée, vous pourriez renommer les colonnes
-- ou créer une nouvelle table avec seulement les données JSONB

Conclusion

Le format JSONB dans SQLite représente une avancée significative pour les applications qui utilisent intensivement le JSON. En offrant des performances améliorées et une utilisation plus efficace de l’espace disque, JSONB est particulièrement utile pour les applications à grande échelle ou les applications mobiles où les ressources sont limitées.

Bien que relativement récent dans l’écosystème SQLite, JSONB devrait devenir la méthode privilégiée pour stocker et manipuler des données JSON dans les futures applications basées sur SQLite.