في المقالة السابقة، تعلم الأساسيات: كيفية استخدام JSON في SQLite، استكشفنا وظائف JSON الأساسية في SQLite وقدراتها. استعرضنا استخدام JSON كبيانات غير منظمة داخل قاعدة بيانات SQLite. من المهم أن أوضحنا بعض وظائف SQLite JSON الضرورية، وناقشنا دورها في تخزين واسترجاع البيانات، ثم عرضنا أمثلة عملية لاستعلامات SQL. هذا الفهم الأساسي لكيفية العمل مع بيانات JSON في SQLite يعد المرحلة الأولى لاستكشافك المتقدم للموضوع.
هيا بنا نبدأ!
دمج قدرات SQL و NoSQL من خلال فهم كامل لمعالجة JSON في SQLite
تعزيز معرفتك حول قدرات معالجة JSON في SQLite يجمع بين أفضل ما في SQL و NoSQL، مما يوفر حلًا فعالًا ومتكاملًا لإدارة التنسيقات المختلطة للبيانات. دعم بيانات JSON في SQLite يحول SQLite إلى جهاز قوي للبيانات غير المنظمة، مثل قواعد البيانات مثل MongoDB.
تجميع SQLite للتكنولوجيا المتقدمة ل JSON يجمع بين مرونة JSON وقوة كتالوج SQLite، مثالي لتطبيقات اليوم الثقيلة البيانات. قدرات SQLite على التعامل مع JSON تفعل أكثر من مجرد تخزين واسترداد البيانات. تسمح لهم بإجراء عمليات كما في SQL على بيانات JSON، مما يخلق جسرًا بين إدارة البيانات المهيأة وغير المهيأة.
يركز هذا الدليل على تعبئة مجموعة إرادتك العملية مع وظائف JSON في SQLite من خلال أمثلة SQL التعليمية. يهدف كل قسم إلى تعزيز فهمك وتعطيك بداية في التعامل الحقيقي مع بيانات JSON في SQLite.
في النهاية، ستكون مجهزًا جيدًا بمجموعة الأدوات المتاحة لمعالجة بيانات JSON في SQLite للتعامل مع أي تراكيب بيانات JSON. ستتعلم كيفية التطبيق على المؤشرات والاستعلام باستخدام تعبيرات المسار، والتصفية، وحتى تحقق من صحة البيانات – وظائف ضرورية للتعامل مع البيانات الديناميكية في البيئات المهيأة باستخدام وظائف JSON في SQLite.
1. كيفية دمج JSON داخل SQLite
دور الدوال المضمنة لـ SQLite الوظائف JSON حيث تلعب دوراً حاسماً في دمج JSON و SQLite. اعتبارًا من إصدار SQLite 3.38.0، الذي تم إصداره في 22-02-2022، تضمنت الوظائف JSON افتراضيًا، بينما كانت تمثّل توابعاً موجودة على أصعبها فيما قبل. وهذا يعني أنه قبل هذا الإصدار كانت هذه الوظائف JSON في SQLite تتطلب التفعيل، بينما الآن هي متاحة افتراضيًا ويمكن التراجع عنها عن طريق تحديد خيار صنع المجموعة الزمنية، في حالة الحاجة لتعطيلها.
يمكنك استيراد بيانات JSON إلى SQLite باستخدام عمليات إدراج SQL بسيطة. وبديلًا، يمكنك أيضًا استخدام أدوات ثالثة أو تقنيات البرمجة للتعامل مع استيراد مجموعات بيانات JSON كبيرة أيضًا. لاستخراج بيانات JSON، يمكنك تحقيق وظيفة json_extract() التي تسترد القيم المرتبطة بمفتاح معين من عمود بيانات JSON.
2. تحسين SQLite JSON Functions للتحليل المتقدم لـ JSON والاستعلامات SQL
في هذا القسم، سنستكشف وظائف JSON المتقدمة وقدراتها في SQLite، باستخدام أمثلة على عبارات SQL لكل منها. طوال هذا المقال، سنستخدم بيانات JSON النموذجية المولدة، تحت الاسم فيلم، كمرجع ليتم استخدامه كبيانات مدروسة:
يمكنك إدراج البيانات في جدول يسمى فيلم بحقل واحد يسمى بيانات، وبدء تشغيل هذه الاستعلامات النموذجية من الآن فصاعدًا ضده. في الاستعلامات التالية، سنستخدم نصوص المدخلات لوظائف JSON، لنكون مباشرين بشأن شرح الوظائف، ثم سنعود إلى البيانات المدرجة في قاعدة البيانات بدءًا من القسم 3.
للحصول على بساطة في هذا المثال، سنستخدم نسخة أبسط من البيانات الأولى لـ JSON:
{
"Name": "Naked of Truth",
"Year": 1979,
"Director": "Ellynn O'Brien",
"Producer": "Kayley Byron Tutt",
"Runtime": 183,
"Rate": 8.0,
"Description": "Donec pretium nec dolor in auctor."
}الكشف عن الأخطاء باستخدام وظيفة json_error_position() في SQLite
يمكن استخدام وظيفة json_error_position() للكشف عن أي خطأ في بناء جملة البيانات JSON الخاصة بك. إذا كان السلسلة المدخلة هي JSON صالحة سترجع 0، وإلا سترجع موضع الحرف لأول خطأ.
على سبيل المثال، إذا كان لديك سلسلة JSON مشوهة كمدخل لهذه الدالة مثل هذا:
SELECT
json_error_position ( '{"Name":"Naked of Truth","Year":1979,' ) AS err_positionستكون نتيجة تشغيل هذا الاستعلام هي موضع خطأ بناء الجملة الذي وقع، والذي في هذه الحالة هو موضع الانعدام “}” في النهاية:
| error_position |
|---|
| 38 |
دمج كائنات JSON باستخدام وظيفة json_patch() في SQLite
تقوم الدالة json_patch() بدمج كائني JSON، مما يسمح بإضافة وتعديل وحذف كائنات JSON.
على سبيل المثال، ستجمع هذه الاستعلام المدخلات الـ JSON الإثنين في كائن JSON واحد:
SELECT
json_patch ( '{"Name":"Naked of Truth"}', '{"Year": 2011}' ) AS patched_json;وسيكون الناتج هكذا، كائن JSON يتكون من كلا الحقلين:
| patched_json |
|---|
| {“Name”:”Naked of Truth”,”Year”:2011} |
التلاعب بحقول JSON باستخدام الدالة json_set() في SQLite
تُستخدم الدالة json_set() لإضافة أو استبدال خصائص JSON. json_set() تأخذ سلسلة JSON كحجة اولى تليها صف واحد أو أكثر من أزواج المسار/القيمة. سيكون الناتج سلسلة JSON تم إنشاؤها من خلال إضافة أو استبدال القيم بناءً على المسار وأزواج القيمة المقدمة.
على سبيل المثال، بناءً على بيانات الاستعلام السابقة، إذا أردت إلحاق حقل Director ببيانات الـ JSON، يمكنك كتابة استعلام كهذا:
SELECT
json_set ( '{"Name":"Naked of Truth","Year":2011}', '$.Director', 'Ellynn OBrien' ) AS json_data;وسيكون الناتج هكذا:
| json_data |
|---|
| {“Name”:”Naked of Truth”,”Year”:2011,”Director”:”Ellynn OBrien”} |
الدالة json_quote() في SQLite
الدالة json_quote() بسيطة، إنها تحيط قيمة المدخلات بعلامتي اقتباس مزدوجتين لجعلها سلسلة JSON صالحة. ها هو مثال بسيط لاستخدامها:
SELECT
json_quote ( 'Naked Of Truth' ) AS valid_json_string;والنتيجة من ذلك ستكون شيئًا مثل هذا:
| valid_json_string |
|---|
| “Naked of Truth” |
كيفية استخدام json_group_object() و json_group_array() الدوال الخاصة بـ JSON في SQLite للتجميع
بالنسبة لمجموعة هذه من دالات JSON في SQLite، نحتاج إلى توسيع بيانات JSON العينة مقارنة بالأمثلة السابقة، لإظهار استخدام كل دالة بطريقة مفهومة. لنفترض أن هذا هو movie الجدول في قاعدة البيانات مع حقل واحد يسمى data، كما ذكرت في بداية هذا القسم:
| data |
|---|
| {“ID”: 1, “Name”: “Forgotten in the Planet”, “Year”: 1970, “Genre”: [“Comedy”, “Crime”], “Director”: “Henrie Randell Githens”, “Cast”: [“Adrian Gratianna”, “Tani O’Hara”, “Tessie Delisle”], “Runtime”: 90, “Rate”: 7.0} |
| {“ID”: 2, “Name”: “The Obsessed’s Fairy”, “Year”: 1972, “Genre”: [“Adventure”], “Director”: “Susanne Uriel Lorimer”, “Cast”: [“Dacy Dex Elsa”, “Matilde Kenton Collins”], “Runtime”: 98, “Rate”: 9.5} |
| {“ID”: 3, “Name”: “Last in the Kiss”, “Year”: 1965, “Genre”: [“History”, “Animation”], “Director”: “Simone Mikey Bryn”, “Cast”: [“Margery Maximilianus Shirk”,”Harri Garwood Michelle”], “Runtime”: 106, “Rate”: 4.1} |
الدالة التراكمية json_group_array() مع مثال استعلام SQL
تعمل الدالة json_group_array() مثل أي دالة تراكمية أخرى في SQLite، تجمع عدة من الصفوف من البيانات في مجموعة JSON واحدة.
على سبيل المثال، سيعيد هذا الاستعلام مجموعة JSON بجميع أسماء الأفلام التي لها معدل أكبر من 6:
SELECT
json_group_array ( json_extract ( data, '$.Name' ) ) AS movie_names
FROM
movie
WHERE
json_extract ( data, '$.Rate' ) > 6والنتيجة من ذلك ستكون شيئًا مثل هذا:
| movie_names |
|---|
| [“Forgotten in the Planet”, “The Obsessed’s Fairy”] |
الدالة json_group_object() في JSON مع مثال استعلام SQL
تنشئ الدالة json_group_object() كائن JSON عن طريق تجميع عمودين من الاستعلام، حيث يُستخدم العمود الأول كمفتاح، والثاني كقيمة. سيُستخدم الأول كاسم مفتاح حقول JSON، والثاني كقيمتها.
على سبيل المثال، سيُرجع هذا الاستعلام كائن JSON حيث يكون اسم كل حقل هو معرف فيلم وقيمة الحقل هي الاسم المقابل إذا كان الفيلم له معدل أكبر من 6، مما يستبعد الفيلم الأخير:
SELECT
json_group_object ( json_extract ( Data, '$.ID' ), json_extract ( Data, '$.Name' ) ) AS movie_rates
FROM
movie
WHERE
json_extract ( Data, '$.Rate' ) > 5سيكون الناتج شيء مثل هذا، كائن JSON يتكون من معرف واسم الأفلام الأولى والثانية حيث أنها لديها معدل أكبر من 5:
| movie_rates |
|---|
| {“1”: “Forgotten in the Planet”,”2″:”The Obsessed’s Fairy”} |
فك تحليل بيانات JSON باستخدام json_each() وjson_tree() دالات قيمة الجدول في SQLite
توفر SQLite دالتين قويتين قيمة الجدول للعمل مع بيانات JSON، json_each() وjson_tree(). لديهما تباين مع وبدون معاملة المسار، مما يتيح لك التفاعل مع JSON على عمق مختلف.
افترض أن هذه هي القيمة الوحيدة في JSON التي تم إدراجها في حقل البيانات لجدول الفيلم في قاعدة البيانات SQLite، فلنبدأ بشرح وظائف التجميع عليها:
| data |
|---|
| { “ID”: 1, “Name”: “Forgotten in the Planet”, “Year”: 1970, “Genre”: [“Comedy”, “Crime”], “Director”: “Henrie Randell Githens”, “Cast”: [“Adrian Gratianna”, “Tani O’Hara”, “Tessie Delisle”], “Runtime”: 90, “Rate”: 7.0 } |
الدالة json_each() في SQLite مع مثال استعلام SQL
تقوم الدالة json_each() بتفكيك كائن JSON إلى صفوف، حيث يمثل كل صف حقلًا في كائن JSON، وتمر فقط عبر المستوى 1 من الحقول المتداخلة في JSON.
على سبيل المثال، سيعود هذا الاستعلام بثمانية صفوف لكل حقل في البيانات JSON:
SELECT
key,
value,
type
FROM
movie,
json_each ( data )النتيجة ستكون كشئ مثل هذا، معرضة اسم الحقل وقيمه كصف في كل حقل JSON، كما ترى، تم سرد حقلي المصفوفة Genre و Cast كما هي، ولم تتدخل الدالة فيهما لسرد العناصر الثانية في المستوى:
| key | Value | Type |
|---|---|---|
| ID | 1 | integer |
| Name | Forgotten in the Planet | text |
| Year | 1970 | integer |
| Genre | [“Comedy”,”Crime”] | array |
| Director | Henrie Randell Githens | text |
| Cast | [“Adrian Gratianna”,”Tani O’Hara”,”Tessie Delisle”] | array |
| Runtime | 90 | integer |
| Rate | 7.0 | real |
الدالة json_tree() في SQLite مع مثال استعلام SQL
تُستخدم الدالة json_tree() للتنقل وتحليل بيانات JSON بالكامل، مما يعني أنها ستدخل في كل حقل عبر جميع المستويات المتداخلة. تمر الدالة json_tree() عبر JSON، مداقعة كل جزء منه، ثم تعطيك جدولًا يوضح كل عنصر استطاعته العثور عليه.
json_tree() يعرض النتائج كمجموعة من الصفوف، مما يوفر نظرة واضحة على حتى أكثر البيانات JSON المتداخلة تعقيدًا. تخبرك هذه الجدول عن اسم كل عنصر، ونوع البيانات الذي يمثله، وقيمته، ومكانه داخل بنية JSON.
لذا ستُرجع هذه الاستعلام عدة صفوف، تصف بنية الكائن JSON، بما في ذلك الحقل المتداخل Cast:
SELECT
key,
value,
type
FROM
movie,
json_tree ( data )سيكون ناتج الاستعلام أعلاه مشابهًا لهذا:
| key | Value | Type |
|---|---|---|
| {“ID”:1,”Name”:”Forgotten in the Planet”,”Year”:1970,”Genre”:[“Comedy”,”Crime”],”Director”:”Henrie Randell Githens”,”Cast”:[“Adrian Gratianna”,”Tani O’Hara”,”Tessie Delisle”],”Runtime”:90,”Rate”:7.0} | object | |
| ID | 1 | integer |
| Name | Forgotten in the Planet | text |
| Year | 1970 | integer |
| Genre | [“Comedy”,”Crime”] | array |
| 0 | Comedy | text |
| 1 | Crime | text |
| Director | Henrie Randell Githens | text |
| Cast | [“Adrian Gratianna”,”Tani O’Hara”,”Tessie Delisle”] | array |
| 0 | Adrian Gratianna | text |
| 1 | Tani O’Hara | text |
| 2 | Tessie Delisle | text |
| Runtime | 90 | integer |
| Rate | 7 | real |
باستخدام معامل المسار، يمكن لـ json_tree() التركيز على جزء معين من JSON. إذا قمت بتزويد json_tree() بمسار معين في JSON كوسطيد الحجة، فسيبدأ بالاستكتشاف من تلك النقطة.
على سبيل المثال، هذا الاستعلام يتجاهل كل شيء خارج الحقل Cast، مما يوفر نظرة مركزة على هذا المجموعة المتداخلة من JSON:
SELECT
key,
value,
type
FROM
movie,
json_tree ( data, '$.Cast' )سيكون ناتج الاستعلام أعلاه مشابهًا لهذا:
| key | Value | Type |
|---|---|---|
| 0 | Adrian Gratianna | text |
| 1 | Tani O’Hara | text |
| 2 | Tessie Delisle | text |
حقيقة مضحكة: هل لاحظت الرقم ‘1’ في عنوان URL الوثائق الرسمية لـ JSON في SQLite وتساءلت عما إذا كان له قصة؟ عندما تم إصدار دعم JSON لأول مرة في SQLite في عام 2015، توقع المبتكر أن ‘JSON1’ سيكون مجرد بداية لسلسلة من الإصدارات—JSON2، JSON3، وهكذا. لكن الجزء المضحك هو: ‘JSON1’ كان فعالًا وفعالًا للغاية لدرجة أنهم لم يضطروا إلى إنشاء ‘JSON2’ أو ‘JSON3’. لذا، فإن الرقم ‘1’ في ‘JSON1’ ليس مجرد مؤشر للإصدار—إنه علامة للنجاح!
3. طرق عملية لاستعلام أي بيانات JSON معقدة في SQLite
استخدام وظائف JSON في SQLite بالاشتراك مع الوظائف المضمنة في SQLite يتيح لك إجراء استعلامات بيانات أكثر تعقيدًا. هنا يمكنك رؤية بعض هذه الأمثلة بما في ذلك التجميع والتصفية وتعبيرات المسار.
كما ذكرت في بداية المنشور، فإن البيانات التشعبية في جدول الفيلم في الأمثلة لجميع الأقسام المتبقية مثل هذا:
| data |
|---|
| {“ID”: 1, “Name”: “Forgotten in the Planet”, “Year”: 1970, “Genre”: [“Comedy”, “Crime”], “Director”: “Henrie Randell Githens”, “Cast”: [“Adrian Gratianna”, “Tani O’Hara”, “Tessie Delisle”], “Runtime”: 90, “Rate”: 7.0} |
| {“ID”: 2, “Name”: “The Obsessed’s Fairy”, “Year”: 1972, “Genre”: [“Comedy”, “Adventure”], “Director”: “Susanne Uriel Lorimer”, “Cast”: [“Dacy Dex Elsa”, “Matilde Kenton Collins”], “Runtime”: 98, “Rate”: 9.5} |
| {“ID”: 3, “Name”: “Last in the Kiss”, “Year”: 1965, “Genre”: [“History”, “Animation”], “Director”: “Simone Mikey Bryn”, “Cast”: [“Margery Maximilianus Shirk”,”Harri Garwood Michelle”], “Runtime”: 106, “Rate”: 4.1} |
تشكيل استعلامات SQL التجميعية باستخدام وظائف JSON في SQLite
تتضمن هذه الطريقة استخدام وظائف JSON بالإضافة إلى وظائف التجميع المضمنة في SQLite لإجراء حسابات على بيانات JSON. على سبيل المثال، يمكنك حساب متوسط ساعات تشغيل الفيلم المصنفة على أنها كوميديا باستخدام الاستعلام التالي:
SELECT
AVG( json_extract ( data, '$.Runtime' ) ) AS average_runtime
FROM
movie AS M,
json_each ( json_extract ( M.data, '$.Genre' ) ) AS T
WHERE
T.value = 'Comedy';ستكون نتيجة الاستعلام أعلاه هكذا لأن هناك فيلمين في قاعدة البيانات بالجانب كوميديا، وساعات تشغيلهما 90 و 98، لذا فإن متوسطهما سيكون كهذا:
| average_runtime |
|---|
| 94 |
فك وتصفية البيانات بشروط متعددة باستخدام JSON
يمكنك استخدام وظيفة json_extract() في SQLite للتصفية المتقدمة عن طريق استخدامها في فصل WHERE لاستعلام SQL. على سبيل المثال، يمكنك تصفية الأفلام على أساس شروط معينة، مثل الأفلام التي لها عضوان في الممثلين أو أكثر والتصنيف أعلى من قيمة معينة.
SELECT
json_extract ( data, '$.Name' ) AS movie_name,
json_extract ( data, '$.Rate' ) AS movie_rate,
json_array_length ( json_extract ( data, '$.Cast' ) ) AS cast_size
FROM
movie
WHERE
json_array_length ( json_extract ( data, '$.Cast' ) ) >= 2
AND json_extract ( data, '$.Rate' ) > 8;ستكون نتيجة الاستعلام أعلاه كهذا:
| movie_name | movie_rate | cast_size |
|---|---|---|
| The Obsessed’s Fairy | 9.5 | 2 |
استخدام تعبيرات المسار لاستخراج قيم محددة من بيانات JSON في SQLite
يمكن استخدام تعبيرات المسار للوصول إلى بيانات JSON المتداخلة عند تلك العنوان المحدد. يعود مثال على ذلك بقائمة جميع movie directors الذين كانوا مخرجين لفيلم في نوع معين، مثل التاريخ.
SELECT DISTINCT
json_extract ( data, '$.Director' ) AS movie_director
FROM
movie,
json_each ( json_extract ( data, '$.Genre' ) )
WHERE
value = 'History';سيكون نتيجة الاستعلام أعلاه مشابهة لما يلي:
| movie_director |
|---|
| Simone Mikey Bryn |
4. كيفية تحقق من مخطط بيانات JSON الخاصة بك في SQLite
تحقق من مخطط بيانات JSON في SQLite طريقة لضمان بنية وتناسق بياناتك، وتحسين التعامل مع الأخطاء في المستقبل، وتبسيط التعامل مع البيانات المعقدة. على الرغم من عدم وجود وظائف مدمجة لتوافق المخطط في SQLite، يمكنك استخدام وظائف JSON الخاصة به ووظيفة CHECK لهذا الغرض.
فحص بنية JSON باستخدام وظائف SQLite json_type() و check()
يمكن استخدام وظيفة json_type() للتحقق من نوع حقل في بيانات JSON. على سبيل المثال، بناءً على الإنشاء السابق لجدول الفيلم، لنفترض أنك تريد عند إنشاء الجدول لتخزين بيانات JSON للفيلم، التأكد من أن كل إدخال يحتوي على حقول الاسم والسنة، حيث تكون السنة العددية. يمكنك استخدام قيد CHECK() مع وظيفة json_type() في إنشاء الجدول:
CREATE TABLE movie ( data TEXT CHECK ( json_type ( data, '$.Name' ) IS NOT NULL AND json_type ( data, '$.Year' ) = 'integer' ) );هنا json_type() يتحقق من نوع الحقول المحددة في بيانات JSON الخاصة بك، الاسم، والسنة. إذا حاولت عملية إدراج جديدة أو تحديث إضافة البيانات حيث لا يوجد اسم أو السنة ليست عدد صحيح، ستفشل قيد CHECK()، وسيتم رفض العملية. هذا يساعد على الحفاظ على سلامة البيانات في بيانات JSON في جدول الفيلم.
التحقق من بيانات JSON باستخدام json_valid() في SQLite
تأكد الدالة json_valid() من صحة البيانات JSON من وجهة نظر تنسيق القياسي JSON، مما يوفر درجة من التحقق من المخطط. على سبيل المثال، لضمان سلامة البيانات JSON قبل الإدراج، يمكنك تطبيق الشيكات التالية:
INSERT INTO movie ( data ) SELECT
'{"Name":"Naked of Truth","Year":1979}' AS movie_input
WHERE
json_valid ( movie_input );في هذا البيان، json_valid() يتحقق مما إذا كانت السلسلة النصية المقدمة صالحة JSON. إذا كانت كذلك، يتم إدراج البيانات في جدول الفيلم، وإذا لم تكن كذلك، يتم تجاهل العملية. هذا الواقي يمنع إدراج بيانات JSON الفوضوية.
دعونا نفكر في مثال آخر يجمع بين القواعد الإثنين، القيد في مرحلة الخلق في جدول الفيلم، والشيك json_valid() في الإدراجات. مع النظر في الاستعلام التالي:
INSERT INTO movie ( data ) SELECT
'{"Year":"1979"}' AS movie_input
WHERE
json_valid ( movie_input );سيكون نتيجة هذا الاستعلام رسالة خطأ “تحقق من القيد فشل” لأن القيمة المدخلة ليست لديها حقل الاسم والحقل السنوي ليس عددًا صحيحًا ، لذا سيفشل الإدراج ، على الرغم من أن بيانات JSON المقدمة صالحة.
علاوة على ذلك ، من أجل التحقق الموثوق به على بيانات JSON معقدة ومتداخلة ، قد تفكر في مكتبة JSONschema بلغة Python كذلك.
5. كيفية التعامل مع بيانات JSON متداخلة في SQLite
تقدم التنقل في بيانات JSON متداخلة وهرمية في SQLite بعض التحديات. ومع ذلك ، تُبسِّط وظائف JSON المدمجة في SQLite هذه العملية وتجعلها قابلة للإدارة. هنا ، يمكنك رؤية بعض الاستراتيجيات لإدارة JSON المتداخلة في SQLite.
فك البيانات الهرمية JSON باستخدام استعلامات SQL
يمكن أن تساعدك وظائف SQLite json_each() و json_extract() في التنقل عبر طبقات بيانات JSON المتداخلة. فكر في هذا الاستعلام الذي يستخدم json_each() لتحليل البيانات و json_extract() لسحب المعلومات المطلوبة بشكل انتقائي.
على سبيل المثال ، سيقوم هذا الاستعلام بالاختراق في صف دُفعة الاسماء في كل سجل JSON في حقل data في جدول movie ، وسيسرِّد الأفلام movies التي لديها أكثر من 2 عضواً في Cast:
SELECT
key,
json_extract ( data, '$.Name' ) AS movie_name,
json_extract ( data, '$.Year' ) AS movie_year,
json_array_length ( json_extract ( data, '$.Cast' ) ) AS cast_size
FROM
movie,
json_each ( data )
WHERE
type = 'array'
AND cast_size > 2
GROUP BY
movie_name;نتيجة الاستعلام أعلاه ستكون شيئًا مثل هذا:
| key | movie_name | movie_year | cast_size |
|---|---|---|---|
| Simone Mikey Bryn | Forgotten in the Planet | 1970 | 3 |
التنقل في صفائف JSON عن طريق استعلام SQL
يمكن لكائنات JSON تخزين معلومات مهمة على شكل صفيف، وباستخدام json_tree() و json_extract() بشكل تراكبي، يمكنك تنقلًا في هذه الصفائف المتداخلة واستخراج البيانات منها.
على سبيل المثال، يجلب هذا الاستعلام اسم كل Actor من صفيف Cast لكل سجل فيلم:
SELECT
json_extract ( data, je.fullkey ) AS actor,
json_extract ( data, '$.Name' ) AS movie_name,
json_array_length ( data, '$.Cast' ) AS cast_size
FROM
movie,
json_tree ( data ) AS je
WHERE
( je.type = 'text' )
AND ( je.fullkey LIKE '%Cast%' );ستكون نتيجة هذا الاستعلام هكذا:
| actor | movie_name | cast_size |
|---|---|---|
| Adrian Gratianna | Forgotten in the Planet | 3 |
| Tani O’Hara | Forgotten in the Planet | 3 |
| Tessie Delisle | Forgotten in the Planet | 3 |
| Dacy Dex Elsa | The Obsessed’s Fairy | 2 |
| Matilde Kenton Collins | The Obsessed’s Fairy | 2 |
| Margery Maximilianus Shirk | Last in the Kiss | 2 |
| Harri Garwood Michelle | Last in the Kiss | 2 |
| Adrian Gratianna | Forgotten in the Planet | 3 |
| Tani O’Hara | Forgotten in the Planet | 3 |
| Tessie Delisle | Forgotten in the Planet | 3 |
تسطيح بيانات JSON باستخدام وظيفة json_each() في SQLite
في بعض الأحيان، يمكن أن يكون تبسيط الهياكل المتداخلة في JSON عن طريق تسطيحها أسلوبًا عمليًا لحل بعض الاستعلامات المعقدة ضد كائنات JSON. يمكن استخدام وظيفة SQLite json_tree() لتسطيح كائنات JSON.
على سبيل المثال، يستخدم هذا الاستعلام json_tree() لتحويل بيانات JSON إلى جدول من أزواج المفتاح-القيمة، تمامًا مسطحة، سيجلب الاستعلام كل نوع قيمة أساسي، من خلال الصفائف والكائنات أيضًا، من سجل الفيلم الأول:
SELECT
jt.fullkey,
jt.key,
jt.value
FROM
movie,
json_tree ( data ) AS jt
WHERE
( jt.key<> '' )
AND ( jt.type IN ( 'integer', 'text', 'real' ) )
AND json_extract ( data, '$.ID' ) = 1ستكون نتيجة هذا الاستعلام هكذا:
| fullkey | key | value |
|---|---|---|
| $.ID | ID | 1 |
| $.Name | Name | Forgotten in the Planet |
| $.Year | Year | 1970 |
| $.Genre[0] | 0 | Comedy |
| $.Genre[1] | 1 | Crime |
| $.Director | Director | Henrie Randell Githens |
| $.Cast[0] | 0 | Adrian Gratianna |
| $.Cast[1] | 1 | Tani O’Hara |
| $.Cast[2] | 2 | Tessie Delisle |
| $.Runtime | Runtime | 90 |
| $.Rate | Rate | 7 |
بتبني هذه الأساليب، يمكنك تفسير بيانات JSON بكفاءة وإدارتها وفك تشفيرها في SQLite، وهو أمر لا يقدر بثمن عند التعامل مع بيانات JSON معقدة.
6. كيفية استخدام الفهرسة لتحسين الاستعلام على بيانات JSON في SQLite
فهرستآوری دادههای JSON در SQLite روش موثری برای بهبود عملکرد عملیات جستجو و ارتقاء عملکرد پرس و جو است، به ویژه برای دادههای بزرگ. با ایجاد یک شاخص بر اساس خصوصیات JSON خاص، میتوانید عملیات جستجو را بر روی یک ستون JSON به میزان قابل توجهی تسریع کنید.
اصول اصلی این رویکرد ساده است. به جای انجام بررسی کامل جدول و تجزیه و تحلیل JSON برای هر ردیف، که میتواند مصرفکننده منابع باشد، SQLite میتواند از شاخص برای سریعترین شناسایی ردیفهای مورد علاقه استفاده کند.
چگونه شاخص SQL را در دادههای JSON در SQLite اضافه کنیم
بیایید با یک مثال عملی با فیلم دادهها به کار ببریم. به عنوان مثال، اگر شما بارها برای فیلمها بر اساس نام جستجو کنید، ایجاد یک شاخص بر روی این ویژگی مفید خواهد بود:
CREATE INDEX idx_name ON movie ( json_extract ( data, '$.item.Name' ) );در اینجا، داده ستونی است که دادههای JSON در آن قرار دارد و فیلم جدول است. تابع json_extract() از هر نام فیلم دادههای JSON را استخراج میکند و SQLite از این مقدار برای ایجاد یک شاخص استفاده میکند.
بمجرد تشغيل هذا الاستعلام وتعيين الفهرس، يمكن لـ SQLite استرداد البيانات بسرور عندما يتم البحث عن فيلم حسب Name. سيكون هذا الاستعلام أسرع بكثير مع وجود فهرس idx_name. ومن ثم، إضافة الفهرسة إلى بيانات JSON في SQLite تقدم قدرات تحسين قوية، مما يجعلها طريقة فعالة لإدارة مجموعات كبيرة من بيانات JSON.
كيفية إنشاء فهرس واحد على عدة مجالات من بيانات JSON في SQLite
لنأخذ مثالاً آخر حيث قد تبحث بشكل متكرر عن بيانات محددة على أساس أكثر من مجال واحد. على سبيل المثال، إذا كنت تبحث بشكل متكرر عن movies حسب الاسم والسنة، فسيكون من المفيد إنشاء فهرس على هذه الخصائص معًا. في SQLite، يمكن القيام بذلك عن طريق إنشاء فهرس على تعبير محسوب:
CREATE INDEX idx_name_year ON movie ( json_extract ( data, '$.Item.Name' ), json_extract ( data, '$.Item.Year' ) );مرة أخرى، عندما يتم تعيين هذا الفهرس، يمكن لـ SQLite استرداد البيانات بسرور عندما يتم البحث عن فيلم حسب الاسم والسنة.
7. دعم Json5 في SQLite
تم عرض JSON5 لدعم بعض بنية التعبيرات المتوافقة مع ECMA وجعل JSON أكثر ملاءمة ليتم استخدامه كلغة للتهيئة. قدم SQLite الدعم لتمديد JSON5 في الإصدار 3.42.0. على الرغم من أن SQLite يمكنه قراءة وتفسير نص JSON الذي يشمل تمديدات JSON5، إلا أن أي نص JSON تولده وظائف SQLite سيناسب بشكل صارم تعريف JSON القياسي. هنا بعض الميزات الرئيسية التي يضيفها تمديد JSON5 إلى الدعم الخاص بـ JSON في SQLite.
الكائنات JSON مع تعليقات في SQLite JSON
يسمح JSON5 بتعليقات واحدة (//…) ومتعددة الأسطر (/…/) وهذا يمكن أن يكون مفيدًا بشكل خاص لإضافة سياق أو توضيحات مباشرة في بيانات JSON الخاصة بك. فيما يلي مثال على التعليقات في كائنات JSON:
/* A
multi-line
comment
in JSON5 */
{
"key": "value" // A single-line comment in JSON5
}مفاتيح الكائن بدون علامات في SQLite JSON
في JSON5، يمكن أن تكون مفاتيح الكائن معبرة بدون تحديد الاقتطاع، مما يبسط بناء جملة JSON الخاصة بك. ومع ذلك، من المهم ملاحظة أن هذا قد يحد من التوافق مع الأنظمة التي تتبع بشكل صارم معايير JSON.
{ key: "value" }السلاسل المتعددة الأسطر في كائنات JSON
يدعم JSON5 السلاسل المتعددة الأسطر، والتي يمكن تحقيقها عن طريق تهجئة أحرف التعجيلة. هذا مفيد عند التعامل مع سلاسل كبيرة أو عند قوام السلسلة بشكل قابل للقراءة.
{ key: "This is a \\\\\\\\ multiline string" }Json5 مقابل التحقق من صحة JSON القياسي في SQLite
هنا، سنقوم بالتعرف على تقنيات التحقق الكاملة للكائنات JSON5 و JSON الكنسية، موضحًا دعمها من خلال أمثلة استعلامات SQL دقيقة في قاعدة البيانات SQLite.
لتحديد ما إذا كانت سلسلة صالحة لـ JSON5، يمكنك استخدام الدالة json_error_position(). ستُرجع هذه الدالة قيمة لا تساوي الصفر إذا كانت السلسلة غير مشكلة جيدًا لـ JSON أو JSON5. وهنا مثال:
SELECT
json_error_position ( '{ key: "value"}' ) AS error_position;ستكون نتيجة هذا الاستعلام 0، مما يشير إلى عدم اكتشاف أي خطأ هنا، على الرغم من أن المفتاح غير مطلق لأن هذا امتداد صالح لـ JSON5.
| error_position |
|---|
| 0 |
من ناحية أخرى، لتحويل سلسلة JSON5 إلى JSON الكنسي، يمكنك استخدام الدالة json(). بينما تتعرف هذه الدالة على وتعالج مدخلات JSON5، ستقوم بإخراج JSON الكنسي فقط. هذا يسمح بالتوافق إلى الوراء مع الأنظمة التي تتوقع JSON الكنسي. وهنا مثال:
SELECT
JSON ( '{key: "value"}' ) AS canonical_json;ستكون نتيجة هذا الاستعلام كائن JSON الكنسي، تم تحويله من تنسيق JSON5، مما جعل المفتاح مطلق هنا:
| canonical_json |
|---|
| {“key”: “value”} |
ومع ذلك، يجب أن تدرك أن الدالة json_valid() ستواصل الإبلاغ عن خطأ للمدخلات التي ليست JSON الكنسية، حتى لو كانت المدخلات صالحة لـ JSON5. هذا تمييز مهم عند العمل مع كل من JSON الكنسي وJSON5 في SQLite. على سبيل المثال، ضع في اعتبارك الاستعلام التالي:
SELECT
json_valid ( '{key: "value"}' ) AS valid_json;ستكون نتيجة هذا الاستعلام 0، مما يشير إلى أن هذا ليس JSON صالحًا لأن لديه مفتاح غير مطلق، وهو تعارض مع تنسيق JSON الكنسي:
| valid_json |
|---|
| {“key”: “value”} |
8. الأخطاء الشائعة واستكشاف الأخطاء وإصلاحها عند العمل مع JSON في SQLite
التعامل مع بيانات JSON في SQLite ينطوي على بعض الم pitfalls الشائعة التي يمكن تجنبها من خلال فهم أعمق للآليات الخاصة، مثل الاستخدام الصحيح للوظائف. إليك بعض الاعتبارات الرئيسية.
كيفية معالجة الأخطاء النحوية في بيانات JSON في مرحلة تحليل JSON في SQLite
يجب أن تكون بيانات JSON مصنفة بشكل صحيح وتتبع معيار نحوي محدد ليتم تحليلها ومعالجتها في قاعدة البيانات SQLite. إذا كانت سلسلة JSON الخاصة بك مصنفة بشكل غير صحيح، فلن يتمكن SQLite من تفسيرها، مما يؤدي إلى الأخطاء. على سبيل المثال، قد يكون لديك أقواس غير متطابقة، استخدام غير صحيح للاقواس، أو فواصل موضوعة بشكل خاطئ.
توفر SQLite الدالة json_valid() للتحقق من صحة سلسلة JSON، كما هو مبين بالاسم. ترجع الدالة json_valid() واحد إذا كان الإدخال سلسلة JSON مشكلة بشكل صحيح و 0 خلاف ذلك. إليك مثال:
SELECT json_valid('{"Name":"Naked of Truth","Year":1979}');في حالة وجود خطأ نحوي في سلسلة JSON، يمكن استخدام الدالة json_error_position() لتحديد موضع الخطأ في السلسلة حيث وقع الخطأ:
SELECT json_error_position('{"Name":"Naked of Truth","Year":1979}');الاستخدام الخاطئ لوظائف JSON عند الاستعلام ضد بيانات JSON
إن سوء استخدام وظائف JSON هو مشكلة شائعة أخرى، لذا من الجدير بالتأكيد على فهم جيد لوظائف JSON واستخدامها في SQLite لتعامل ناجح مع البيانات. على سبيل المثال، استخدام المسار الخاطئ أو عدم الأخذ بعين الاعتبار نظام الفهرسة الصفري للمصفوفات JSON في SQLite يمكن أن يؤدي إلى الأخطاء أو استرجاع بيانات غير صحيح.
لا يدعم SQLite وظائف JSON بواسطة BLOB
من المهم التأكد من عدم محاولة استخدام BLOBs مع وظائف JSON في SQLite لأن جميع وظائف JSON في SQLite حاليًا يلقون خطأ إذا كانت أي من أوساطهم BLOBs وليست مدخلات JSON صالحة في الوقت الحالي. لا يدعم SQLite أي ترميز ثنائي لـ JSON حاليًا ، بينما يمكن أن يكون هذا تحسينًا مستقبليًا.
كيفية القيام بتحقق JSON أثناء استعلام SQL البيانات المتعلقة بـ JSON في SQLite
تستخدم وظيفة json() في SQLite بشكل أساسي لفرض تنسيق JSON لسلسلة عن طريق إضافة اقتباسات وهرولة الأحرف اللازمة ، إلخ. استخدام json() بشكل غير صحيح يمكن أن يؤدي إلى نقص في التحقق من الأخطاء وتناقضات بيانات محتملة.
ومع ذلك ، ليست مصممة للتحقق من صحة JSON. للتحقق من سلسلة JSON أو العثور على خطأ تركيبي ، استخدم وظائف json_valid() و json_error_position() كما تمت مناقشتهم سابقًا.
الإجمالي
في هذا الدليل الشامل ، قضينا مرورًا عبر التكامل القوي لـ JSON و SQLite ، مما يوفر نظرة ثاقبة حول الفرص الواسعة التي يوفرها هذا المزيج. بدأنا بنظرة عامة على وظائف SQLite لـ JSON مع حالات استخدامها المفصلة مع أمثلة استعلام SQL.
لقد استكشفنا تقنيات الاستعلام المتقدمة مثل التعامل مع بيانات JSON الهرمية داخل SQLite. هذه الرحلة تعمقت في ميكانيكيات فك وإدارة البيانات JSON، مما أظهر فائدة وظائف SQLite مثل json_each() و json_tree(). كما تناولنا قيمة مسطحة البيانات JSON لإدارة البيانات بكفاءة.
ثم تحولنا إلى مجال كبير غالباً ما يُتجاهل: تعزيز الأداء عن طريق الفهرسة. هذا التحسين القوي يمكن أن يزيد بشكل كبير من أداء الاستعلام ويعزز تجربة SQLite الخاصة بك مع JSON. تم بعد ذلك مناقشة امتداد JSON5 الحديث، مما يجلب مرونة أكبر لتنسيق بيانات JSON الخاصة بك.
وأخيراً، تناولنا بعض الأخطاء الشائعة ونصائح للتصحيح لتلييف رحلتك عبر JSON في SQLite، معززين أهمية البنية الصحيحة لـ JSON واستخدام SQLite وظائف JSON بشكل صحيح.
تذكر، التعلم والتجربة هي مفتاح فتح الإمكانات الكاملة لـ JSON في SQLite. عند تطبيق هذه التقنيات على مشاريعك، يرجى مشاركة تجاربك لمساعدة الآخرين في رحلة مماثلة. لذا، دعونا نواصل التعلم ودفع الحدود مع JSON في SQLite. تحظى باستخدام JSON سعيد!
Source:
https://dzone.com/articles/how-to-master-advanced-json-querying-in-sqlite