Para leer un formato tipo JSON dentro de ORACLE existen diferentes formas una de ellas es usando JSON_TABLE.
En el siguiente blog daré algunas formas de leer los diferentes atributos usando PL-SQL.
Tomaremos de ejemplo el siguiente JSON.
En el script completo dejare toda la demo incluida la creación de la tabla para la prueba.
JSON:
{
"squadName": "Super hero squad",
"active" : true,
"attributes" : {
"formed" : 2016,
"secretBase": "Super tower",
"Address" : {
"city" : "South San Francisco",
"zipCode" : 99236,
"country" : "United States of America"
}
},
"members": [
{
"name" : "Molecule Man",
"age" : 29,
"secretIdentity": "Dan Jukes",
"powers" : ["Radiation resistance",
"Turning tiny"
]
},
{
"name" : "Madame Uppercut",
"age" : 39,
"secretIdentity": "Jane Wilson",
"powers" : ["Million tonne punch"
]
},
{
"name" : "Eternal Flame",
"age" : 1000000,
"secretIdentity": null,
"powers" : ["Immortality",
"Heat Immunity",
"Inferno",
"Teleportation",
"Interdimensional travel"
]
}
]
}
Accediendo al nodo principal
Para acceder a los atributos primarios deberemos usar el símbolo $ seguidamente con el nombre del atributo a mostrar.
también podemos mostrar el valor de todo un array u objecto que contiene varios objetos usando format json
select jt.*
from demo_json a
, json_table(a.json_column, '$[*]'
columns ( squadName varchar2(100) path '$.squadName'
, active varchar2(100) path '$.active'
, attributes_json format json path '$.attributes'
, members_json format json path '$.members'
)
) jt;
Accediendo al nodo hijo attributes
Simplemente concatenamos el objecto a buscar con el atributo.
Para el ejemplo del address seria usando format json.
select jt.*
from demo_json a
, json_table(a.json_column, '$[*]'
columns ( squadName varchar2(100) path '$.squadName'
, active varchar2(100) path '$.active'
, attributes_formed varchar2(100) path '$.attributes.formed'
, attributes_secretBase varchar2(100) path '$.attributes.secretBase'
, attributes_address format json path '$.attributes.Address'
)
) jt;
Accediendo al nodo hijo/hijo attributes/address
Continuamos concatenando el valor con puntos.
Note que puede declarar atributos tipo number como formed o zipCode.
select jt.*
from demo_json a
, json_table(a.json_column, '$[*]'
columns ( squadName varchar2(100) path '$.squadName'
, active varchar2(100) path '$.active'
, attributes_formed number path '$.attributes.formed'
, attributes_secretBase varchar2(100) path '$.attributes.secretBase'
, attr_address_city varchar2(100) path '$.attributes.Address.city'
, attr_address_zip number path '$.attributes.Address.zipCode'
, attr_address_country varchar2(100) path '$.attributes.Address.country'
)
) jt;
Accediendo al nodo hijo members array
Como este nodo tiene un array de objectos necesitamos hacer un loop, para ello usaremos nested path.
El nodo de powers es igual otro array por lo que mostraremos como format json
select jt.*
from demo_json a
, json_table(a.json_column, '$[*]'
columns ( squadName varchar2(100) path '$.squadName'
, active varchar2(100) path '$.active'
, nested path '$.members[*]'
columns (
name varchar2(100) path '$.name'
, age number path '$.age'
, secretIdentity varchar2(100) path '$.secretIdentity'
, powers format json path '$.powers'
)
)
) jt;
Accediendo al nodo hijo members/powers array
En el último nodo accederemos de forma similar al anterior usando nested path y usando ‘$’
select jt.*
from demo_json a
, json_table(a.json_column, '$[*]'
columns ( squadName varchar2(100) path '$.squadName'
, active varchar2(100) path '$.active'
, nested path '$.members[*]'
columns (
name varchar2(100) path '$.name'
, age number path '$.age'
, secretIdentity varchar2(100) path '$.secretIdentity'
--, powers format json path '$.powers'
, nested path '$.powers[*]'
columns (
power varchar2(100) path '$'
)
)
)
) jt;
Ejemplo 2
Para este ejemplo cambiaremos un poco la estructura del JSON
{
"data": [
[
"7764355",
"23189291"
],
[
"7764355",
"23189295"
]
],
"meta": {
"columns": [
"session_id",
"event_id",
],
"count": 4
}
}
Nodo 1
Para acceder al nodo primario usaremos:
select jt.*
from demo_json_2 a
, json_table(a.json_column, '$.data[*]'
columns ( row_num for ordinality,
nested path '$[*]'
columns (
row_1 varchar2(100) path '$'
)
)
) jt;
Nodos restantes
Para ello podremos acceder de la siguiente forma:
select jt.*
from demo_json_2 a
, json_table(a.json_column, '$.meta[*]'
columns ( row_num for ordinality,
count_c varchar2(100) path '$.count',
nested path '$.columns[*]'
columns (
col_1 varchar2(100) path '$'
)
)
) jt;
A little knowledge to share (Oracle APEX) 