SQLite JSON: json_extract, json_set ו-json_each

ל-SQLite אין טיפוס JSON, וזה בסדר

ל-SQLite אין טיפוס עמודה ייעודי ל-JSON. JSON נכנס לעמודת TEXT רגילה, וסט של פונקציות מובנות, שנקראות יחד הרחבת JSON1, יודע לפענח, לשלוף ולשנות אותו. JSON1 מגיעה עם כל גרסה מודרנית של SQLite, כך שאין מה להתקין.

המודל המנטלי: שומרים את המסמך כטקסט, ומשתמשים בפונקציות כדי להסתכל לתוכו.

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

INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT id, data FROM users;

שתי שורות, בכל אחת מסמך JSON בעמודת טקסט רגילה. עכשיו צריך דרכים להגיע לתוך המסמכים האלה.

חילוץ שדות עם json_extract ו-->>

json_extract(column, path) מוציא ערך ממסמך JSON. הנתיב מתחיל ב-$ (השורש) ומשתמש ב-.field למפתחות של אובייקט וב-[i] לאינדקסים של מערך.

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT
    json_extract(data, '$.name')     AS name,
    json_extract(data, '$.tags[0]')  AS first_tag
FROM users;

לכתוב json_extract(data, '$.name') בכל מקום נמאס מהר, אז SQLite נותנת שני אופרטורים:

• -> מחזיר ערך מקודד כ-JSON (מחרוזות חוזרות עם המירכאות שלהן).
• ->> מחזיר ערך SQL (טקסט או מספר, בלי מירכאות).

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}');

SELECT
    data -> '$.name'      AS name_json,
    data ->> '$.name'     AS name_text,
    data ->> '$.tags[0]'  AS first_tag
FROM users;

name_json חוזר כ-"Ada" (עדיין JSON), ו-name_text כ-Ada. השתמשו ב-->> כשאתם רוצים ערך להשוואה או לתצוגה. השתמשו ב--> כשאתם מתכוונים להעביר את התוצאה לפונקציית JSON אחרת.

סינון לפי שדות JSON

ברגע שאפשר לחלץ, אפשר לסנן. הביטוי נכנס לפסוקית ה-WHERE כמו כל ביטוי אחר:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "active": 1}'),
    ('{"name": "Boris", "email": "boris@example.com", "active": 0}'),
    ('{"name": "Cleo", "email": "cleo@example.com", "active": 1}');

SELECT data ->> '$.name' AS name
FROM users
WHERE data ->> '$.active' = 1;

זה עובד, אבל בטבלה בגודל כלשהו זה איטי: כל שורה צריכה לעבור פענוח כדי לחשב את התנאי. נתקן את זה עם אינדקס עוד רגע.

בניית JSON: json_object ו-json_array

בכיוון ההפוך, אפשר לבנות JSON בתוך שאילתה:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@example.com'),
    ('Boris', 'boris@example.com');

SELECT json_object(
    'id', id,
    'name', name,
    'email', email
) AS user_json
FROM users;

json_object('k1', v1, 'k2', v2, ...) בונה אובייקט. json_array(v1, v2, ...) בונה מערך. הן שימושיות להרכבת תגובות API ישירות ב-SQL, ואפשר לקנן אותן בלי בעיה:

SELECT json_object(
    'user', json_object('name', 'Ada', 'id', 1),
    'roles', json_array('admin', 'beta')
) AS payload;

עדכון JSON: json_set, json_insert, json_replace

שלוש פונקציות קרובות משנות מסמך JSON ומחזירות את הגרסה החדשה:

• json_set(doc, path, value): מגדירה את הנתיב, יוצרת אותו אם הוא חסר ודורסת אותו אם הוא קיים.
• json_insert(doc, path, value): מכניסה רק אם הנתיב עוד לא קיים.
• json_replace(doc, path, value): מעדכנת רק אם הנתיב כבר קיים.

הפונקציות לא משנות במקום: הן מחזירות מסמך חדש, שבדרך כלל כותבים בחזרה עם UPDATE:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com"}');

UPDATE users
SET data = json_set(data, '$.email', 'ada@new.com', '$.verified', 1)
WHERE id = 1;

SELECT data FROM users;

שימו לב ש-json_set מקבלת כמה זוגות של נתיב וערך בקריאה אחת. כדי להסיר מפתח, השתמשו ב-json_remove(doc, path).

פריסת מערכים עם json_each

json_each היא פונקציה שמחזירה טבלה: היא מקבלת מערך JSON (או אובייקט) ומחזירה שורה אחת לכל איבר. כך "מצאו משתמשים עם התגית admin", שמסורבל ב-SQL רגיל, הופך ל-JOIN רגיל:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "tags": ["beta"]}'),
    ('{"name": "Cleo", "tags": ["admin"]}');

SELECT u.data ->> '$.name' AS name, t.value AS tag
FROM users u, json_each(u.data, '$.tags') t
WHERE t.value = 'admin';

