如何在PL / SQL中解析JSON字符串

I want to parse a JSON string that is in the CLOB column from table Tests_1, and insert it into another table (Test_2).

我想解析表Tests_1中CLOB列中的JSON字符串,并将其插入另一个表(Test_2)。

How can I do this in PL/SQL without using any JSON library?

如何在不使用任何JSON库的情况下在PL / SQL中执行此操作?

create table Tests_1
(
  value   CLOB
)
create table Test_2 (a date,b date,c number,d number, e number)

INSERT INTO Tests_1
  (value)
VALUES
  ('{ 
"a":"01/01/2015",
"b":"31/12/2015",
"c":"11111111111",
"d":"1111111111",
"e":"1234567890"
}');

4 个解决方案

#1

Oracle 12c supports JSON

Oracle 12c支持JSON

if you have an existing table simply do

如果你有一个现有的表只是做

ALTER TABLE table1 ADD CONSTRAINT constraint_name CHECK (your_column IS json);
SELECT t.your_column.id FROM table1 t;

Note that for some reason t nickname is necessary there

请注意,由于某种原因,昵称是必要的

Or complete example:

或者完整的例子:

CREATE TABLE json_documents (
  id    RAW(16) NOT NULL,
  data  CLOB,
  CONSTRAINT json_documents_pk PRIMARY KEY (id),
  CONSTRAINT json_documents_json_chk CHECK (data IS JSON)
);

INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(),
        '{
          "FirstName"      : "John",
          "LastName"       : "Doe",
          "Job"            : "Clerk",
          "Address"        : {
                              "Street"   : "99 My Street",
                              "City"     : "My City",
                              "Country"  : "UK",
                              "Postcode" : "A12 34B"
                             },
          "ContactDetails" : {
                              "Email"    : "[e-mail]",
                              "Phone"    : "44 123 123456",
                              "Twitter"  : "@johndoe"
                             },
          "DateOfBirth"    : "01-JAN-1980",
          "Active"         : true
         }');




SELECT a.data.FirstName,
       a.data.LastName,
       a.data.Address.Postcode AS Postcode,
       a.data.ContactDetails.Email AS Email
FROM   json_documents a;


FIRSTNAME       LASTNAME        POSTCODE   EMAIL
--------------- --------------- ---------- -------------------------
Jayne           Doe             A12 34B    [e-mail]
John            Doe             A12 34B    [e-mail]

2 rows selected.

More info

• https://oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1

• https://docs.oracle.com/database/122/ADJSN/using-PLSQL-object-types-for-JSON.htm#ADJSN-GUID-F0561593-D0B9-44EA-9C8C-ACB6AA9474EE

#2

With 11.0.4 version (there is no 11.0.4 version, of course) you have at least two choices(apart from writing a parser yourself) :

对于11.0.4版本(当然没有11.0.4版本),您至少有两个选择(除了自己编写解析器):

Depending on the version of RDBMS you are using, here are a couple of options:

根据您使用的RDBMS版本,以下是几个选项:

First one: for Oracle 11.1.0.7 and up, install Apex 5 and use apex_json package:

第一个:对于Oracle 11.1.0.7及更高版本,安装Apex 5并使用apex_json包:

-- here I have 12.1.0.1 version with version 5 of apex installed

column ora_version format a21;
column apex_version format a21;


select (select version from v$instance) as ora_version
     , (select version_no from apex_release) as apex_version
  from dual;

--drop table test_2;
/* our test table */  
create table test_2(
  c_a date,
  c_b date,
  c_c number,
  c_d number,
  c_e number
);

select * from test_2;

declare
  l_json_doc clob; 
begin
  dbms_output.put_line('Parsing json...');
  l_json_doc := '{"a":"01/01/2015","b":"31/12/2015",
                  "c":"11111111111","d":"1111111111",
                  "e":"1234567890"}';
  apex_json.parse(l_json_doc);
  insert into test_2(c_a, c_b, c_c, c_d, c_e)
    values(apex_json.get_date(p_path=>'a', p_format=>'dd/mm/yyyy'),
           apex_json.get_date(p_path=>'b', p_format=>'dd/mm/yyyy'),
           to_number(apex_json.get_varchar2(p_path=>'c')),
           to_number(apex_json.get_varchar2(p_path=>'d')),
           to_number(apex_json.get_varchar2(p_path=>'e')));
  commit;
  dbms_output.put_line('Done!');
end;
/

