Tengo una tabla con un campo JSON que contiene una matriz de objetos JSON. Necesito seleccionar objetos por alguna condición.

Crear y llenar una tabla:

 CREATE TABLE test ( id INT AUTO_INCREMENT PRIMARY KEY, json_list JSON ); INSERT INTO test(json_list) VALUES ("{""list"": [{""type"": ""color"", ""value"": ""red""}, {""type"": ""shape"", ""value"": ""oval""}, {""type"": ""color"", ""value"": ""green""}]}"), ("{""list"": [{""type"": ""shape"", ""value"": ""rect""}, {""type"": ""color"", ""value"": ""olive""}]}"), ("{""list"": [{""type"": ""color"", ""value"": ""red""}]}") ;

Ahora necesito seleccionar todos los objetos con type = color de todas las filas.

Quiero ver esta salida:

 id extracted_value 1 {"type": "color", "value": "red"} 1 {"type": "color", "value": "green"} 2 {"type": "color", "value": "olive"} 3 {"type": "color", "value": "red"}

Sería bueno conseguir esto también:

 id color 1 red 1 green 2 olive 3 red

No puedo cambiar el DB o JSON.

estoy usando mysql 5.7

Mi solución actual

Mi solución es unirme a la tabla con algún conjunto de índices y luego extraer todos los elementos de la matriz JSON.

No me gusta, ya que si el posible recuento de objetos en una matriz es grande, se requiere tener todos los índices hasta el máximo. Hace que la consulta sea lenta, ya que no detendrá el cálculo del valor JSON cuando se alcance el final de la matriz.

 SELECT test.id, JSON_EXTRACT(test.json_list, CONCAT('$.list[', ind.ind, ']')), ind.ind FROM test CROSS JOIN (SELECT 0 AS ind UNION ALL SELECT 1 AS ind UNION ALL SELECT 2 AS ind) ind WHERE JSON_LENGTH(json_list, "$.list") > ind.ind AND JSON_EXTRACT(json_list, CONCAT('$.list[', ind.ind, '].type')) = "color";

Es fácil obtener solo valores cambiando la ruta JSON_EXTRACT . ¿Pero hay una mejor manera?

Ediciones

• Se agregó una verificación para la longitud de json_list.list. Esto filtró el 67 % de las filas de la tabla derivada en este caso.