Apprendre les bases : Comment utiliser JSON dans SQLite

Dans ce guide, nous explorons une intersection fascinante entre deux technologies populaires : JSON et SQLite. Savoir utiliser JSON avec SQLite est important dans le développement de logiciels modernes, surtout lorsqu’il s’agit de structures de données complexes qui ne s’adaptent pas complètement à une structure tabulaire. Que vous soyez un développeur expérimenté ou un débutant avide d’élargir vos connaissances, ce tutoriel vous aidera à commencer à apprendre à utiliser JSON dans la base de données SQLite.

Allons-y!

Renforcer la gestion des données des applications modernes avec SQLite

SQLite, considéré comme le moteur de base de données le plus largement déployé et utilisé, est une base de données conviviale qui n’exige aucune configuration compliquée ou connexion serveur. SQLite est simple et adaptable à diverses applications, ce qui en fait un choix de prédilection dans le développement logiciel. SQLite a une empreinte binaire réduite, généralement inférieure à 1 Mo, ce qui signifie qu’il est plus léger que les autres bases de données. De plus, SQLite est entièrement conforme aux principes ACID.

Une autre caractéristique unique de SQLite est qu’il est bien adapté aux applications individuelles et aux appareils connectés à Internet, comme les gadgets de maison intelligente, qui font partie de l’Internet des Objets (IoT). De plus, malgré sa simplicité, SQLite dispose d’un contrôle solide sur le langage SQL standard. Il peut gérer des choses comme les transactions, les sous-requêtes et les déclencheurs. Ainsi, SQLite est simple à utiliser, mais reste tout de même assez puissant.

La capacité de SQLite s’étend au-delà du simple stockage de données. SQLite est efficace et convivial, avec des fonctionnalités telles que la recherche plein texte et le support des BLOB. SQLite offre également un mécanisme d’extension pour une fonctionnalité supplémentaire, ce qui en fait un outil adaptable dans l’écosystème logiciel moderne.

Fait amusant : Saviez-vous que, bien que beaucoup de gens prononcent SQLite comme ‘S-Q-Lite’ (sequel-light), son créateur, Richard Hipp, l’a en réalité destiné à être prononcé comme ‘S-Q-L-ite’ (ess-que-ell-ite) tout comme un minéral, en soulignant sa nature robuste mais légère?

Pourquoi SQLite excelle dans le domaine des bases de données relationnelles modernes

SQLite est une solution de choix pour les scénarios où les bases de données client-serveur à grande échelle pourraient être disproportionnées, car elle est légère et sans serveur. Comme SQLite est autonome, elle ne dépend d’aucune dépendance externe, ce qui la rend très fiable. Les bases de données SQLite sont portables sur différents systèmes de fichiers et architectures, facilitant ainsi la migration des données dans la base de données SQLite.

Les cas d’utilisation typiques de SQLite sont répandus dans divers domaines, car il s’agit du moteur de base de données le plus largement déployé. Par exemple, SQLite est un choix standard pour la persistance locale dans les applications, en particulier les applications mobiles. SQLite est également largement utilisé pour l’analyse des données et les tests, où sa clarté et sa puissance constituent une combinaison gagnante. Enfin, SQLite est un choix idéal pour le stockage des données des sites Web, où il peut gérer les données des utilisateurs, le contenu du site, et plus encore.

La performance de SQLite est impressionnante, avec une vitesse souvent supérieure à celle d’autres bases de données célèbres pour la plupart des opérations courantes.

Pourquoi les capacités de gestion JSON d’SQLite en font une excellente option pour la gestion moderne des données

Gérer efficacement les données non structurées est un défi auquel de nombreux développeurs sont confrontés. C’est là que le JSON entre en jeu. En tant que format de données flexible et sans schéma, le JSON est utile pour gérer les données qui ne s’intègrent pas proprement dans une structure tabulaire.

En stockant des données JSON dans SQLite, vous pouvez exploiter les puissantes capacités de requêtage de SQLite pour extraire et manipuler efficacement vos données JSON. La beauté de cette combinaison réside dans le fait que SQLite est livré avec des fonctions intégrées pour gérer les données JSON de manière simple. De plus, la popularité du JSON en tant que format d’échange de données et sa portabilité signifie que les données JSON stockées dans SQLite peuvent facilement être partagées, migrées ou exportées vers différents systèmes.

