如何在PL/SQL中解析JSON字符串 [英] How can I parse JSON string in PL/SQL

问题描述

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

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

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

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

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"
}');

推荐答案

Oracle 12c支持JSON

Oracle 12c supports 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;

请注意，出于某些原因，在那儿需要t昵称

Note that for some reason t nickname is necessary there

或完整示例:

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.

更多信息

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

查看全文