Imparare le basi: come utilizzare JSON in SQLite

In questa guida, esploriamo un’interessante intersezione tra due tecnologie popolari: JSON e SQLite. Conoscere come utilizzare JSON con SQLite è importante nello sviluppo software moderno, specialmente quando si tratta di strutture dati complesse che potrebbero non adattarsi completamente a una struttura tabulare. Che tu sia un sviluppatore esperto o un principiante desideroso di ampliare le tue conoscenze, questo tutorial ti aiuterà a iniziare a imparare come utilizzare JSON nel database SQLite.

Iniziamo!

Potenziare la gestione dei dati delle applicazioni moderne con SQLite

SQLite, definito il motore di database più ampiamente distribuito e utilizzato, è un database user-friendly che non richiede alcuna configurazione complicata o connessione al server. SQLite è semplice e adattabile a diverse applicazioni, il che lo ha reso una scelta preferita nello sviluppo software. SQLite ha una piccola dimensione binaria, generalmente inferiore a 1 MB, il che significa che è più leggero rispetto ad altri database. Inoltre, SQLite è pienamente conforme ai principi ACID.

Un’altra caratteristica unica di SQLite è che è ben adattato per applicazioni individuali e dispositivi connessi a Internet come gadget per la casa intelligente, che fanno parte dell’Internet delle Cose (IoT). Inoltre, nonostante la sua semplicità, SQLite ha un forte controllo sulla lingua SQL standard. Può gestire cose come transazioni, sotto-query e trigger. Quindi, SQLite è semplice da usare, eppure rimane molto potente.

La capacità di SQLite si estende oltre solo il semplice archivio dati. SQLite è efficiente e user-friendly, con caratteristiche come la ricerca full-text e il supporto per blob. SQLite offre anche un meccanismo di estensione per funzionalità aggiuntive, rendendolo uno strumento flessibile nell’ecosistema software moderno.

Fatto divertente: Sapevi che, mentre molte persone pronunciano SQLite come ‘S-Q-Lite’ (sequel-light), il suo creatore, Richard Hipp, intendeva che fosse pronunciato come ‘S-Q-L-ite’ (ess-que-ell-ite) proprio come un minerale, sottolineando la sua natura robusta ma leggera?

Perché SQLite eccelle nel mondo dei moderni database relazionali

SQLite è una soluzione ideale per scenari in cui l’uso di database client-server su vasta scala potrebbe essere eccessivo, poiché è leggero e senza server. Poiché SQLite è autonomo, non dipende da alcuna dipendenza esterna, rendendolo molto affidabile. I database SQLite sono portabili tra diversi sistemi di file e architetture, quindi la migrazione dei dati nel database SQLite è agevole.

I casi d’uso tipici di SQLite si estendono attraverso una varietà di domini, essendo il motore di database più diffuso al mondo. Ad esempio, SQLite è una scelta standard per la persistenza locale negli applicativi, specialmente nei dispositivi mobili. SQLite è anche ampiamente utilizzato per l’analisi dei dati e i test, dove la sua chiarezza e potenza costituiscono una combinazione vincente. Infine, SQLite è un’opzione ideale per il salvataggio dei dati dei siti web, dove può gestire i dati degli utenti, il contenuto del sito e altro ancora.

Le prestazioni di SQLite sono impressionanti, con velocità spesso superiore ad altri famosi database per la maggior parte delle operazioni comuni.

Perché le capacità di gestione dei dati JSON di SQLite la rendono una scelta eccezionale per la gestione dei dati moderni

Gestire dati non strutturati in modo efficiente è una sfida che molti sviluppatori affrontano. È qui che entra in gioco JSON. Come formato di dati flessibile e senza schema, JSON è utile per gestire dati che non si adattano perfettamente a una struttura tabulare.

Archiviando dati JSON in SQLite, puoi sfruttare le potenti capacità di query di SQLite per estrarre e manipolare i tuoi dati JSON in modo efficiente. La bellezza di questa combinazione risiede nel fatto che SQLite offre funzioni integrate per gestire i dati JSON in modo semplice. Inoltre, la popolarità di JSON come formato di scambio dati e la sua portabilità significano che i dati JSON memorizzati in SQLite possono essere facilmente condivisi, migrati o esportati in diversi sistemi.

Il supporto JSON di SQLite è maturato nel tempo. È stato introdotto per la prima volta come estensione nella versione 3.9.0, rilasciata nel 2015, ma le versioni successive hanno incluso il supporto integrato per JSON. SQLite ti consente di salvare e recuperare dati JSON utilizzando una colonna TEXT e una suite di funzioni JSON, come json(), json_extract(), json_object(), e json_array().

Comprendere le potenti funzioni di SQLite per la query JSON

SQLite gestisce e manipola i dati JSON utilizzando funzioni JSON. Ecco le prime 10 funzioni JSON in SQLite, elencate a scopo di riferimento, e l’uso di ciascuna sarà esemplificato utilizzando una semplice query SQL nella sezione seguente.

1. json(): Questa funzione verifica se una stringa è un JSON valido. Se lo è, la funzione restituisce lo stesso JSON. Se non lo è, restituisce NULL.
2. json_extract(): Questa funzione estrae un oggetto da una stringa JSON utilizzando un percorso.
3. json_array(): Questa funzione crea un array JSON.
4. json_array_length(): Questa funzione restituisce la lunghezza dell’array JSON.
5. json_insert(): Questa funzione inserisce un valore JSON in una stringa JSON.
6. json_object(): Questa funzione crea un oggetto JSON.
7. json_remove(): Questa funzione rimuove una proprietà dalla stringa JSON.
8. json_replace(): Questa funzione sostituisce un valore in una stringa JSON.
9. json_type(): Questa funzione restituisce il tipo del valore JSON (come INTEGER, REAL, NULL, TRUE, FALSE, TEXT e BLOB).
10. json_valid(): Questa funzione verifica se una stringa è un JSON valido.

