دعم JSON في SQLite: json_extract و json_set و json_each

SQLite بلا نوع JSON خاص — ولا مشكلة في ذلك

لا يوفّر SQLite نوع عمود مخصصًا لتخزين JSON. ببساطة، يُخزَّن الـ JSON داخل عمود TEXT عادي، وتتكفّل مجموعة من الدوال المدمجة — يُطلق عليها مجتمعةً اسم امتداد 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;

هذا يعمل، لكن مع جدول كبير نسبياً ستلاحظ بطئاً واضحاً، لأن كل صف يحتاج إلى تحليل (parse) كامل لتقييم الشرط. سنعالج هذه المشكلة بعد قليل عبر إنشاء فهرس مناسب.

بناء 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، بينما 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

في sqlite توجد ثلاث دوال متقاربة تُعدّل مستند 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 هي دالة تُرجع جدولاً (table-valued function): تأخذ مصفوفة 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 في SQLite

الاستعلام WHERE data ->> '$.active' = 1 الذي رأيناه قبل قليل يعمل بلا مشاكل، لكن SQLite مضطر لتحليل كل صف حتى يُقيّم الشرط. ولو كنت تستعلم عن حقل معيّن بكثرة، فالأفضل أن تُنشئ فهرسًا تعبيريًا (expression index) عليه:

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' في الاستعلام، فلن يتطابقا، وسيبقى الفهرس مهملاً دون استخدام — فاختر صيغة واحدة والتزم بها.

أما الحقول التي تستعلم عنها باستمرار، فالأفضل استخدام عمود محسوب (generated column) لأنه أوضح وأسهل في القراءة:

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 ما بعد أشهر من الآن.

الفرق بين jsonb و json في SQLite

منذ الإصدار 3.45 من SQLite، ظهر تمثيل ثنائي اسمه 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 النصي العادي عندما تريد أن تكون المستندات مقروءة بشكل واضح في عمليات التفريغ (dumps) وسهلة الفحص. أما JSONB فاستعن به حين يكون الجدول ضخمًا، كثير الاستعلام، ويظهر فعلًا حِمل التحليل (parsing overhead) في أدوات قياس الأداء. لا تبدّل الافتراضي بلا داعٍ — وضوح JSON النصي يستحق الكثير أثناء تصحيح الأخطاء.

متى يكون تخزين JSON في SQLite هو الخيار الصحيح؟

أعمدة JSON تتألق في الحالات التالية:

• عندما يختلف شكل البيانات من سطر لآخر (مثل حمولات الأحداث، وسجلات التدقيق، وويب هوكس التكاملات).
• عندما تخزّن استجابة من واجهة API خارجية وتريد الاحتفاظ بها كما هي.
• عندما يكون الحقل نادر الاستعلام ولا يُستخدم في التصفية تقريبًا.

وتكون خيارًا سيئًا في الحالات التالية:

• عندما تستخدم JSON تهرّبًا من تصميم مخطط (schema) مناسب. إذا كانت جميع الصفوف تحمل الحقول نفسها، فهذه أعمدة وليست JSON.
• عندما تحتاج إلى التصفية أو الربط (join) على قيمة معيّنة بشكل متكرر. عمود حقيقي مع فهرس سيتفوّق دائمًا على البحث عبر مسار JSON.
• عندما تكون بحاجة إلى مفاتيح خارجية (foreign keys). JSON لا يوفّر تكاملًا علائقيًا.

النقطة المثالية هي المزج بين الاثنين: أعمدة قياسية للحقول التي تقود الاستعلامات والقيود، وعمود JSON بجوارها لاستيعاب الذيل الطويل من البيانات المتغيّرة.

التالي: البحث النصي الكامل

JSON يمنحك المرونة على صعيد التخزين. الصفحة التالية تتناول FTS5 — محرّك البحث النصي الكامل في SQLite — الذي يوفّر بحثًا نصيًا حقيقيًا مع ترتيب النتائج وإبراز الكلمات، وهو يتجاوز بأشواط ما يستطيع LIKE فعله.