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!!

Leer datos con formato JSON desde Oracle - JSON_VALUE

Si necesitamos realizar alguna operación con datos que tengan formato JSON en versiones anteriores  a la 12c  hemos tenido que crear nuestras rutinas para leer los datos de este formato, pero ahora desde la version Oracle 12.1.0.2 ya se incluyó utilidades para extrae los datos como JSON_VALUE el cual es una gran utilidad.

JSON_VALUE como primer parámetro recibe la cadena de JSON y segundo la ruta o dirección dé extracción del dato.

https://docs.oracle.com/database/121/SQLRF/functions093.htm#SQLRF56668

Ejemplos

Para un entendimiento practico vamos a realizar un ejemplo, Empecemos con algo básico extraigamos dato Color

SELECT JSON_VALUE('{Color:"Amarillo"}', '$.Color') AS value 

FROM DUAL;

Ahora con varios colores, fijemos del lado de la ruta indicamos el elemento "Color2" para que nos retorne el color.

SELECT JSON_VALUE('{Color1:"Amarillo", Color2:"Azul", Color2:"Rojo"}', '$.Color2') AS value 
FROM DUAL;

Extrae elemento de la lista basado en el índice del elemento, las posiciones de los elementos van desde 0 hasta 2

SELECT JSON_VALUE('["Amarillo", "Azul", "Rojo"]', '$[2]') AS value 
FROM DUAL;

Extraer una propiedad de uno de los elementos de la lista

SELECT JSON_VALUE('[{Color:"Amarillo"}, {Color:"Azul"}, {Color:"Rojo"}]', '$[1].Color') AS value 
FROM DUAL;

Ahora veamos algo mas practico, tendremos una lista de personas Nombre, Sexo y Edad. Para hacer esto el dato JSON  lo voy a poner en una subconsulta y el elemento que extraeré es el de la tercera posición (para el arreglo va a ser la 2).

SELECT  JSON_VALUE(A.DATO_JSON,'$[2].Nombre')   Nombre, 
        JSON_VALUE(A.DATO_JSON,'$[2].Sexo')     Sexo, 
        JSON_VALUE(A.DATO_JSON,'$[2].Edad')     Edad
FROM(
    SELECT '[   {Nombre:"Victor", Sexo:"M", Edad:30}, 
                {Nombre:"Manuel", Sexo:"M", Edad:25}, 
                {Nombre:"Paola" , Sexo:"F", Edad:30} ]' DATO_JSON
    FROM DUAL
)A;

Ahora agreguemos un campo mas "Email" en el dato JSON, pero que "Paola" no lo tenga para ver el resultado . 

SELECT  JSON_VALUE(A.DATO_JSON,'$[2].Nombre')   Nombre, 
        JSON_VALUE(A.DATO_JSON,'$[2].Sexo')     Sexo, 
        JSON_VALUE(A.DATO_JSON,'$[2].Edad')     Edad,
        JSON_VALUE(A.DATO_JSON,'$[2].Email')    Email
FROM(
    SELECT '[   {Nombre:"Victor", Sexo:"M", Edad:30, Email:"victor@hola.com"}, 
                {Nombre:"Manuel", Sexo:"M", Edad:25, Email:"manuel@hola.com"}, 
                {Nombre:"Paola" , Sexo:"F", Edad:30} ]' DATO_JSON
    FROM DUAL
)A;

Lo que ocurrió es que aunque el el campo no exista lo va a presentar NULO. Tambien podemos manejo eventos de datos nulos o errores para que en caso de que ocurra presente un default.

SELECT  JSON_VALUE(A.DATO_JSON,'$[2].Nombre')   Nombre, 
        JSON_VALUE(A.DATO_JSON,'$[2].Sexo')     Sexo, 
        JSON_VALUE(A.DATO_JSON,'$[2].Edad')     Edad,
        JSON_VALUE(A.DATO_JSON,'$[2].Email' DEFAULT 'No definido' ON ERROR)    Email
FROM(
    SELECT '[   {Nombre:"Victor", Sexo:"M", Edad:30, Email:"victor@hola.com"}, 
                {Nombre:"Manuel", Sexo:"M", Edad:25, Email:"manuel@hola.com"}, 
                {Nombre:"Paola" , Sexo:"F", Edad:30} ]' DATO_JSON
    FROM DUAL
)A;

Espero les sirva de ayuda