Parse JSON list with no key in PLSQL-CodePudding

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

CodePudding user response：

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

NAME	AGE
Victor	20
Ana	23
Betty	24
Carol	25

db<>fiddle here

CodePudding user response：

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

CodePudding user response：

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;
/