Cari objek yang sepadan dengan nilai utama dalam MySQL JSON: Bagaimana untuk melaksanakannya

Question

Saya cuba mencari cara untuk mencari objek JSON dan mendapatkan kunci tertentu, tetapi cari kunci lain.

Berikut ialah contoh seni bina:

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

Fiddle Pangkalan Data: https://www.db-fiddle.com/f/npPgVqh7fJL2JweGJ5LWXE/1

Jadi apa yang saya mahu ialah memilih rentetan "Rumput" dengan memberikan ID 1 从 options.

Tetapi nampaknya tiada cara untuk melakukan ini. Saya boleh mendapatkan rumput dengan melakukan ini:

select JSON_EXTRACT(`options`, '$[0].text') from `fields`;
// "Grass"

Tetapi ini memerlukan mengetahui indeks tatasusunan

Saya boleh mendapatkan sebahagian indeks tatasusunan seperti ini:

select JSON_SEARCH(`options`, 'one', '1') from `fields`;
// "$[0].value"

Dan dapatkan indeks itu sendiri dengan melakukan sesuatu yang sangat menakutkan seperti ini:

select
REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', '')
from `fields`;
// 0

Malah mencapai apa yang saya inginkan dengan melakukan perkara yang sangat menakutkan seperti ini:

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

Tapi mesti ada cara yang lebih baik kan?

Fiddle Pangkalan Data: https://www.db-fiddle.com/f/npPgVqh7fJL2JweGJ5LWXE/1

Answer 1

MySQL 8.0 menyediakan JSON_TABLE() untuk membantu mengendalikan kes sedemikian.

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;
+-------+-------+
| text  | value |
+-------+-------+
| Grass | 1     |
+-------+-------+

Tetapi anda perlu melaksanakan ungkapan JSON_TABLE() kompleks ini setiap kali anda ingin menulis pertanyaan seperti ini.

Adalah lebih mudah untuk tidak menggunakan JSON dan sebaliknya menyimpan data dalam jadual dengan lajur biasa (satu baris bagi setiap pasangan teks/nilai). Anda kemudian boleh mencari nilai yang dikehendaki dalam mana-mana lajur.

SELECT * FROM field_options WHERE value = '1';

99% daripada penggunaan JSON dalam MySQL yang saya lihat pada Stack Overflow boleh diselesaikan dengan mudah dengan tidak menggunakan JSON.