Le support JSON d’SQLite s’est amélioré au fil du temps. Il a été introduit pour la première fois en tant qu’extension dans la version 3.9.0, publiée en 2015, mais les versions ultérieures sont livrées avec un support intégré pour le JSON. SQLite vous permet de sauvegarder et de récupérer des données JSON en utilisant une colonne TEXT et un ensemble de fonctions JSON, telles que json(), json_extract(), json_object(), et json_array().

Comprendre les fonctions puissantes de SQLite pour la requête JSON

SQLite gère et manipule les données JSON en utilisant fonctions JSON. Voici les 10 principales fonctions JSON en SQLite, listées à titre de référence, et l’utilisation de chacune sera illustrée à l’aide d’une requête SQL simple dans la section suivante.

1. json(): Cette fonction vérifie si une chaîne est un JSON valide. Si c’est le cas, la fonction retourne le même JSON. Sinon, elle retourne NULL.
2. json_extract(): Cette fonction extrait un objet d’une chaîne JSON à l’aide d’un chemin.
3. json_array(): Cette fonction crée un tableau JSON.
4. json_array_length(): Cette fonction retourne la longueur du tableau JSON.
5. json_insert(): Cette fonction insère une valeur JSON dans une chaîne JSON.
6. json_object(): Cette fonction crée un objet JSON.
7. json_remove(): Cette fonction supprime une propriété de la chaîne JSON.
8. json_replace(): Cette fonction remplace une valeur dans une chaîne JSON.
9. json_type(): Cette fonction retourne le type de la valeur JSON (comme INTEGER, REAL, NULL, TRUE, FALSE, TEXT, et BLOB).
10. json_valid(): Cette fonction vérifie si une chaîne est un JSON valide.

Série pratique d’exemples pour comprendre les fonctions d’encodage et de décodage JSON en SQLite

Dans cette section, nous avons fourni des exemples minimaux et une brève explication pour chacune des fonctions JSON que nous avons listées dans la section précédente. Nous utilisons un exemple de données JSON provenant du générateur Dadroit JSON. Voici le JSON original pour vous donner le contexte.

La fonction json() en SQLite

Cette requête convertit le texte JSON en un objet JSON.

SELECT
    json ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}' ) AS json_object;

Le résultat de cette requête serait comme ceci:

La fonction json_extract() en SQLite

Cette requête extrait la valeur Name de l’objet JSON en l’utilisant comme chemin.

SELECT json_extract('{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Name') AS movie_name;

Le résultat de cette requête serait comme ceci:

La fonction json_array() en SQLite

Cette requête crée un nouvel array JSON à partir des entrées fournies.

SELECT
    json_array ( 1, 2, 3 ) AS array_result;

Le résultat serait comme ceci:

La fonction json_type() en SQLite

Cette requête récupère le type de données de la valeur Year de l’objet JSON.

SELECT
    json_type ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Year' ) AS property_type;

Le résultat serait comme ceci:

La fonction json_array_length() en SQLite

Cette requête compte le nombre d’éléments dans le tableau Cast dans l’objet JSON.

SELECT
    json_array_length ( '{"Genre":["Comedy","Crime"],"Cast":["Adrian Gratianna","Tani O''Hara","Tessie Delisle"]}', '$.Cast' ) AS array_length;

Le résultat serait comme ceci:

La fonction json_object() en SQLite

Cette requête crée un objet JSON avec les paires clé-valeur ID et Name.

SELECT
    json_object ( 'ID', 1, 'Name', 'Forgotten in the Planet' ) AS result;

Le résultat serait le suivant:

La fonction json_insert() dans SQLite

Cette requête insère la propriété clé-valeur Director dans l’objet JSON.

SELECT
    json_insert ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Director', 'Henrie Randell Githens' ) AS insert_movie;

Le résultat serait le suivant:

La fonction json_remove() dans SQLite

Cette requête supprime la paire clé-valeur Director de l’objet JSON.

SELECT
    json_remove ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}', '$.Director' ) AS result_of_remove;

Le résultat serait le suivant:

La fonction json_replace() dans SQLite

Cette requête remplace le Year dans l’objet JSON par la nouvelle valeur 1971.

SELECT
     json_replace ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}', '$.Year', 1971 ) AS result_of_replace;

Le résultat serait le suivant:

La fonction json_valid() dans SQLite

