MySQL JSON: búsqueda de objetos basados ​​en valores clave

Las tablas MySQL a menudo utilizan columnas JSON para almacenar estructuras de datos complejas. Cuando se trabaja con JSON, una tarea común es recuperar un objeto específico en función de un par clave-valor. Sin embargo, encontrar el objeto directamente puede no siempre ser sencillo.

Ejemplo de esquema JSON:

Considere el siguiente esquema:

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;

Con datos de muestra:

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

Problema:

Supongamos que queremos recuperar la cadena "Grass" de la matriz JSON de opciones, sabiendo que su "valor" correspondiente es "1". Usar JSON_EXTRACT() solo requiere el índice de la matriz, que puede no estar disponible.

Solución 1: usar JSON_SEARCH() y una horrible manipulación de cadenas

Este enfoque combina JSON_SEARCH() para encontrar parcialmente el índice y luego elimina las partes no deseadas de la cadena:

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

Solución 2: usar JSON_TABLE()

MySQL 8.0 presenta JSON_TABLE() para simplificar esta tarea:

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;

Enfoque alternativo: modelo de datos relacionales

Para estructuras de datos más simples como este ejemplo, considere almacenar los datos en una tabla relacional con columnas para pares de clave y valor, lo que permite la búsqueda directa:

SELECT * FROM field_options WHERE value = '1';