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

Showing what you have tried would still be helpful. Doesn't `apex_json.get_count(p_path=>'.')` work? — Alex Poole, Jan 19 '22 at 12:29
@AlexPoole No, it returned nothing. I also tried =>'' =>'[%]' =>'.person'. Thanks — Whiplax, Jan 19 '22 at 13:07
If you must populate your db with data coming in JSON format, did you (or your bosses) consider upgrading the db version? — , Jan 19 '22 at 14:40
You are asking the right question, and in the right way (insisting on using JSON-specific tools, in your case from the APEX_JSON package). I added the `oracle-apex` tag to attract the right kind of attention to your question. I can't help (I don't know APEX), but search for how it can parse **arrays**. You keep saying "no keys" - those don't apply to arrays. You have an array of objects, that is perfectly legitimate, you just need a function that processes arrays, not objects (as the first step anyway). — , Jan 19 '22 at 14:46
@mathguy what I meant by keys is that the array is the root of the JSON, instead of the root being an dictionary, so there's no key for me refer to in the path to get the object, and that's where I'm stuck. Sorry I should have elaborated better. But I'll search for this function to process arrays, thanks. — Whiplax, Jan 20 '22 at 11:09

Abolfazl Aghili · Answer 1 · 2022-03-05T07:04:26.003

--oracle 12c or later
SELECT *
  FROM JSON_TABLE (
           '[{"name":"Victor", "age":"20"},{"name":"Ana", "age":"23"}]',
           '$[*]'
           COLUMNS 
                NAME VARCHAR2 (2000) PATH '$.name',
                AGE  VARCHAR2 (2000) PATH '$.age') 
                    
--oracle 11g
SELECT *
   FROM XMLTABLE (
            '/json/row'
            PASSING apex_json.to_xmltype (
                        '[{"name":"Victor", "age":"20"},{"name":"Ana", "age":"23"}]')
            COLUMNS 
                NAME    VARCHAR2 (2000) PATH '/row/name',
                AGE     VARCHAR2 (2000) PATH '/row/age')

The `JSON_TABLE()` function is not available for Oracle DB ver.11g — Barbaros Özhan, Mar 04 '22 at 10:59

score 1 · Answer 2 · answered Jan 19 '22 at 16:06

With APEX_JSON you can do something like this:

DECLARE
  l_json_text VARCHAR2(32767);
  l_json_values    apex_json.t_values;
BEGIN  
  l_json_text := '[
    {"name":"Victor", "age":"20"},
    {"name":"Ana", "age":"23"}
]
';  
  apex_json.parse(
    p_values => l_json_values,
    p_source => l_json_text
  );
  DBMS_OUTPUT.put_line('----------------------------------------'); 
  FOR r IN 1 .. nvl(apex_json.get_count(p_path => '.', p_values => l_json_values),0) loop
    dbms_output.put_line(apex_json.get_varchar2(p_path => '[%d].name', p0 => r, p_values => l_json_values));
    dbms_output.put_line(apex_json.get_varchar2(p_path => '[%d].age', p0 => r, p_values => l_json_values));
    /* insert into your_table 
       (name,
       age
       ) 
       VALUES 
       (
            apex_json.get_varchar2(p_path => '[%d].name', p0 => r, p_values => l_json_values),
            apex_json.get_varchar2(p_path => '[%d].age', p0 => r, p_values => l_json_values)
        );
    */
  END loop;
  DBMS_OUTPUT.put_line('----------------------------------------'); 
END;
/

Barbaros Özhan · Accepted Answer · 2022-01-19T18:24:31.490

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

score 0 · Answer 4 · answered Jan 19 '22 at 13:18

If you can find a proper JSON parser then you should use that; however, if one is not available, you could parse it yourself. From Oracle 11gR2, you can use:

