Leer datos JSON como Tabla en Oracle - JSON_TABLE

Hola, en un post anterior hable de como extraer información de un dato con formato JSON con la función JSON_VALUE, ahora les quiero mostrar como tratar el formato JSON como si fuera una tabla. JSON_TABLE es una función que viene disponible desde la version 12.1.0.2

Para entenderlo mejor veámoslo con un ejemplo practico, vamos a tener una lista de personas Nombre, Sexo , Edad y Email. 

Para esto vamos a simular que el dato JSON esta en una tabla JSON_DATO con la clausula WITH .

WITH JSON_DATO AS
(
    SELECT '[   {Nombre:"Victor", Sexo:"M", Edad:30}, 
                {Nombre:"Manuel", Sexo:"M", Edad:25}, 
                {Nombre:"Paola" , Sexo:"F", Edad:30} ]' AS JSON_PERSONAS
    FROM DUAL
)
select Personas.*
from JSON_DATO,
JSON_TABLE (JSON_PERSONAS, '$[*]' 
            COLUMNS (   Nombre VARCHAR2 (50) PATH '$.Nombre',
                        Sexo   VARCHAR2 (1 ) PATH '$.Sexo',
                        Edad   NUMBER   (3 ) PATH '$.Edad') 
           )AS Personas;

Este ejemplo tiene el mismo resultado que el anterior, sino que se agrega en el JSON elemento de lista Persona para que vean como varia la ruta de acceso al dato

WITH JSON_DATO AS

(

    SELECT '[   {Persona:[{Nombre:"Victor", Sexo:"M", Edad:30}]}, 

                {Persona:[{Nombre:"Manuel", Sexo:"M", Edad:25}]}, 

                {Persona:[{Nombre:"Paola" , Sexo:"F", Edad:30}]} ]' AS JSON_PERSONAS

    FROM DUAL

)

select Personas.*

from JSON_DATO,

JSON_TABLE(JSON_PERSONAS, '$.Persona[*]' 

           COLUMNS ( Nombre VARCHAR2 (50) PATH '$.Nombre',

                     Sexo   VARCHAR2 (1 ) PATH '$.Sexo',

                     Edad   NUMBER   (3 ) PATH '$.Edad') 

          )AS Personas;

Incluyamos un elemento de dirección que contenga datos de la ubicación. Fíjense en las rutas de acceso como es para acceder al dato dirección.

WITH JSON_DATO AS
(
    SELECT 
    '[  {Persona:[{Nombre:"Victor", Sexo:"M", Edad:30, 

                   Dir:{Pais:"ECU", Ciu:"GYE"} }]

                  }, 
        {Persona:[{Nombre:"Manuel", Sexo:"M", Edad:25, 

                   Dir:{Pais:"ECU", Ciu:"QTO"} }]

                  }, 

        {Persona:[{Nombre:"Paola" , Sexo:"F", Edad:30, 

                   Dir:{Pais:"COL", Ciu:"BOG"} }]

                  } 

     ]' 
            AS JSON_PERSONAS
    FROM DUAL
)
select Personas.*
from JSON_DATO,

JSON_TABLE(JSON_PERSONAS, '$.Persona[*]' 

            COLUMNS (   Nombre VARCHAR2 (50) PATH '$.Nombre',

                        Sexo   VARCHAR2 (1 ) PATH '$.Sexo',

                        Edad   NUMBER   (3 ) PATH '$.Edad',

                        Pais   VARCHAR2 (10) PATH '$.Dir.Pais',

                        Ciudad VARCHAR2 (10) PATH '$.Dir.Ciu') 

         )AS Personas;

Ahora para hacerlo mas interesante vamos a incluir un elemento de Fono que puede tener ninguno o varios numero de teléfonos. Para esto vamos a usar la clausula NESTED PATH para poder acceder a la lista de telefonos que esta dentro del elemento Fono.

WITH JSON_DATO AS
(
    SELECT 
    '[  {Persona:[{Nombre:"Victor", Sexo:"M", Edad:30, 
                   Dir:{Pais:"ECU", Ciu:"GYE"}, 
                   Fono:[{Tipo:"Mobil",Numero:"555-444"},

                         {Tipo:"Casa",Numero:"555-000"}]}]
        }, 
        {Persona:[{Nombre:"Manuel", Sexo:"M", Edad:25, 
                   Dir:{Pais:"ECU", Ciu:"QTO"} }]
        }, 
        {Persona:[{Nombre:"Paola" , Sexo:"F", Edad:30, 
                   Dir:{Pais:"COL", Ciu:"BOG"},
                   Fono:[{Tipo:"Casa",Numero:"333-000"}] }]
        } 
     ]' 
            AS JSON_PERSONAS
    FROM DUAL
)
select Personas.*
from JSON_DATO,
JSON_TABLE(JSON_PERSONAS, '$.Persona[*]' 
            COLUMNS (   Nombre VARCHAR2 (50) PATH '$.Nombre',
                        Sexo   VARCHAR2 (1 ) PATH '$.Sexo',
                        Edad   NUMBER   (3 ) PATH '$.Edad',
                        Pais   VARCHAR2 (10) PATH '$.Dir.Pais',
                        Ciudad VARCHAR2 (10) PATH '$.Dir.Ciu',
                        NESTED PATH '$.Fono[*]'
                            COLUMNS (FonoTipo VARCHAR2(32) PATH '$.Tipo',
                                     FonoNumero VARCHAR2(32) PATH '$.Numero')
                    ) 
         )AS Personas;

Listo, ya tenemos un dato JSON como tabla, espero les sirva!!