Serie pratica di esempi per comprendere le funzioni di codifica e decodifica JSON in SQLite

In questa sezione, abbiamo fornito esempi minimi e una breve spiegazione per ciascuna delle funzioni JSON elencate nella sezione precedente. Utilizziamo un esempio di dati JSON provenienti dal generatore Dadroit JSON. Ecco il JSON originale per fornire contesto.

La funzione json() in SQLite

Questa query converte il testo JSON in un oggetto JSON.

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

Il risultato di questa query sarebbe simile a questo:

La funzione json_extract() in SQLite

Questa query estrae il valore Name dall’oggetto JSON utilizzandolo come percorso.

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

Il risultato di questa query sarebbe simile a questo:

La funzione json_array() in SQLite

Questa query crea un nuovo array JSON dalle entrate fornite.

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

Il risultato sarebbe simile a questo:

La funzione json_type() in SQLite

Questa query recupera il tipo di dati del valore Year dall’oggetto JSON.

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

Il risultato sarebbe simile a questo:

La funzione json_array_length() in SQLite

Questa query conta il numero di elementi nell’array Cast nell’oggetto JSON.

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

Il risultato sarebbe simile a questo:

La funzione json_object() in SQLite

Questa query crea un oggetto JSON con le coppie chiave-valore ID e Name.

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

Il risultato sarebbe simile a questo:

La funzione json_insert() in SQLite

Questa query inserisce la proprietà chiave-valore Director nell’oggetto JSON.

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

Il risultato sarebbe simile a questo:

La funzione json_remove() in SQLite

Questa query rimuove la coppia chiave-valore Director dall’oggetto JSON.

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

Il risultato sarebbe simile a questo:

La funzione json_replace() in SQLite

Questa query sostituisce il Year nell’oggetto JSON con il nuovo valore 1971.

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

Il risultato sarebbe simile a questo:

La funzione json_valid() in SQLite

Questa query verifica se la stringa fornita ha la sintassi e la struttura corrette necessarie per un JSON valido, e restituisce 1 se lo era e 0 altrimenti.

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

Il risultato sarebbe simile a questo:

Esempi pratici di query per migliorare l’elaborazione di query SQL utilizzando le funzioni JSON in SQLite

Ora che hai imparato le basi del JSON in SQLite, qui ti vengono presentati alcuni esempi di flusso di lavoro pratico con dati JSON nel database SQLite, utilizzando le funzioni JSON precedentemente menzionate e i dati JSON precedentemente menzionati come input.

Archiviare dati JSON in SQLite con query di inserimento

Innanzitutto, è necessario inserire il JSON in un database SQLite. Creiamo una tabella chiamata movies con un campo denominato data come campo di testo, poiché è possibile memorizzare il JSON in SQLite in un campo di testo. Utilizzerai questo campo data per memorizzare e recuperare i valori JSON:

CREATE TABLE movies ( data TEXT );

Quindi inseriamo il nostro JSON nel campo data della tabella 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}' );

Per modificare (sostituire, inserire, rimuovere, ecc.) il JSON in SQLite, puoi utilizzare le funzioni json_replace(), json_insert() e json_remove().

La seguente query sostituisce il nome del film con il nuovo valore dove l’ID è 1:

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

La seguente query inserisce una nuova proprietà come nuovo campo nel JSON memorizzato precedentemente nella riga:

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

La seguente query rimuove la proprietà Runtime dal JSON memorizzato precedentemente nella riga:

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

Estrarre Dati JSON da SQLite

Per recuperare i dati JSON da SQLite, puoi utilizzare la funzione json_extract() o l’operatore di abbreviazione ->:

Seleziona il nome del film:

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

O utilizzando l’operatore di abbreviazione ->:

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

Recupera l’elenco dei generi:

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

Recupera il primo attore dall’elenco Cast:

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

Estrai il Year e il Rate:

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

Risultati conclusivi

Complimenti per aver completato questo percorso! Hai imparato come il tipo di dati JSON e il database SQLite possano lavorare insieme. SQLite è uno strumento utile da avere nel tuo set di strumenti. È semplice ma potente, ed è facile da usare. Anche se è piccolo, è pieno di caratteristiche utili.

In poche parole, SQLite ci consente di salvare e recuperare dati JSON utilizzando una colonna di testo e alcune funzioni JSON. Queste funzioni ci consentono di esplorare, analizzare e modificare i dati JSON nel nostro database SQLite. SQLite offre molti strumenti per gestire i dati JSON, dall’aggiunta e dalla modifica dei dati JSON al recupero di essi per vari scopi. Abbiamo trattato dieci funzioni JSON principali in SQLite che rendono più semplice la gestione dei dati JSON. Poi, abbiamo esaminato alcuni esempi di query SQL utilizzando queste funzioni JSON in SQLite.

Ricorda: diventare bravi nell’uso di JSON con SQLite è una capacità che richiede un allenamento più approfondito. Quindi, non essere timido – tuffati, sperimenta e impara. Alla fine, se hai trovato utile questa guida, sentiti libero di condividerla. Goditi il tuo percorso di programmazione!