Oracle · REST

Formas de leer un JSON en Oracle – JSON_TABLE

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.

Script 1

Script 2

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;

sql_1

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;

sql_2

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;

sql_3

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;

sql_4

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;

sql_5

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;

sql_2_1

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;

sql_2_2

Referencias

Oracle Doc Ref

Ref_2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s