如何在ORACLE数据库的表中的JSON BLOB字段中创建视图？

尼基尔·罗希拉

JSON：

{
    "first": {
         "JAN": {
                "ID": "1",
                "Name": "Jim"
         }
    },
    "second": {
         "FEB": {
                "ID": "2",
                "Name": "Jack"
         }
    },
    "Idname" : "2",
    "Date" : "01/28/2014",
    "State" : "1"
}

我需要将JSON转换为VIEW，以便可以使用SELECT语句以COLUMNS的形式访问JSON字段。我正在使用Oracle 11g数据库。

以下选择查询的预期输出：

Select JAN_ID, JAN_NAME from <view name>;

给出结果：

1     Jim

巴巴罗斯·奥赞

JSON函数是针对数据库Oracle12c +版本定义的。APEX_JSON对于早期版本，应安装版本5.0+的软件包。每当安装完成时，以下代码都可以用作解决方法，以提取所需的值：

DECLARE
   v_json VARCHAR2(32767); 
   v_mon1 OWA.VC_ARR;
   v_mon2 OWA.VC_ARR;
BEGIN
   SELECT *
     INTO v_json
     FROM j;  -- your JSON value is inserted into this table         
              -- there's no WHERE clause assuming only one row is to be inserted
   APEX_JSON.PARSE(v_json);
   FOR i IN 1..2
   LOOP
     v_mon1(i) := TO_CHAR(TO_DATE(i, 'j'),'jspth');
     v_mon2(i) := TO_CHAR(TO_DATE(i, 'mm'),'MON');

     DBMS_OUTPUT.PUT_LINE(APEX_JSON.GET_VARCHAR2(v_mon1(i)||'.'||v_mon2(i)||'.ID')||'  '||
                          APEX_JSON.GET_VARCHAR2(v_mon1(i)||'.'||v_mon2(i)||'.Name'));
   END LOOP;   
END;
/

如果需要创建视图，则可以将现有数据转换为XMLTYPE，然后XMLTABLE将提取列的功能用作

CREATE OR REPLACE VIEW v_people AS
WITH t AS
(
SELECT APEX_JSON.TO_XMLTYPE(jsdata) AS xml_data
  FROM j
), t2 AS
(  
SELECT xt1.*, xt2.*
  FROM t
 CROSS JOIN
       XMLTABLE('/json/first'
                PASSING xml_data
                COLUMNS 
                  ID1   INT  PATH 'JAN/ID',
                  Name1 VARCHAR2(100) PATH 'JAN/Name'
               ) xt1
 CROSS JOIN
       XMLTABLE('/json/second'
                PASSING xml_data
                COLUMNS 
                  ID2   INT  PATH 'FEB/ID',
                  Name2 VARCHAR2(100) PATH 'FEB/Name'
               ) xt2               
)
SELECT ID1 AS ID, Name1 AS Name FROM t2
UNION ALL
SELECT ID2, Name2 FROM t2

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。