Requêtes et extraction de données JSON dans SQLite

Introduction

Une fois que vous avez stocké des données JSON dans SQLite, vous aurez besoin d’extraire et d’interroger ces données. SQLite offre plusieurs fonctions puissantes pour accéder aux éléments spécifiques à l’intérieur de vos structures JSON.

Les chemins JSON

Dans SQLite, vous accédez aux éléments d’un objet JSON en utilisant des chemins JSON (JSON paths). Ces chemins ressemblent à des sélecteurs et commencent généralement par $ qui représente la racine du document JSON.

Voici la syntaxe des chemins JSON dans SQLite :

• $ : La racine du document JSON
• $.nom : Accède à la propriété “nom” de l’objet racine
• $.adresse.ville : Accède à la propriété “ville” à l’intérieur de l’objet “adresse”
• $.interets[0] : Accède au premier élément du tableau “interets”
• $.interets[*] : Fait référence à tous les éléments du tableau “interets”

Extraction de données avec json_extract()

La fonction principale pour extraire des données JSON est json_extract(). Cette fonction prend deux arguments ou plus :

1. La chaîne JSON à interroger
2. Un ou plusieurs chemins JSON à extraire

Extraire une seule valeur

-- Supposons que nous avons cette table avec des données JSON
CREATE TABLE utilisateurs (
    id INTEGER PRIMARY KEY,
    profil TEXT NOT NULL -- Contient des données JSON
);

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

-- Extrayons l'âge de l'utilisateur
SELECT json_extract(profil, '$.age') AS age FROM utilisateurs;
-- Résultat : 28

-- Extrayons la ville de l'adresse (valeur imbriquée)
SELECT json_extract(profil, '$.adresse.ville') AS ville FROM utilisateurs;
-- Résultat : "Paris"

Extraire plusieurs valeurs en une seule requête

-- Extrayons plusieurs champs en une seule requête
SELECT
    json_extract(profil, '$.nom') AS nom,
    json_extract(profil, '$.age') AS age,
    json_extract(profil, '$.adresse.ville') AS ville
FROM utilisateurs;

Les opérateurs -> et ->>

SQLite propose deux opérateurs pratiques pour simplifier l’extraction de données JSON :

• -> : Équivalent à json_extract()
• ->> : Équivalent à json_extract() mais retire les guillemets des valeurs textuelles

Exemple d’utilisation :

-- Avec l'opérateur ->
SELECT profil -> '$.nom' AS nom FROM utilisateurs;
-- Résultat : "Alice" (avec les guillemets)

-- Avec l'opérateur ->>
SELECT profil ->> '$.nom' AS nom FROM utilisateurs;
-- Résultat : Alice (sans les guillemets)

Ces opérateurs sont particulièrement utiles pour rendre vos requêtes plus lisibles.

Filtrer les données JSON

Vous pouvez utiliser les fonctions d’extraction JSON dans les clauses WHERE pour filtrer vos résultats :

-- Sélectionner les utilisateurs de plus de 25 ans
SELECT
    id,
    profil ->> '$.nom' AS nom
FROM utilisateurs
WHERE CAST(profil ->> '$.age' AS INTEGER) > 25;

-- Sélectionner les utilisateurs qui vivent à Paris
SELECT
    id,
    profil ->> '$.nom' AS nom
FROM utilisateurs
WHERE profil ->> '$.adresse.ville' = 'Paris';

NOTE

L’opérateur ->> est particulièrement utile dans les clauses WHERE car il retire les guillemets des valeurs textuelles, ce qui facilite les comparaisons.

Travailler avec des tableaux JSON

SQLite possède des fonctions spécifiques pour les tableaux JSON :

Compter les éléments d’un tableau

-- Insérer un utilisateur avec un tableau d'intérêts
INSERT INTO utilisateurs (profil)
VALUES ('{"nom":"Bob", "interets":["sport", "musique", "voyages", "cuisine"]}');

-- Compter le nombre d'intérêts
SELECT json_array_length(profil, '$.interets') AS nb_interets
FROM utilisateurs
WHERE profil ->> '$.nom' = 'Bob';
-- Résultat : 4

Rechercher dans un tableau

Pour rechercher dans un tableau JSON, vous devrez souvent utiliser les fonctions de table json_each() ou json_tree().