column c_c format 99999999999;
select to_char(c_a, 'dd/mm/yyyy') as c_a
     , to_char(c_b, 'dd/mm/yyyy') as c_b
     , c_c
     , c_d
     , c_e 
  from test_2;

Result:

ORA_VERSION           APEX_VERSION         
--------------------- ---------------------
12.1.0.1.0            5.0.2.00.07          

1 row selected.

Table created.

no rows selected.

Parsing json...
Done!
PL/SQL procedure successfully completed.


C_A        C_B                 C_C        C_D        C_E
---------- ---------- ------------ ---------- ----------
01/01/2015 31/12/2015  11111111111 1111111111 1234567890

1 row selected.

Second one: Use opensource PL/JSON. Never used it before, so I'm taking this opportunity to try it out. It's quite similar to apex_json.

第二个:使用opensource PL / JSON。从来没有使用它,所以我借此机会尝试一下。它与apex_json非常相似。

declare
  l_json      json;  --json object
  l_json_doc  clob;
begin
  dbms_output.put_line('Parsing json...');

  -- parsing is done upon object instantiation

  l_json_doc := '{"a":"01/01/2015","b":"31/12/2015",
                  "c":"11111111111","d":"1111111111",
                  "e":"1234567890"}';
  l_json := json(l_json_doc);


  insert into test_2(c_a, c_b, c_c, c_d, c_e)
    values(to_date(l_json.get('a').get_string, 'dd-mm-yyyy'),
           to_date(l_json.get('b').get_string, 'dd-mm-yyyy'),
           to_number(l_json.get('c').get_string),
           to_number(l_json.get('d').get_string),
           to_number(l_json.get('e').get_string));
  commit;
  dbms_output.put_line('Done!');
end;

column c_c format 99999999999;
select to_char(c_a, 'dd/mm/yyyy') as c_a
     , to_char(c_b, 'dd/mm/yyyy') as c_b
     , c_c
     , c_d
     , c_e 
  from test_2;

Result:

C_A        C_B                 C_C        C_D        C_E
---------- ---------- ------------ ---------- ----------
01/01/2015 31/12/2015  11111111111 1111111111 1234567890
01/01/2015 31/12/2015  11111111111 1111111111 1234567890

2 rows selected.

Introduction of json_table() in 12.1.0.2 release makes JSON parsing it a bit simpler(just for the sake of demonstration):

在12.1.0.2版本中引入json_table()使JSON解析它更简单一些(仅为了演示):

insert into test_2
  select to_date(c_a, 'dd-mm-yyyy')
       , to_date(c_b, 'dd-mm-yyyy')
       , c_c
       , c_d
       , c_e
    from json_table('{"a":"01/01/2015",
                      "b":"31/12/2015",
                      "c":"11111111111",
                      "d":"1111111111",
                      "e":"1234567890"}'
                    , '$' 
                    columns ( 
                       c_a varchar2(21) path '$.a',
                       c_b varchar2(21) path '$.b',
                       c_c varchar2(21) path '$.c',
                       c_d varchar2(21) path '$.d',
                       c_e varchar2(21) path '$.e'
                    )) ;

result:

 select *
   from test_2;


C_A         C_B                C_C        C_D        C_E
----------- ----------- ---------- ---------- ----------
1/1/2015    12/31/2015  1111111111 1111111111 1234567890

#3

Since you specified you don't want to use any JSON library, if the format is fixed you could coerce it into something you could parse as XML, starting with stripping the curly braces, replacing the colons with equals signs, and removing the double-quotes from the first part of each name/value pair:

由于您指定了您不想使用任何JSON库,如果格式已修复,您可以将其强制转换为可以解析为XML的内容,从剥离花括号开始,用等号替换冒号,并删除双 - 每个名称/值对的第一部分引用:

select regexp_replace(regexp_replace(value, '(^{|}$)'),
  '^"(.*)":(".*")($|,)', '\1=\2', 1, 0, 'm')
from tests_1;

