leer - ¿Cómo genero objetos json anidados utilizando las funciones json nativas de mysql?

php mysql json column

Question

Usando solo las funciones JSON nativas (sin PHP, etc.) en MySQL versión 5.7.12 (sección 13.16 del manual) Estoy tratando de escribir una consulta para generar un documento JSON a partir de tablas relacionales que contiene un subobjeto. Dado el siguiente ejemplo:

CREATE TABLE `parent_table` (
   `id` int(11) NOT NULL,
   `desc` varchar(20) NOT NULL,
   PRIMARY KEY (`id`)
);
CREATE TABLE `child_table` (
   `id` int(11) NOT NULL,
   `parent_id` int(11) NOT NULL,
   `desc` varchar(20) NOT NULL,
   PRIMARY KEY (`id`,`parent_id`)
);
insert `parent_table` values (1,''parent row 1'');
insert `child_table` values (1,1,''child row 1'');
insert `child_table` values (2,1,''child row 2'');

Estoy tratando de generar un documento JSON que se vea así:

[{
    "id" : 1,
    "desc" : "parent row 1",
    "child_objects" : [{
            "id" : 1,
            "parent_id" : 1,
            "desc" : "child row 1"
        }, {
            "id" : 2,
            "parent_id" : 1,
            "desc" : "child row 2"
        }
    ]
}]

Soy nuevo en MySQL y sospecho que hay un patrón SQL para generar objetos JSON anidados de una a muchas relaciones, pero tengo problemas para encontrarlo.

En Microsoft SQL (con el que estoy más familiarizado), funciona lo siguiente:

select 
 [p].[id]
,[p].[desc]
,(select * from [dbo].[child_table] where [parent_id] = [p].[id] for json auto) AS [child_objects]
from [dbo].[parent_table] [p]
for json path

Intenté escribir el equivalente en MySQL de la siguiente manera:

select json_object(
 ''id'',p.id 
,''desc'',p.`desc`
,''child_objects'',(select json_object(''id'',id,''parent_id'',parent_id,''desc'',`desc`) 
                  from child_table where parent_id = p.id)
)
from parent_table p;

select json_object(
  ''id'',p.id 
 ,''desc'',p.`desc`
 ,''child_objects'',json_array((select json_object(''id'',id,''parent_id'',parent_id,''desc'',`desc`) 
                              from child_table where parent_id = p.id))
 )
 from parent_table p

Ambos intentos fallan con el siguiente error:

Error Code: 1242. Subquery returns more than 1 row

Answer 1

La razón por la que obtiene estos errores es que el objeto json padre no espera un conjunto de resultados como una de sus entradas, necesita tener pares de objetos simples como {nombre, cadena}, etc. informe de errores, puede estar disponible en futuras funciones . Esto solo significa que necesita convertir los resultados de varias filas en una concatenación de resultados separados por comas y luego convertidos en una matriz json.

Casi lo tienes con tu segundo ejemplo.

Puede lograr lo que busca con la función GROUP_CONCAT

select json_object(
  ''id'',p.id 
 ,''desc'',p.`desc`
 ,''child_objects'',json_array(
                     (select GROUP_CONCAT(
                                 json_object(''id'',id,''parent_id'',parent_id,''desc'',`desc`)
                             )   
                      from child_table 
                      where parent_id = p.id))
                   )
 from parent_table p;

Esto casi funciona, termina tratando la subconsulta como una cadena que deja los caracteres de escape allí.

''{/"id/": 1, 
/"desc/": /"parent row 1/", 
/"child_objects/": 
    [/"
    {///"id///": 1,
     ///"desc///": ///"child row 1///", 
    ///"parent_id///": 1
    },
    {///"id///": 2, 
    ///"desc///": ///"child row 2///", 
    ///"parent_id///": 1}/"
    ]
}''

Para que esto funcione en un formato apropiado, debe cambiar la forma en que crea la salida JSON de la siguiente manera:

select json_object(
  ''id'',p.id 
 ,''desc'',p.`desc`
 ,''child_objects'',(select CAST(CONCAT(''['',
                GROUP_CONCAT(
                  JSON_OBJECT(
                    ''id'',id,''parent_id'',parent_id,''desc'',`desc`)),
                '']'')
         AS JSON) from child_table where parent_id = p.id)

 ) from parent_table p;

Esto le dará el resultado exacto que necesita:

''{/"id/": 1, 
/"desc/": /"parent row 1/", 
/"child_objects/": 
    [{/"id/": 1, 
    /"desc/": /"child row 1/", 
    /"parent_id/": 1
    }, 
    {/"id/": 2, 
    /"desc/": /"child row 2/", 
    /"parent_id/": 1
    }]  
}''