Αποθήκευση και Αναζήτηση JSON στο SQLite: Παραδείγματα και Βέλτιστες Πρακτικές

JSON, ή JavaScript Object Notation, είναι ένας μορφότυπος για την αποθήκευση και ανταλλαγή δεδομένων. Είναι ένας ελαφρύς, βασισμένος σε κείμενο και αναγνώσιμος από τον άνθρωπο μορφότυπος που είναι εύκολος για τις μηχανές να αναλύσουν και να παράγουν. Στο SQLite, το JSON μπορεί να αποθηκευτεί και να αναζητηθεί με μερικούς διαφορετικούς τρόπους.

Χρήση της Επέκτασης JSON1

Ένας τρόπος για να αποθηκεύσετε JSON στο SQLite είναι να χρησιμοποιήσετε την επέκταση JSON1, η οποία παρέχει συναρτήσεις για κωδικοποίηση και αποκωδικοποίηση JSON. Για να χρησιμοποιήσετε την επέκταση JSON1, πρέπει πρώτα να την ενεργοποιήσετε εκτελώντας την ακόλουθη εντολή:

SELECT load_extension('/path/to/json1/extension');

Μόλις η επέκταση ενεργοποιηθεί, μπορείτε να δημιουργήσετε έναν πίνακα που έχει μια στήλη τύπου JSON χρησιμοποιώντας την ακόλουθη δήλωση SQL:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data JSON
);

Εισαγωγή Δεδομένων JSON

Για να εισαγάγετε μια τιμή JSON σε αυτόν τον πίνακα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση json_encode που παρέχεται από την επέκταση JSON1, όπως αυτό:

INSERT INTO users (id, data) VALUES (1, json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
}));

Αναζήτηση Δεδομένων JSON

Για να αναζητήσετε αυτόν τον πίνακα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση json_extract, η οποία σας επιτρέπει να εξαγάγετε τιμές από τη στήλη JSON καθορίζοντας μια διαδρομή JSON. Για παράδειγμα, για να πάρετε τη διεύθυνση email του χρήστη με id 1, μπορείτε να χρησιμοποιήσετε το ακόλουθο ερώτημα:

SELECT json_extract(data, '$.email') AS email
FROM users
WHERE id = 1;

Αποθήκευση JSON σε BLOB

Ένας άλλος τρόπος για να αποθηκεύσετε και να αναζητήσετε JSON στο SQLite είναι να χρησιμοποιήσετε τον τύπο BLOB. Σε αυτή την περίπτωση, μπορείτε να δημιουργήσετε έναν πίνακα με μια στήλη BLOB όπως αυτό:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data BLOB
);

Εισαγωγή JSON σε μια Στήλη BLOB

Για να εισαγάγετε μια τιμή JSON σε αυτόν τον πίνακα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση json_encode για να κωδικοποιήσετε την τιμή ως συμβολοσειρά, και στη συνέχεια να χρησιμοποιήσετε τη συνάρτηση hex για να την μετατρέψετε σε BLOB, όπως αυτό:

INSERT INTO users (id, data) VALUES (1, hex(json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
})));

Αναζήτηση JSON σε μια Στήλη BLOB

Για να αναζητήσετε αυτόν τον πίνακα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση JSON_EXTRACT που παρέχεται από την επέκταση JSON1, αλλά πρέπει να μετατρέψετε την τιμή BLOB πίσω σε συμβολοσειρά χρησιμοποιώντας τη συνάρτηση unhex, όπως αυτό:

SELECT json_extract(unhex(data), '$.email') AS email
FROM users
WHERE id = 1;

Περίληψη

Συμπερασματικά, το SQLite παρέχει δύο τρόπους για να αποθηκεύσετε και να αναζητήσετε τιμές JSON: χρησιμοποιώντας την επέκταση JSON1 και χρησιμοποιώντας τον τύπο BLOB. Και οι δύο προσεγγίσεις έχουν τα δικά τους πλεονεκτήματα και μειονεκτήματα, και ποιον θα επιλέξετε θα εξαρτηθεί από τις συγκεκριμένες ανάγκες σας.

Αν χρειάζεστε να μετατρέψετε δεδομένα JSON σε δηλώσεις SQL INSERT, δείτε το δωρεάν εργαλείο μας Μετατροπέας JSON σε SQL.