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

Проблема:

Предположим, мы хотим получить строку «Трава» из массива параметров 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() для упрощения этой задачи:

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';