INSERT INTO table_name (name, age)
WITH jsondata (json) AS (
  SELECT '[
    {"name":"Victor", "age":"20"},
    {"name":"Ana", "age":"23"},
    {
      "name":"Betty",
      "age":"24"
    },
    {
      "age":"25",
      "name":"Carol"
    }
]' FROM DUAL
),
data (json, items, i, name, age) AS (
  SELECT json,
         REGEXP_COUNT(
           json,
               '\{\s*"name"\s*:\s*"(.*?)"\s*,\s*"age"\s*:\s*"(.*?)"\s*\}'
           || '|\{\s*"age"\s*:\s*"(.*?)"\s*,\s*"name"\s*:\s*"(.*?)"\s*\}',
           1,
           'n'
         ),
         1,
         REGEXP_SUBSTR(
           REGEXP_SUBSTR(
             json,
                 '\{\s*"name"\s*:\s*"(.*?)"\s*,\s*"age"\s*:\s*"(.*?)"\s*\}'
             || '|\{\s*"age"\s*:\s*"(.*?)"\s*,\s*"name"\s*:\s*"(.*?)"\s*\}',
             1,
             1,
             'n'
           ),
           '"name"\s*:\s*"(.*?)"',
           1,
           1,
           'n',
           1
         ),
         REGEXP_SUBSTR(
           REGEXP_SUBSTR(
             json,
                 '\{\s*"name"\s*:\s*"(.*?)"\s*,\s*"age"\s*:\s*"(.*?)"\s*\}'
             || '|\{\s*"age"\s*:\s*"(.*?)"\s*,\s*"name"\s*:\s*"(.*?)"\s*\}',
             1,
             1,
             'n'
           ),
           '"age"\s*:\s*"(.*?)"',
           1,
           1,
           'n',
           1
         )
  FROM   jsondata
UNION ALL
  SELECT json,
         items,
         i + 1,
         REGEXP_SUBSTR(
           REGEXP_SUBSTR(
             json,
                 '\{\s*"name"\s*:\s*"(.*?)"\s*,\s*"age"\s*:\s*"(.*?)"\s*\}'
             || '|\{\s*"age"\s*:\s*"(.*?)"\s*,\s*"name"\s*:\s*"(.*?)"\s*\}',
             1,
             i + 1,
             'n'
           ),
           '"name"\s*:\s*"(.*?)"',
           1,
           1,
           'n',
           1
         ),
         REGEXP_SUBSTR(
           REGEXP_SUBSTR(
             json,
                 '\{\s*"name"\s*:\s*"(.*?)"\s*,\s*"age"\s*:\s*"(.*?)"\s*\}'
             || '|\{\s*"age"\s*:\s*"(.*?)"\s*,\s*"name"\s*:\s*"(.*?)"\s*\}',
             1,
             i + 1,
             'n'
           ),
           '"age"\s*:\s*"(.*?)"',
           1,
           1,
           'n',
           1
         )
  FROM   data
  WHERE  i < items
)
SELECT name, age
FROM   data;

(Note: the regular expression does not handle escaped quotes in the strings as I am assuming they will not occur in names; however, if they do then instead of .*? you can use (\(["\/bfnrt]|u[0-9a-fA-F]{4})|[^"])*.)

Which, given the table:

CREATE TABLE table_name (name VARCHAR2(30), age NUMBER);

Then after the insert:

SELECT * FROM table_name;

Outputs:

NAME AGE

Victor 20

Ana 23

Betty 24

Carol 25

db<>fiddle here

Hey thank you very much for the answer. I do have the parser APEX_JSON to use, but I can't find out how to iterate over each person, since all examples I find assumes the list is identified by a key. — Whiplax, Jan 19 '22 at 13:44

score 0 · Answer 5 · answered Jan 19 '22 at 13:30

Last time done that with a clob variable. Try to do it like :

DECLARE 
json_body clob := '[
    {"name":"Victor", "age":"20"},
    {"name":"Ana", "age":"23"}
    ]';
BEGIN
  FOR items IN (SELECT   *
    FROM   
     JSON_TABLE(json_body FORMAT JSON,'$[*]'
              COLUMNS (
                        name_ varchar (200) PATH '$.name',
                        age_ varchar (200) PATH '$.age')))
LOOP
    INSERT INTO T_DATA (
       name,
       age
       ) VALUES (
    items.name_, 
items.age_
);
END LOOP;
END;
/

This will put your data into a table and then you can play with them

select * from T_DATA;

Resulting into : result

Thanks for the answer. Unfortunately the version of the database doesn't support JSON_TABLE. — Whiplax, Jan 19 '22 at 13:36

Parse JSON list with no key in PLSQL

5 Answers5