Bagaimana untuk Mendapatkan Objek Tertentu daripada Lajur JSON dalam MySQL Berdasarkan Pasangan Nilai Kunci?

MySQL JSON: Mencari Objek Berdasarkan Nilai Utama

Jadual MySQL sering menggunakan lajur JSON untuk menyimpan struktur data yang kompleks. Apabila bekerja dengan JSON, tugas biasa adalah untuk mendapatkan semula objek tertentu berdasarkan pasangan nilai kunci. Walau bagaimanapun, mencari objek secara langsung mungkin tidak selalunya mudah.

Contoh Skema JSON:

Pertimbangkan skema berikut:

<code class="sql">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;</code>

Dengan sampel data:

<code class="sql">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"}]'
);</code>

Masalah:

Andaikan kita ingin mendapatkan rentetan "Rumput" daripada tatasusunan JSON pilihan, dengan mengetahui bahawa "nilai" yang sepadan ialah " 1." Menggunakan JSON_EXTRACT() sahaja memerlukan indeks tatasusunan, yang mungkin tidak tersedia dengan mudah.

Penyelesaian 1: Menggunakan JSON_SEARCH() dan Manipulasi Rentetan Mengerikan

Pendekatan ini menggabungkan JSON_SEARCH() untuk mencari sebahagian indeks dan kemudian mengalih keluar bahagian yang tidak diingini daripada rentetan:

<code class="sql">SELECT
JSON_EXTRACT(`options`,CONCAT('$[',REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', ''), '].text'))
from `fields`;</code>

Penyelesaian 2: Menggunakan JSON_TABLE()

MySQL 8.0 memperkenalkan JSON_TABLE () untuk memudahkan tugas ini:

<code class="sql">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;</code>

Pendekatan Alternatif: Model Data Perhubungan

Untuk struktur data yang lebih mudah seperti contoh ini, pertimbangkan untuk menyimpan data dalam jadual hubungan dengan lajur untuk pasangan kunci dan nilai, membolehkan carian terus:

<code class="sql">SELECT * FROM field_options WHERE value = '1';</code>

Atas ialah kandungan terperinci Bagaimana untuk Mendapatkan Objek Tertentu daripada Lajur JSON dalam MySQL Berdasarkan Pasangan Nilai Kunci?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!