-- Trouver les utilisateurs qui s'intéressent à la musique
SELECT
    u.id,
    u.profil ->> '$.nom' AS nom
FROM utilisateurs u,
     json_each(u.profil, '$.interets') AS interet
WHERE interet.value = 'musique';

Dans cet exemple, json_each() agit comme une table virtuelle qui “décompose” le tableau d’intérêts, vous permettant de filtrer par valeur spécifique.

La fonction json_tree() pour les structures complexes

Pour les structures JSON profondément imbriquées, json_tree() est particulièrement utile :

-- Supposons un JSON avec une structure complexe
INSERT INTO utilisateurs (profil)
VALUES ('{
    "nom": "Charlie",
    "projets": [
        {
            "nom": "Projet A",
            "technologies": ["Python", "SQLite", "React"]
        },
        {
            "nom": "Projet B",
            "technologies": ["Node.js", "MongoDB"]
        }
    ]
}');

-- Trouver les utilisateurs qui ont utilisé SQLite dans leurs projets
SELECT DISTINCT
    u.id,
    u.profil ->> '$.nom' AS nom
FROM utilisateurs u,
     json_tree(u.profil, '$.projets[*].technologies') AS tech
WHERE tech.value = 'SQLite';

La fonction json_tree() parcourt récursivement la structure JSON, vous permettant d’accéder à n’importe quel élément à n’importe quelle profondeur.

Vérifier le type d’un élément JSON

La fonction json_type() vous permet de déterminer le type d’un élément JSON :

-- Vérifier les types de différents éléments
SELECT
    json_type(profil, '$') AS type_document,
    json_type(profil, '$.nom') AS type_nom,
    json_type(profil, '$.age') AS type_age,
    json_type(profil, '$.interets') AS type_interets
FROM utilisateurs;

-- Résultats possibles : object, array, text, integer, real, true, false, null

Exemple complet : Analyse de données JSON

Voici un exemple complet qui utilise différentes techniques d’extraction JSON :

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

INSERT INTO produits (details) VALUES
('{"nom":"Smartphone", "prix":499.99, "caracteristiques":["Écran 6.5\"", "128 Go", "Appareil photo 48MP"], "stock":{"entrepot_A":45, "entrepot_B":32}}'),
('{"nom":"Laptop", "prix":899.99, "caracteristiques":["15.6\"", "SSD 512 Go", "16 Go RAM"], "stock":{"entrepot_A":12, "entrepot_B":8}}'),
('{"nom":"Tablette", "prix":299.99, "caracteristiques":["10\"", "64 Go", "4G"], "stock":{"entrepot_A":0, "entrepot_B":5}}');

-- Requête qui extrait plusieurs informations
SELECT
    id,
    details ->> '$.nom' AS nom,
    CAST(details ->> '$.prix' AS REAL) AS prix,
    json_array_length(details, '$.caracteristiques') AS nb_caracteristiques,
    CAST(details ->> '$.stock.entrepot_A' AS INTEGER) +
    CAST(details ->> '$.stock.entrepot_B' AS INTEGER) AS stock_total,
    details -> '$.caracteristiques[0]' AS caracteristique_principale
FROM produits
WHERE CAST(details ->> '$.prix' AS REAL) < 500
  AND (CAST(details ->> '$.stock.entrepot_A' AS INTEGER) > 0
       OR CAST(details ->> '$.stock.entrepot_B' AS INTEGER) > 0);

Cette requête démontre comment :

• Extraire des propriétés simples et imbriquées
• Convertir des types avec CAST
• Compter des éléments dans un tableau
• Accéder à un élément spécifique d’un tableau
• Effectuer des calculs sur des valeurs extraites
• Filtrer avec plusieurs conditions sur des données JSON

Bonnes pratiques

1. Utilisez l’opérateur ->> dans les clauses WHERE pour éviter les problèmes de comparaison avec les guillemets.
2. Convertissez les types explicitement avec CAST lorsque vous travaillez avec des nombres.
3. Créez des index sur les expressions JSON fréquemment interrogées pour améliorer les performances.
4. Pour les requêtes complexes, envisagez d’extraire les données JSON dans des vues ou des tables temporaires.

Prochaines étapes

Maintenant que vous maîtrisez l’extraction de données JSON, vous pouvez apprendre à modifier ces données JSON directement dans SQLite.