Cette requête vérifie si la chaîne fournie a la syntaxe et la structure correctes requises pour un JSON valide, et renvoie 1 si c’était le cas et 0 sinon.

SELECT
     json_valid ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}' ) AS result_of_valid;

Le résultat serait le suivant:

Exemples de requêtes pratiques pour une interrogation SQL améliorée à l’aide des fonctions JSON dans SQLite

Maintenant que vous avez appris les bases du JSON dans SQLite, voici présentés quelques exemples de flux de travail pratiques avec des données JSON dans la base de données SQLite, en utilisant les fonctions JSON mentionnées précédemment, et les données JSON mentionnées en entrée.

Stocker des données JSON dans SQLite avec des requêtes d’insertion

Premièrement, vous devez insérer le JSON dans une base de données SQLite. Créons une table nommée `movies` avec un champ nommé `data` en tant que champ texte, car vous pouvez stocker du JSON dans SQLite dans un champ texte. Vous utiliserez ce champ `data` pour stocker et récupérer les valeurs JSON :

CREATE TABLE movies ( data TEXT );

Ensuite, insérons notre JSON dans le champ `data` de la table `movies` :

INSERT INTO movies ( data )
 VALUES
     ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Genre":["Comedy","Crime"],"Director":"Henrie Randell Githens","Cast":["Adrian Gratianna","Tani OHara","Tessie Delisle"],"Runtime":90,"Rate":7.0}' );

Pour modifier (remplacer, insérer, supprimer, etc.) du JSON dans SQLite, vous pouvez utiliser les fonctions `json_replace()`, `json_insert()` et `json_remove()`.

La requête suivante remplace le nom du film par la nouvelle valeur où l’`ID` est `1` :

UPDATE movies
 SET data = json_replace ( data, '$.Name', 'Found in the Universe' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

La requête suivante insère une nouvelle propriété en tant que nouveau champ dans les données JSON stockées précédemment dans la ligne :

UPDATE movies
 SET data = json_insert ( data, '$.Country', 'USA' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

La requête suivante supprime la propriété Runtime des données JSON stockées précédemment dans la ligne :

UPDATE movies
 SET data = json_remove ( data, '$.Runtime' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Extraire des données JSON de SQLite

Pour récupérer des données JSON à partir de SQLite, vous pouvez utiliser la fonction `json_extract()` ou l’opérateur abrégé `->` :

Sélectionnez le nom du film :

SELECT
     json_extract ( data, '$.Name' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Ou en utilisant l’opérateur abrégé `->` :

SELECT
     data -> '$.Name'
 FROM
     movies
 WHERE
     data -> '$.ID' = 1;

Récupérez la liste des genres :

SELECT
     json_extract ( data, '$.Genre' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Récupérez le premier acteur de la liste `Cast` :

SELECT
     json_extract ( data, '$.Cast[0]' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Extrayez le `Year` et le `Rate` :

SELECT
     json_extract ( data, '$.Year' ) AS Year,
     json_extract ( data, '$.Rate' ) AS Rate
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Conclusions

Félicitations pour avoir terminé ce voyage ! Vous avez appris comment le type de données JSON et la base de données SQLite peuvent travailler ensemble. SQLite est un outil pratique à avoir dans votre boîte à outils. Il est simple mais puissant, et facile à utiliser. Même s’il est petit, il est plein de fonctionnalités utiles.

En un mot, SQLite nous permet de sauvegarder et de récupérer des données JSON en utilisant une colonne de texte et quelques fonctions JSON. Ces fonctions nous permettent d’explorer, d’analyser et de modifier les données JSON dans notre base de données SQLite. SQLite offre de nombreux outils pour gérer les données JSON, depuis l’ajout et la modification des données JSON jusqu’à la récupération de celles-ci pour diverses finalités. Nous avons abordé dix fonctions JSON principales dans SQLite qui facilitent la manipulation des données JSON. Ensuite, nous avons examiné quelques exemples de requêtes SQL utilisant ces fonctions JSON dans SQLite.

N’oubliez pas : devenir bon dans l’utilisation de JSON avec SQLite est une compétence qui doit être pratiquée plus attentivement. Alors, n’hésitez pas – plongez-vous, expérimentez et apprenez. En fin de compte, si vous avez trouvé ce guide utile, n’hésitez pas à le partager. Profitez de votre voyage de codage !