[MariaDB] JSON Array dentro de un JSON Object

Uno de los problemas actuales de los Motores MariaDB y MySQL es que al hacer un Json Array dentro de un Json Object, la lista se obtiene con un formato Json String.

Por ejemplo la siguiente Query:

SELECT
JSON_ARRAYAGG(JSON_OBJECT(
	"nom_menu"    , men.NOM_MENU   ,
	"nom_orden"   , men.NUM_ORDEN  ,
	"nom_pagina"  , men.NOM_PAGINA ,
	"flg_visible" , men.FLG_VISIBLE,
	"flg_activo"  , men.FLG_ACTIVO ,
	"submenu",
   	(SELECT 
		JSON_ARRAYAGG(JSON_OBJECT(
		"nom_submenu" , sub.NOM_SUBMENU,
		"num_vector1" , sub.NUM_VECTOR1,
		"num_vector2" , sub.NUM_VECTOR2,
		"num_orden"   , sub.NUM_ORDEN  ,
		"nom_pagina"  , sub.NOM_PAGINA ,
		"flg_visible" , sub.FLG_VISIBLE,
		"flg_activo"  , sub.FLG_ACTIVO
		))        
		FROM trabajo.submenu sub
      WHERE sub.id_menu = men.id_menu AND sub.FLG_VISIBLE = 1)                  
)) AS JSON_SP
FROM trabajo.menu men
WHERE men.FLG_VISIBLE = 1
ORDER BY men.NUM_ORDEN ASC;

Da como resultado un JSON OBJECT que en su nodo hijo «Submenu» tine un JSON Array en formato String, el cual no se puede Parsear ni recorrer ya que la estructura esta dañada:

{
    "nom_menu": "SISTEMA",
    "nom_orden": 1,
    "nom_pagina": null,
    "flg_visible": 1,
    "flg_activo": 1,
    "submenu": "[{\"nom_submenu\": \"Menús\", \"num_vector1\": 0, \"num_vector2\": 0, \"num_orden\": 1, \"nom_pagina\": \"views/sistema/menus.php\", \"flg_visible\": 1, \"flg_activo\": 1},{\"nom_submenu\": \"SubMenús\", \"num_vector1\": 0, \"num_vector2\": 0, \"num_orden\": 2, \"nom_pagina\": \"views/sistema/submenus.php\", \"flg_visible\": 1, \"flg_activo\": 1}]"
}, {
    "nom_menu": "PRUEBA1",
    "nom_orden": 2,
    "nom_pagina": null,
    "flg_visible": 1,
    "flg_activo": 1,
    "submenu": "[{\"nom_submenu\": \"Prueba1_1\", \"num_vector1\": 0, \"num_vector2\": 0, \"num_orden\": 1, \"nom_pagina\": \"views/sistema/prueba1_!.php\", \"flg_visible\": 1, \"flg_activo\": 1}]"
}, {
    "nom_menu": "PRUEBA2",
    "nom_orden": 3,
    "nom_pagina": null,
    "flg_visible": 1,
    "flg_activo": 1,
    "submenu": null
}, {
    "nom_menu": "PRUEBA4",
    "nom_orden": 5,
    "nom_pagina": null,
    "flg_visible": 1,
    "flg_activo": 0,
    "submenu": null
}

La solución a este problema se obtiene aplicando la función JSON_EXTRACT la cual toma y reinserta el Json en la misma posición. Basándonos en la misma Query Anterior pero ahora aplicando la función queda algo así:

SELECT
JSON_ARRAYAGG(JSON_OBJECT(
	"nom_menu"    , men.NOM_MENU   ,
	"nom_orden"   , men.NUM_ORDEN  ,
	"nom_pagina"  , men.NOM_PAGINA ,
	"flg_visible" , men.FLG_VISIBLE,
	"flg_activo"  , men.FLG_ACTIVO ,
	"submenu",
		JSON_EXTRACT(
   	(SELECT 
		JSON_ARRAYAGG(JSON_OBJECT(
		"nom_submenu" , sub.NOM_SUBMENU,
		"num_vector1" , sub.NUM_VECTOR1,
		"num_vector2" , sub.NUM_VECTOR2,
		"num_orden"   , sub.NUM_ORDEN  ,
		"nom_pagina"  , sub.NOM_PAGINA ,
		"flg_visible" , sub.FLG_VISIBLE,
		"flg_activo"  , sub.FLG_ACTIVO
		))        
		FROM trabajo.submenu sub
      WHERE sub.id_menu = men.id_menu AND sub.FLG_VISIBLE = 1), '$')                  
)) AS JSON_SP
FROM trabajo.menu men
WHERE men.FLG_VISIBLE = 1
ORDER BY men.NUM_ORDEN ASC;

Con la correcta aplicación de la función JSON_EXTRACT, la cual nos retorna en el símbolo de moneda ‘$’ todo la lista de JSON (‘$[0]’ nos retornaría el primer JSON dentro del arreglo), finalmente obtenemos un JSON_OBJECT 100% funcional y parseable.

[{
        "nom_menu": "SISTEMA",
        "nom_orden": 1,
        "nom_pagina": null,
        "flg_visible": 1,
        "flg_activo": 1,
        "submenu": [{
                "nom_submenu": "Men�s",
                "num_vector1": 0,
                "num_vector2": 0,
                "num_orden": 1,
                "nom_pagina": "views/sistema/menus.php",
                "flg_visible": 1,
                "flg_activo": 1
            }, {
                "nom_submenu": "SubMen�s",
                "num_vector1": 0,
                "num_vector2": 0,
                "num_orden": 2,
                "nom_pagina": "views/sistema/submenus.php",
                "flg_visible": 1,
                "flg_activo": 1
            }
        ]
    }, {
        "nom_menu": "PRUEBA1",
        "nom_orden": 2,
        "nom_pagina": null,
        "flg_visible": 1,
        "flg_activo": 1,
        "submenu": [{
                "nom_submenu": "Prueba1_1",
                "num_vector1": 0,
                "num_vector2": 0,
                "num_orden": 1,
                "nom_pagina": "views/sistema/prueba1_!.php",
                "flg_visible": 1,
                "flg_activo": 1
            }
        ]
    }, {
        "nom_menu": "PRUEBA2",
        "nom_orden": 3,
        "nom_pagina": null,
        "flg_visible": 1,
        "flg_activo": 1,
        "submenu": null
    }, {
        "nom_menu": "PRUEBA4",
        "nom_orden": 5,
        "nom_pagina": null,
        "flg_visible": 1,
        "flg_activo": 0,
        "submenu": null
    }
]