CODEWITHSUNDEEP
jsonoracle-databaseoracle11goracle-apexjsonparser
Whiplax
Whiplax

Reputation: 13

Parse JSON list with no key in PLSQL

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

Upvotes: 1

Views: 1320

Answers (5)

Abolfazl Aghili
Abolfazl Aghili

Reputation: 111

--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')

Upvotes: 4

Barbaros Özhan
Barbaros Özhan

Reputation: 65373

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

Upvotes: 1

Koen Lostrie
Koen Lostrie

Reputation: 18695

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

Upvotes: 1

CretC
CretC

Reputation: 1

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

Upvotes: 0

MT0
MT0

Reputation: 168406

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

Upvotes: 0

Related Questions