REGEXP_REPLACE(REGEXP_REPLACE(VALUE,'(^{|}$)'),'^"(.*)":(".*")($|,)','\1=\2',1,0
--------------------------------------------------------------------------------

a="01/01/2015"
b="31/12/2015"
c="11111111111"
d="1111111111"
e="1234567890"

which you can use as the attributes of a dummy XML node; convert that to XMLType and you can use XMLTable to extract the attributes:

您可以将其用作虚拟XML节点的属性;将其转换为XMLType,您可以使用XMLTable提取属性:

select x.a, x.b, x.c, x.d, x.e
from tests_1 t
cross join xmltable('/tmp'
  passing xmltype('<tmp ' ||regexp_replace(regexp_replace(value, '(^{|}$)'),
    '^"(.*)":(".*")($|,)', '\1=\2', 1, 0, 'm') || ' />')
  columns a varchar2(10) path '@a',
    b varchar2(10) path '@b',
    c number path '@c',
    d number path '@d',
    e number path '@e'
) x;

A          B                      C             D             E
---------- ---------- ------------- ------------- -------------
01/01/2015 31/12/2015   11111111111    1111111111    1234567890

Then you can convert the strings to dates during insert:

然后您可以在插入期间将字符串转换为日期:

insert into test_2 (a, b, c, d, e)
select to_date(x.a, 'DD/MM/YYYY'), to_date(x.b, 'DD/MM/YYYY'), x.c, x.d, x.e
from tests_1 t
cross join xmltable('/tmp'
  passing xmltype('<tmp ' || regexp_replace(regexp_replace(value, '(^{|}$)'),
    '^"(.*)":(".*")($|,)', '\1=\2', 1, 0, 'm') || ' />')
  columns a varchar2(10) path '@a',
    b varchar2(10) path '@b',
    c number path '@c',
    d number path '@d',
    e number path '@e'
) x;

select * from test_2;

A          B                      C             D             E
---------- ---------- ------------- ------------- -------------
2015-01-01 2015-12-31   11111111111    1111111111    1234567890

That will cope with some of the name/value pairs not being there, and you'll get nulls if that happens.

这将处理一些不存在的名称/值对,如果发生这种情况,你将获得空值。

If all the pairs will always be there you could just tokenize the string and pull out the relevant parts:

如果所有对总是在那里你可以只是标记字符串并拉出相关部分:

select to_date(regexp_substr(value, '[^"]+', 1, 4), 'DD/MM/YYYY') as a,
  to_date(regexp_substr(value, '[^"]+', 1, 8), 'DD/MM/YYYY') as b,
  to_number(regexp_substr(value, '[^"]+', 1, 12)) as c,
  to_number(regexp_substr(value, '[^"]+', 1, 16)) as d,
  to_number(regexp_substr(value, '[^"]+', 1, 20)) as e
from tests_1;

A          B                      C             D             E
---------- ---------- ------------- ------------- -------------
2015-01-01 2015-12-31   11111111111    1111111111    1234567890

#4

From Oracle 18c you could use TREAT AS JSON operator:

从Oracle 18c开始,您可以使用TREAT AS JSON运算符:

SQL Enhancements for JSON

JSON的SQL增强功能

• You can specify that a given SQL expression returns JSON data, using TREAT (... AS JSON).

  您可以使用TREAT(... AS JSON)指定给定的SQL表达式返回JSON数据。

• TREAT (... AS JSON) lets you specify that the return value from a given SQL expression is to be treated as JSON data. Such expressions can include PL/SQL function calls and columns specified by a SQL WITH clause. New data-guide views make it easy to access path and type information for JSON fields, which is recorded for index-backed data guides. Returning generated and queried JSON data in LOB instances widens the scope of the use of relational data.

  TREAT(... AS JSON)允许您指定将给定SQL表达式的返回值视为JSON数据。此类表达式可以包括PL / SQL函数调用和SQL WITH子句指定的列。新的数据引导视图可以轻松访问JSON字段的路径和类型信息,这些信息是为索引支持的数据指南记录的。在LOB实例中返回生成和查询的JSON数据扩大了关系数据的使用范围。

---

This operator provides a way to inform the database that the content of a VARCHAR2, BLOB, CLOB should be treated as containing JSON. This enables a number of useful features, including the ability to use "Simplified Syntax" on database objects that do not have an "IS JSON" constraint.

此运算符提供了一种通知数据库应将VARCHAR2,BLOB,CLOB的内容视为包含JSON的方法。这启用了许多有用的功能,包括在没有“IS JSON”约束的数据库对象上使用“简化语法”的功能。

And in your example:

在你的例子中:

create table Test_1(val CLOB);
create table Test_2(a date,b date,c number,d number, e number);

INSERT INTO Test_1(val)
VALUES('{ 
"a":"01/01/2015",
"b":"31/12/2015",
"c":"11111111111",
"d":"1111111111",
"e":"1234567890"
}');

INSERT INTO Test_2(a,b,c,d,e)
SELECT val_as_json.a,
       val_as_json.b,
       val_as_json.c,
       val_as_json.d,
       val_as_json.e
FROM (SELECT TREAT(val as JSON) val_as_json
      FROM Test_1) sub;
COMMIT;