MySQL JSON: मुख्य मूल्यों के आधार पर वस्तुओं की खोज करना

MySQL टेबल अक्सर जटिल डेटा संरचनाओं को संग्रहीत करने के लिए JSON कॉलम का उपयोग करते हैं। JSON के साथ काम करते समय, एक सामान्य कार्य कुंजी-मूल्य जोड़ी के आधार पर एक विशिष्ट ऑब्जेक्ट को पुनः प्राप्त करना है। हालाँकि, वस्तु को सीधे ढूँढना हमेशा आसान नहीं हो सकता है। `फ़ील्ड` ( `id` int(10) अहस्ताक्षरित शून्य नहीं AUTO_INCREMENT, `लेबल` वर्कर(64) COLLATE utf8mb4_unicode_ci शून्य नहीं, `विकल्प` json डिफ़ॉल्ट शून्य, प्राथमिक कुंजी (`आईडी`) ) इंजन=इनोडीबी डिफॉल्ट चारसेट=utf8mb4 COLLATE=utf8mb4_unicode_ci;

नमूना डेटा के साथ:

'फ़ील्ड' में डालें (लेबल, विकल्प) मान ( 'मेरा क्षेत्र', '[{"टेक्स्ट": "ग्रास", "वैल्यू": "1"}, {"टेक्स्ट": "सिंथेटिक (नया प्रकार - सॉफ्ट)", "वैल्यू": "2"}, {"टेक्स्ट": " सिंथेटिक (पुराना प्रकार - कठोर)", "मूल्य": "3"}, {"पाठ": "बजरी", "मूल्य": "5"}, {"पाठ": "मिट्टी", "मूल्य": " 6"}, {"पाठ": "रेत", "मूल्य": "7"}, {"पाठ": "घास/सिंथेटिक मिश्रण", "मूल्य": "8"}]' );

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;

मान लीजिए हम विकल्प JSON सरणी से स्ट्रिंग "ग्रास" को पुनः प्राप्त करना चाहते हैं, यह जानते हुए कि इसका संबंधित "मान" "1" है। अकेले JSON_EXTRACT() का उपयोग करने के लिए सरणी के सूचकांक की आवश्यकता होती है, जो आसानी से उपलब्ध नहीं हो सकता है।

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"}]'
);

यह दृष्टिकोण इंडेक्स को आंशिक रूप से ढूंढने के लिए JSON_SEARCH() को जोड़ता है और फिर स्ट्रिंग से अवांछित भागों को हटा देता है:

SELECT JSON_EXTRACT(`options`,CONCAT('$[',REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), ''$[', ''), '].value'', ' '), ']।मूलपाठ')) `फ़ील्ड्स` से;

समाधान 2: JSON_TABLE()

MySQL 8.0 का उपयोग करके इस कार्य को सरल बनाने के लिए JSON_TABLE() प्रस्तुत किया गया है:

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

वैकल्पिक दृष्टिकोण: संबंधपरक डेटा मॉडल

इस उदाहरण की तरह सरल डेटा संरचनाओं के लिए, कॉलम के साथ एक संबंधपरक तालिका में डेटा संग्रहीत करने पर विचार करें कुंजी और मान जोड़े, सीधे खोज की अनुमति देते हैं:

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;