如何在 PL/SQL 中解析 JSON 字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40631688/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I parse JSON string in PL/SQL
提问by Onur Cete
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"
}');
采纳答案by Toolkit
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 tnickname is necessary there
请注意，出于某种原因，t那里需要昵称
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" : "[email protected]",
"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 [email protected]
John Doe A12 34B [email protected]
2 rows selected.
More info
更多信息
回答by Nick Krasnov
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.7and up, install Apex 5 and use apex_jsonpackage:
第一个：对于 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.
第二个：使用开源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):
介绍json_table()在12.1.0.2版本使得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
回答by Alex Poole
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, 0, 'm')
from tests_1;
REGEXP_REPLACE(REGEXP_REPLACE(VALUE,'(^{|}$)'),'^"(.*)":(".*")($|,)','=',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, 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, 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
回答by Lukasz Szozda
From Oracle 18cyou could use TREAT AS JSON operator:
从Oracle 18c 开始，您可以使用TREAT AS JSON 运算符：
SQL Enhancements for JSON
You can specify that a given SQL expression returns JSON data, using TREAT (... AS 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.
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.
JSON 的 SQL 增强
您可以使用 TREAT (... AS JSON) 指定给定的 SQL 表达式返回 JSON 数据。
TREAT (... AS JSON) 允许您指定将给定 SQL 表达式的返回值视为 JSON 数据。此类表达式可以包括 PL/SQL 函数调用和 SQL WITH 子句指定的列。新的数据指南视图可以轻松访问 JSON 字段的路径和类型信息，这些信息是为索引支持的数据指南记录的。在 LOB 实例中返回生成和查询的 JSON 数据扩大了关系数据的使用范围。
此运算符提供了一种通知数据库应将 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 sub.val_as_json.a,
sub.val_as_json.b,
sub.val_as_json.c,
sub.val_as_json.d,
sub.val_as_json.e
FROM (SELECT TREAT(val as JSON) val_as_json
FROM Test_1) sub;
COMMIT;