כל שורה מ-users מחוברת לאיברי מערך ה-tags שלה. json_each חושפת עמודות שימושיות, ביניהן key, value, type ו-fullkey. האחות שלה, json_tree, עוברת רקורסיבית על כל המסמך, כולל כל צומת מקונן, וזה שימושי לחיפוש במסמכים שהמבנה שלהם לא ידוע.

אינדוקס שדות JSON

השאילתה WHERE data ->> '$.active' = 1 שלמעלה עובדת, אבל SQLite צריכה לפענח כל שורה כדי לחשב את התנאי. לשדות שאתם שולפים לעתים קרובות, בנו אינדקס על ביטוי:

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

CREATE INDEX idx_users_email
    ON users(json_extract(data, '$.email'));

INSERT INTO users (data) VALUES
    ('{"email": "ada@example.com"}'),
    ('{"email": "boris@example.com"}');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE json_extract(data, '$.email') = 'ada@example.com';

האינדקס חייב להשתמש בדיוק באותו ביטוי כמו השאילתה. שילוב של json_extract(data, '$.email') באינדקס עם data ->> '$.email' בשאילתה לא יתאים, והאינדקס יישאר ללא שימוש: בחרו צורה אחת והיצמדו אליה.

לשדות שאתם שולפים כל הזמן, עמודה מחושבת קריאה יותר:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT,
    email TEXT GENERATED ALWAYS AS (data ->> '$.email') VIRTUAL
);

CREATE INDEX idx_users_email ON users(email);

INSERT INTO users (data) VALUES ('{"email": "ada@example.com"}');

SELECT email FROM users WHERE email = 'ada@example.com';

email נראית למי שכותב שאילתות כמו עמודה רגילה, אבל נשארת מסונכרנת עם ה-JSON אוטומטית.

אימות JSON

json_valid(text) מחזירה 1 אם הטקסט מתפענח כ-JSON, ו-0 אחרת. צרפו אותה לאילוץ CHECK כדי לדחות נתונים פגומים בזמן הכתיבה:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    payload TEXT CHECK (json_valid(payload))
);

INSERT INTO events (payload) VALUES ('{"type": "click"}');
INSERT INTO events (payload) VALUES ('not json');

ההכנסה הראשונה מצליחה; השנייה נכשלת עם שגיאת אילוץ. בלי הבדיקה הזו, JSON פגום יושב בשקט בטבלה עד שקריאה כלשהי ל-json_extract מתפוצצת חודשים אחר כך.

JSON מול JSONB

מאז SQLite 3.45 יש ייצוג בינארי בשם JSONB: אותם נתונים, מפוענחים מראש לצורה בינארית קומפקטית, כך שהפונקציות לא מפענחות מחדש בכל קריאה. משפחת הפונקציות jsonb_* (jsonb_extract, jsonb_set, jsonb_object, ...) מחזירה JSONB במקום טקסט, ואפשר לשלוף מעמודות JSONB עם אותם אופרטורים.

CREATE TABLE docs (id INTEGER PRIMARY KEY, body BLOB);

INSERT INTO docs (body) VALUES
    (jsonb('{"name": "Ada", "score": 99}'));

SELECT body ->> '$.name' AS name FROM docs;

השתמשו ב-JSON רגיל (טקסט) כשאתם רוצים שהמסמכים יהיו קריאים לבני אדם ב-dump וקלים לבדיקה. פנו ל-JSONB כשהטבלה גדולה, נשלפת לעתים קרובות, ועלות הפענוח באמת מופיעה בפרופיילינג. אל תעברו כברירת מחדל: הקריאות של JSON רגיל שווה הרבה בזמן דיבוג.

מתי JSON הוא הבחירה הנכונה

עמודות JSON מבריקות כש:

• המבנה משתנה משורה לשורה (למשל payloads של אירועים, לוגים של ביקורת, webhooks של אינטגרציות).
• אתם שומרים במטמון תגובה של API חיצוני ורוצים לשמור אותה שלמה.
• שדה נשלף לעתים רחוקות וכמעט אף פעם לא מסננים לפיו.

הן לא מתאימות כש:

• אתם משתמשים ב-JSON כדי להתחמק מתכנון סכמה. אם לכל שורה יש אותם שדות, אלה עמודות.
• צריך לסנן או לבצע JOIN לפי ערך לעתים קרובות. עמודה אמיתית עם אינדקס תנצח חיפוש בנתיב JSON בכל פעם.
• הייתם רוצים מפתחות זרים. ל-JSON אין שלמות רלציונית.

נקודת האיזון היא לשלב בין השניים: עמודות סקלריות לשדות שמניעים שאילתות ואילוצים, ולצידן עמודת JSON לזנב הארוך של הנתונים המשתנים.

הצעד הבא: חיפוש טקסט מלא

JSON נותן גמישות בצד האחסון. העמוד הבא עוסק ב-FTS5, מנוע חיפוש הטקסט המלא של SQLite, שנותן חיפוש טקסט אמיתי עם דירוג והדגשה, הרבה מעבר למה ש-LIKE יכול לעשות.