MySQL JSON: البحث عن الكائنات بناءً على القيم الأساسية

غالبًا ما تستخدم جداول MySQL أعمدة JSON لتخزين هياكل البيانات المعقدة. عند العمل مع JSON، تتمثل المهمة الشائعة في استرداد كائن محدد بناءً على زوج من القيمة الرئيسية. ومع ذلك، قد لا يكون العثور على الكائن مباشرةً أمرًا سهلاً دائمًا.

مثال لمخطط JSON:

ضع في اعتبارك المخطط التالي:

CREATE TABLE `fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

مع نماذج البيانات:

INSERT INTO `fields` (label, options) VALUES
(
  'My Field', 
  '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, {"text": "Synthetic  (Old Type - Hard)", "value": "3"}, {"text": "Gravel", "value": "5"}, {"text": "Clay", "value": "6"}, {"text": "Sand", "value": "7"}, {"text": "Grass/Synthetic Mix", "value": "8"}]'
);

المشكلة:

لنفترض أننا نريد استرداد السلسلة "Grass" من مصفوفة الخيارات JSON، مع العلم أن "القيمة" المقابلة لها هي "1." يتطلب استخدام JSON_EXTRACT() وحده فهرس المصفوفة، والذي قد لا يكون متاحًا بسهولة.

الحل 1: استخدام JSON_SEARCH() والتلاعب الرهيب بالسلسلة

هذا النهج يجمع JSON_SEARCH() للعثور على الفهرس جزئيًا ثم يزيل الأجزاء غير المرغوب فيها من السلسلة:

SELECT
JSON_EXTRACT(`options`,CONCAT('$[',REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', ''), '].text'))
from `fields`;

الحل 2: استخدام JSON_TABLE()يقدم MySQL 8.0 JSON_TABLE() لتبسيط هذه المهمة:

حدد field_options.* من الحقول المشتركة json_table(fields.options, الأعمدة '$[*]'( مسار النص النصي "$.text"، مسار نص القيمة "$.value" ) ) كخيارات_field_options حيث field_options.value = 1;

select field_options.* from fields cross join 
json_table(fields.options, 
'$[*]' columns(
  text text path '$.text',
  value text path '$.value'
 )
) as field_options 
where field_options.value = 1;

المنهج البديل: نموذج البيانات العلائقية

بالنسبة لهياكل بيانات أبسط مثل هذا المثال، فكر في تخزين البيانات في جدول علائقي يحتوي على أعمدة لـ أزواج المفاتيح والقيم، مما يسمح بالبحث المباشر:

]

SELECT * FROM field_options WHERE value = '1';