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