Parse JSON list with no key in PLSQL

Question

What I'm trying to do is fill up a table with the data from a JSON. The file is formatted like this.

[
  {
    "name": "Victor",
    "age": "20"
  },
  {
    "name": "Ana",
    "age": "23"
  }
]

I can't change how it's formatted.

I tried using APEX_JSON to parse it and add row by row, but I can't even use the GET_COUNT, none of the paths I tried worked.

The database is an Oracle 11g, so there's no JSON_TABLE

Answer 1

You can use XMLTABLE along with APEX_JSON.TO_XMLTYPE() function in order to simulate JSON_TABLE such as

WITH t(jsCol) AS
(
 SELECT '[
          {
            "name": "Victor",
            "age": "20"
          },
          {
            "name": "Anna",
            "age": "23"
          }
         ]' 
   FROM dual 
)
SELECT name, age
  FROM t,
       XMLTABLE('/json/row'
                PASSING APEX_JSON.TO_XMLTYPE(jsCol)
                COLUMNS 
                  name VARCHAR2(100) PATH 'name',
                  age  VARCHAR2(100) PATH 'age'
               )

NAME	AGE
Victor	20
Anna	23